Google Sheets – Restrict Script to Certain Range

google sheetsgoogle-apps-script

There is this question regarding adding checkbox functionality to Google Drive: Checkbox function to automatically add to total. And a great answer is:

In the Google Sheets spreadsheet go to Tools -> Script Editor.
Enter the following code:

function onEdit() {
  if(SpreadsheetApp.getActiveRange().getValue() == 1) {
    SpreadsheetApp.getActiveRange().setValue('=CHAR(10004)');
    SpreadsheetApp.getActiveRange().setBackgroundRGB(0,255, 0);
  }
  if(SpreadsheetApp.getActiveRange().getValue() == 0) {
    SpreadsheetApp.getActiveRange().setValue('=CHAR(10060)');
    SpreadsheetApp.getActiveRange().setBackgroundRGB(255, 0, 0);
  }
}

Enter into any cell a 1 for a tick, and a zero for a cross.

This code is great and it works beautifully, but I would like to know how to ONLY apply that to a range of cells and not to the whole spreadsheet.

By example, cells: G25 x I47

Best Answer

In order to limit the range of a script, get the row and column of the active cell, and check that it falls within some bounds:

function onEdit(e) {
  var row = e.range.getRow();
  var col = e.range.getColumn();
  if (col >= 7 && col <= 9 && row >= 25 && row <= 47) {
  if (e.value == 1) {
    e.range.setFormula('=CHAR(10004)');
    e.range.setBackgroundRGB(0,255, 0);
  }
  if (e.value == 0) {
    e.range.setFormula('=CHAR(10060)');
    e.range.setBackgroundRGB(255, 0, 0);
  }
}

(I also shortened the script using the event object.)

Related Topic