How to setup the "onChange" trigger in Google Sheet script editor

Question

I need to get a Google sheet's cell background color hex code into that same cell.

This is the function I currently use:

  function BGHEX(row, column) {
  var background = SpreadsheetApp.getActive().getDataRange().getCell(row, column).getBackground();
  return background;
}

Now I have 2 issues:

1. I need the function to run again when the cell's background color is edited. I know I need to use the installable trigger "onChange" but no idea how to set it up.
2. I need this script & function to run on a duplicate of this Google Sheet file (meaning other sheet id's).

Thanks in advance for your help!

Answer 1

Follow these steps to setup an onChange trigger in your spreadsheet

1. Open your Apps Script project.
2. At the left, click Triggers alarm.
3. At the bottom right, click Add Trigger.
4. Select and configure the type of trigger you want to create.
5. Click Save.

Your trigger setup should look like this:

To detect background change in your spreadsheet, you need to have an Event Object to determine which changeType the user made. For this scenario, we need 'FORMAT'.

Also, to change the background color of a cell or range, the user must click or highlight the range. Using getActiveRange() will help us determine which range is being edited.

Code:

function onBGChange(e) {
  if(e.changeType == 'FORMAT')
    Logger.log(SpreadsheetApp.getActive().getActiveRange().getBackground());
}

Example:

Here I change the background color of A1

Output:

Executions Tab:

To apply this to another spreadsheet, you need to replicate the code and trigger set up. A script is bound to the spreadsheet where it was created and functions like getActive() and getActiveRange() will only work on the spreadsheet the script was bound to.

References:

• Installable Triggers
• Event Object
• getActiveRange()