1

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();
Rubén
  • 34,714
  • 9
  • 70
  • 166
MorningSleeper
  • 415
  • 1
  • 5
  • 13

1 Answers1

0

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;

Bryan P
  • 5,031
  • 3
  • 30
  • 44
  • Unfortunately that also returns #REF! Thanks for the info on active spreadsheet already being passed into function with e. – MorningSleeper Oct 01 '15 at 05:46
  • Thanks, I see the person who filed that report mentions it always returns A1 while I am getting #REF!, any idea why I don't get A1 if that report is related to what I posted? – MorningSleeper Oct 01 '15 at 06:04
  • 1
    The issue reported by Bryan P was fixed but the onChange is broken again. Please star the current issue -> [38159142](https://issuetracker.google.com/issues/38159142) – Rubén May 31 '18 at 16:00