Google Spreadsheet SCRIPT Check if edited cell is

问题:

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")

回答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"
} ​

回答2:

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
  }
}

回答3:

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)

回答4:

Just a quick fix to a possible problem to the previous answer by Rasmus:

On the lines:

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

it is adding (actually it is not subtracting the initial row and column. This results in a bigger range than what is expected by 1 row and 1 whole column. Just subtract 1 in the same row:

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

Code by Rasmus Fuglsnag and the correction 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 simplest way to do this. I can not believe there is a better simpler way to do this by code.

回答5:

If you want very few lines of code, you could 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 could take a little longer than the other methods.