How to identify changed cell in onChange function

Question

I am trying to detect background color change of two cells. A while back I created the following Google Sheet function and added it as an installable trigger From spreadsheet On change since cell color changes are not detected by on Edit. I thought it worked but I just checked it now, and it is not detecting which cell's background color was changed, Any ideas?

function onChangeInstallable(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();
  var activeSheetName = activeSheet.getSheetName();

  if (activeSheetName == "Settings") {

    if(e.changeType == "OTHER"){

      // makes it this far ok but following line returns
      // #REF! instead of A1 notation of changed cell.

      var editedRange = SpreadsheetApp.getActiveRange().getA1Notation();

      if (editedRange == 'B43' || editedRange == 'B44'){

        setBackgroundColors();

      }
    }
  }
}

Also tried the following, but it returns a "Cell reference out of range" error.

var editedRange = activeSheet.getActiveCell().getA1Notation();

Bryan P Bryan P · Accepted Answer · 2015-10-01T05:40:19

edit: looks like this case is still broken, star issue 3269 to vote for a fix

try var editedRange = activeSheet.getActiveRange().getA1Notation();

the active spreadsheet is already being passed into the function with the e object too, fyi.

var ss = e.source;

How to identify changed cell in onChange function

1 Answers