Google Spreadsheet SCRIPT Check if edited cell is in a specific range
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"
}
Easiest Way is to NAME your Trigger Range
By naming your "trigger" range, then you don't have to mess with the script once you've set the range name inside the script. By simply editing the range of your named range inside the standard google sheets interface, the script will still work even if you expand or decrease the size of your range.
Step 1: Name Your Range
Name the range of cells you are interested in acting as the trigger for your script should you edit any of the cells in your range as per the instructions here: https://support.google.com/docs/answer/63175
I named my range "triggerRange".
Step 2: Edit your range name into the following script:
function onEdit(e) {
var myRange = SpreadsheetApp.getActiveSheet().getRange('triggerRange'); //<<< Change Your Named Ranged Name Here inside the getRange() function.
//SpreadsheetApp.getUi().alert("myRange in A1 Notation is: " + myRange.getA1Notation()); //If you're having problems, uncomment this to make sure your named range is properly defined
//Let's get the row & column indexes of the active cell
var row = e.range.getRow();
var col = e.range.getColumn();
//SpreadsheetApp.getUi().alert('The Active Cell Row is ' + row + ' and the Column is ' + col); //uncomment this out to do testing
//Check that your active cell is within your named range
if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) { //As defined by your Named Range
SpreadsheetApp.getUi().alert('You Edited a Cell INSIDE the Range!');//Repalace Your Custom Code Here
} else {
SpreadsheetApp.getUi().alert('You Edited a Cell OUTSIDE the Range!');//Comment this out or insert code if you want to do something if the edited cells AREN'T inside your named range
return;
}
}
PS: Thanks to the other posters for providing the basic framework for this basic script. I've obviously commented the script pretty heavily so you can easily test it. Remove the alerts that I created inside the script for a cleaner look...or just copy and paste this instead:
function onEdit(e) {
var myRange = SpreadsheetApp.getActiveSheet().getRange('triggerRange'); //<<< Change Your Named Ranged Name Here
//Let's get the row & column indexes of the active cell
var row = e.range.getRow();
var col = e.range.getColumn();
//Check that your active cell is within your named range
if (col >= myRange.getColumn() && col <= myRange.getLastColumn() && row >= myRange.getRow() && row <= myRange.getLastRow()) { //As defined by your Named Range
SpreadsheetApp.getUi().alert('You Edited a Cell INSIDE the Range!');//Repalace Your Custom Code Here
}
}
You can simply check whether the edit event 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 acknowledge that this is very verbose, but I still haven't found a simple method on range ala range.contains(range)