CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
LAD Service Desk
LAD Service Desk

Reputation: 289

Insert "LastUpdate Date" when some specific columns are modified (Help in the script)

Situation:

I have a spreadsheet with 10 sheets and 15 users logging in and modifying it.

Script Function:

When someone modify any row in any column, this script will update the lastcolumn with DateTime and insert a comment with the user who made that modification.

Problem:

(1) Performance Issue: This script run when the user modify any Column. This cause maybe when I have more > 3 user logged the spreadsheet turn slowly to save.
(2) This script should be run only when some specific's columns are modify. For ej.: If the activeuser modify the column A,B,C;D,E & I the script update the lastcolumn J with the Date&Time; but if the activeuser modify the column F,G,H the script should be not run.

Test Case:

(1) This script is running OnEdit very well but is updating the lastcoulmn when someone modify anyrow in any column.

I will appreciate if anyone can help me to modify this script for only run when specific columns are modified.

Script:

    function onEdit(event)
    {
      var ss = SpreadsheetApp.getActiveSpreadsheet();

    //Script Last Update Timming

      var actSht = event.source.getActiveSheet();
      var actRng = event.source.getActiveRange();

      var index = actRng.getRowIndex();
      var dateCol = actSht.getLastColumn();
      var lastCell = actSht.getRange(index,dateCol);
      var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm");

      // Note: Insert the Date when someone update the row in the last coulmn
        lastCell.setValue(date);

      // Nota: Insert a comment in the lastCell with the user who modify that row
        lastCell.setComment(Session.getEffectiveUser());
    }

Upvotes: 2

Views: 12722

Answers (1)

Waqar Ahmad
Waqar Ahmad

Reputation: 3732

you can check active row and active column using below code and then based on the identified row, column, you may proceed further.

var activeCell = sheet.getActiveCell();
var row = activeCell.getRow();
var column = activeCell.getColumn();

you overall code will look something like this.

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Script Last Update Timming

  var actSht = event.source.getActiveSheet();
  var actRng = event.source.getActiveRange();

  var activeCell = actSht.getActiveCell();
  var row = activeCell.getRow();
  var column = activeCell.getColumn();

  if(row < 2)   return; //If header row then return
  var colNums  = [1, 5, 6, 7]; //Coulmns, whose edit is considered
  if(colNums.indexOf(column) == -1) return; //If column other than considered then return


  var index = actRng.getRowIndex();
  var dateCol = actSht.getLastColumn();
  var lastCell = actSht.getRange(index,dateCol);
  var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm");

  // Note: Insert the Date when someone update the row in the last coulmn
  lastCell.setValue(date);

  // Nota: Insert a comment in the lastCell with the user who modify that row
  lastCell.setComment(Session.getEffectiveUser());
}

Upvotes: 3

Related Questions