Google SCRIPT spreadsheet. Check if the edited cell has been edited in a specific range.

Question

I need to determine if changes have been made to the spreadsheet in a specific data range, and if so, set the current update time.

The problem is that I have a spreadsheet in which I edit the headers and text, and I don't want the update time in a specific cell to be updated in the spreadsheet, but when I edit the data in a range of cells, I want to update the changed time.

Here I have to update the time.

function onEdit(e) { var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); ss.getRange("G10").setValue(new Date()); } ​ 

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

Answer 1

There is an Event , provided as a parameter of your onEdit() function, and contains the necessary information about what has been edited. If you are interested in what this (e) , here it is.

Since the onEdit() function is called for each edit, you should invest as little processing as possible when deciding whether to exit. Using the event that has passed, you will need fewer service calls, so it will be more efficient. The way Rasmus answer converts A1 notation to column and row numbers is good if you need to be flexible, but if the editing 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" } ​ 

Answer 2

You can simply check if an editing event has occurred within the range.

 function onEdit(e) { var sheet = SpreadsheetApp.getActiveSheet(); var editRange = sheet.getActiveRange(); var editRow = editRange.getRow(); var editCol = editRange.getColumn(); var range = sheet.getRange("B4:J6"); var rangeRowStart = range.getRow(); var rangeRowEnd = rangeRowStart + range.getHeight(); var rangeColStart = range.getColumn(); var rangeColEnd = rangeColStart + range.getWidth(); if (editRow >= rangeRowStart && editRow <= rangeRowEnd && editCol >= rangeColStart && editCol <= rangeColEnd) { // Do your magic here } } 

I admit this is very verbose, but I still haven't found a simple method for the range ala range.contains (range)

Answer 3

Just quickly fix a possible problem with the previous Rasmus answer:

In the lines:

 var rangeRowEnd = rangeRowStart + range.getHeight(); var rangeColEnd = rangeColStart + range.getWidth(); 

it adds (in fact, this is not a subtraction of the original row and column, which leads to a larger range than expected from 1 row and the whole integer columns. Just subtract 1 in the same row:

 var rangeRowEnd = rangeRowStart + range.getHeight()-1; var rangeColEnd = rangeColStart + range.getWidth()-1; 

The code from Rasmus Fuglsnag and the fix should look like this:

 function onEdit(e) { var sheet = SpreadsheetApp.getActiveSheet(); var editRange = sheet.getActiveRange(); var editRow = editRange.getRow(); var editCol = editRange.getColumn(); var range = sheet.getRange("B4:J6"); var rangeRowStart = range.getRow(); var rangeRowEnd = rangeRowStart + range.getHeight()-1; var rangeColStart = range.getColumn(); var rangeColEnd = rangeColStart + range.getWidth()-1; if (editRow >= rangeRowStart && editRow <= rangeRowEnd && editCol >= rangeColStart && editCol <= rangeColEnd) { // Do your magic here } } 

Thanks to Rasmus Fuglsnag for providing the easiest way to do this. I cannot believe that there is an easier way to do this with code.

Answer 4

If you want very few lines of code, you can use:

 function onEdit(){ var cell = SpreadsheetApp.getActive().getActiveCell(); var row = cell.getRow(); var col = cell.getColumn(); //if col > A && col < K ... if(col > 1 && col < 11 && row > 3 && row < 7){ //B4:J6 //do something } 

However, changing the range may take a little longer than other methods.