How to find out if a specific cell range has been updated in Google Sheets?

Question

I am making a spreadsheet that requires finding out if a certain cell range has been edited and then add the date that it was edited on. I have the range of cells needed to be edited, and I also don't want the date to change if I sort the table from a different column.

I would think the general idea would look something like this, not actual code just an idea:

cell_range=(b2:d2)
if (cell_range)==editted:
     date()

if (table_sort==True):
     date_change==False

If anyone knows how to do this, that would be greatly appreciated.

Answer 1

you need a script for this. try for example:

function onEdit(e) {
var s = SpreadsheetApp.getActiveSheet();
if( s.getName() == "Sheet1" ) { 
var r = s.getActiveCell();
if( r.getColumn() == 2 ) { 
  var nextCell = r.offset(0, 8);
  var newDate = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy");
  nextCell.setValue(newDate);
}
if( r.getColumn() == 3 ) { 
  var nextCell = r.offset(0, 7);
  var newDate = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy");
  nextCell.setValue(newDate);
}
if( r.getColumn() == 4 ) { 
  var nextCell = r.offset(0, 6);
  var newDate1 = Utilities.formatDate(new Date(), 
  "GMT+8", "MM/dd/yyyy");
  nextCell.setValue(newDate1);
}}}

• "Sheet1" = sheet name
• r.getColumn() == 3 = column C / 3rd column
• r.offset(0, 7) = offset timestamp 7 columns to the right on the same row eg column H
• "GMT+8" = timezone
• "MM/dd/yyyy hh:mm:ss" = date and time format

alternative (untested):

function onEdit(e) { 
 if ([2].indexOf(e.range.columnStart) != -1) { 
 e.range.offset(0, 7).setValue(new Date()); } 
 if ([3].indexOf(e.range.columnStart) != -1) { 
 e.range.offset(0, 6).setValue(new Date()); }
 if ([4].indexOf(e.range.columnStart) != -1) { 
 e.range.offset(0, 5).setValue(new Date()); } }