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:

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.
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!

Welcome to Stack Overflow. Please read developers.google.com/apps-script/guides/triggers/installable. If you need further help, show what you tried and add a brief description of you search efforts as is suggested in How to Ask — Wicket
– Wicket, Commented Apr 29, 2021 at 14:18

Nikko J. · Accepted Answer · 2021-04-29 16:06:36Z

Follow these steps to setup an onChange trigger in your spreadsheet

Open your Apps Script project.

At the left, click Triggers alarm.

At the bottom right, click Add Trigger.

Select and configure the type of trigger you want to create.

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:

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related