Google Spreadsheet SCRIPT Check if edited cell is in a specific range

by

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

More Related Contents:

  1. Refresh data retrieved by a custom function in Google Sheet
  2. How do you create a “reverse pivot” in Google Sheets?
  3. Create Google Calendar Events from Spreadsheet but prevent duplicates
  4. ultimate short custom number formatting – K, M, B, T, etc., Q, D, Googol
  5. Google Apps Script: How to set “Use column A as labels” in chart embedded in spreadsheet?
  6. Apps Script: how to get hyperlink from a cell where there is no formula
  7. Retrieving Google Sheets cell border style programmatically
  8. Determining the last row in a single column
  9. Cannot find method getRange(number,number)?
  10. You do not have permission to call openById
  11. Google Apps Script, copy one spreadsheet to another spreadsheet with formatting
  12. Single Google Form for multiple Sheets [duplicate]
  13. Adding Buttons To Google Sheets and Set value to Cells on clicking
  14. How do I communicate a page(modal dialog) with its sibling (sidebar)?
  15. How to replace text in Google Spreadsheet using App Scripts?
  16. Custom function throws a “You do not have the permission required to setValue” error
  17. Margins parameters for Google spreadsheet export as PDF
  18. Google Apps Script: Dynamically creating spreadsheet menu items
  19. Script to convert .XLSX to Google Sheet and move converted file
  20. email notification if cell is changed
  21. Hidden field in a Google Form
  22. Apply multiple font colors to the text in a single Google Sheets cell
  23. How to evaluate a spreadsheet formula within a custom function?
  24. Google Sheet use Importxml error could not fetch url [duplicate]
  25. On Edit doesnt work if cell auto updates – Need a workaround Google Sheets
  26. Determine current user in Apps Script
  27. Adding a property to an Html template gives error “Object does not allow properties to be added or changed”
  28. We’re sorry, a server error occurred while reading from storage. Error code PERMISSION_DENIED
  29. Custom Functions and Recalculation
  30. e.range.getA1Notation() unable to track changes caused by formula update

Leave a Comment