Check if edited cell is in a specific range

Question

I need to detect if changes made to a spreadsheet are being made within a certain range of data and if so, set the current update time.

The issue is, I have a spreadsheet that I edit headers and text on and I do not want the update time in a specific cell to be updated on the spreadsheet but when I edit the data in a range of cells, I DO want the update time changed.

Here's what I have to update the time.

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

  ss.getRange("G10").setValue(new Date());
} ​

I only want the date in G10 set if I edit certain cells (in a range "B4:J6")

Answer 1

There is an Event provided as a parameter to your onEdit() function, and it contains the necessary information about what was edited. If you were wondering what that (e) was all about, this is it.

Since an onEdit() function is called for every edit, you should invest as little processing as possible in determining whether you should exit. By using the event that's passed in, you will require fewer Service calls, so will be more efficient. The way that Rasmus' answer converts the A1 notation to column and row numbers is good if you need to be flexible, but if the edit range is fixed, you can simply use constant values for comparisons - again, to reduce the processing time required.

function onEdit(e) 
{
  var editRange = { // B4:J6
    top : 4,
    bottom : 6,
    left : 2,
    right : 10
  };

  // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  // We're in range; timestamp the edit
  var ss = e.range.getSheet();
  ss.getRange(thisRow,7)   // "G" is column 7
    .setValue(new Date()); // Set time of edit in "G"
} ​