CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Run.Bat
Run.Bat

Reputation: 105

Auto timestamp on edit:range

I am not really good with scripting so I have turned here for help. I have a Google Sheet that I want a cell(K17) to auto timestamp when any cell within a range (A1:L30) is changed/updated.

Only scripts I can find are when specific cells update it will auto timestamp the cell next to it. Here is an example of code I have found:

function onEdit() {
 var s = SpreadsheetApp.getActiveSheet();
 if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
   var r = s.getActiveCell();
   if( r.getColumn() == 13 ) { //checks the column
     var nextCell = r.offset(0, 1);
     if( nextCell.getValue() === '' ) //is empty?
       var time = new Date();
       time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
       nextCell.setValue(time);
   };
 };
}

Upvotes: 1

Views: 593

Answers (1)

Jesse Scherer
Jesse Scherer

Reputation: 1522

The key to doing this is to examine the event object that is passed in -- your example doesn't do this. Once you can examine the event you can see the range that was touched. Then compare to whatever criteron (yours is being within A1:L30, which is to say in the first 30 rows and in the 12 columns of Sheet1):

function onEdit(e) {
  var editedRange = e.range;                 // This is what got edited
  var editedSheet = editedRange.getSheet();  // Ranges belong to a sheet
  if (
    (editedSheet.getName() == "Sheet1")      // correct sheet?
    && (editedRange.getColumn() <= 12)       // column L is number 12
    && (editedRange.getRow() < 30)           // We're on row 30 or less
  ) {
      var time = new Date();  
      time = Utilities.formatDate(time, "GMT", "HH:mm:ss");    
      editedSheet.getRange("K17").setValue(time);
    }
}

Upvotes: 1

Related Questions