How to format cells through code in Google Sheets

In Google Sheets, you might wish to set the color of a cell by calling a custom function in that cell, by doing something something like:

function myFunction(input){
   this.setBackground('red');
   return "You wrote: "+ input;
}

And then in a cell in the spreadsheet call it like a custom formula function:

=myFunction("some text.")

But setting the color of a cell by using such a function is not possible, for various reasons. One reason is that there is no this equivalent that would reference the calling cell, which lets the custom function naturally reference it. (See the bottom of this article for a weird hack I discovered, though.) It would have been nice to do this.setBackground('red') though. But (a custom function called within) a cell doesn’t have access privileges to call setters, only getters. So in any case you would get an “Exception: You do not have permission to call setBackground” when the cell calls the function. Even though it would have been reasonable to allow the cell to access a simple formatting setter on itself atleast (Google, are you listening?).

So what are our options for assigning formatting through a custom function in Google Apps script?

Option 1 — Automatically when the spreadsheet opens

You can do the above, but you cannot set a cell to =setBackgroundOfScriptDeclaredCell() to call that custom function from within the cell. It will give a “Exception: You do not have permission to call setBackground”. Due to the access privilege limitations mentioned above. of what a custom function in a cell is allowed to access (only getters, not setters). See: https://developers.google.com/apps-script/guides/sheets/functions#advanced

Option 2 — When user initiates your script

Option 3 — Setting a conditional formatting rule via your script

You can set conditional formatting rules through your script, which would allow you to keep everything configured in a single script: https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule

This script could be executed automatically when the spreadsheet opens, or when user initiates your script (for instance through a menu option).

Doing this, then you could make a function like:

function foo() {
  return 1;
}

and put it into A1. Then when foo() executes in A1 and returns 1 then A1 will change its background color to red. This of course presumes that you know the result that foo() would output. The conditional formatting rule for A1 will already have been set by the script when the user opened the spreadsheet.

The benefit of doing something like this is that this shows that you can control a cell’s formatting through your script, even the conditional formatting rules (the most advanced of which is called “custom formulas”). This is advantageous if you want to contain every bit of rather complex code (including complex custom formulas) in your script. Maybe you want to be able to easily redistribute your script. Or maybe you just want to keep everything in one place. You also avoid having complex logic spread across and hidden in the Google Sheets GUI.

If you had used the GUI to manage your custom formulas used for conditional formatting, then to see and manage that logic, you would have to find and select the correct cell, or select the entire spreadsheet, and then right click and select “Conditional Formatting”, or go to the menu and select “Format -> Conditional Formatting”, to actually see and edit that logic. Which can be quite inconvenient at times.

Execute custom functions in a custom formula used for conditional formatting, like you would in a cell?

Sadly, the “Custom Formula” option in Google Sheets does not execute custom functions, even though the GUI says a custom formula may take a “Value or Formula”. So you cannot put:

=foo()=1

here and expect it to run. But you may of course do

=B2=1

and then put the custom function call =foo() in B2, which will be executed by the spreadsheet as normal.

How to get a `this` reference to the calling cell? A weird hack.

If you have a cell with =test() you could do:

function test() { 
 return SpreadsheetApp.getActiveSheet().getCurrentCell().getA1Notation();
}

This will actually output the cell’s own reference, so in cell B2 it would output “B2”. So it seems like getCurrentCell() can be used as a this equivalent in Google Sheets.

NB! But this is probably a hack/subversion of the getCurrentCell()function, since getCurrentCell() is not meant to be an actual this equivalent. So using it like this might be subverting its documented purpose: "The current cell is the cell that has focus in the Google Sheets UI, and is highlighted by a dark border. ... When a user selects one or more cell ranges, one of the cells in the selection is the current cell." developers.google.com/apps-script/reference/spreadsheet/… But for some reason it works like thiswhen the custom function is called from within a cell. And the result stays the same, even though the user might be selecting other cell ranges in the sheet. Maybe because the current cell is treated as the selected cell at the point in time the function was called from within the cell. If anyone knows why, then feel free to share that knowledge in a comment!

A small note:getActiveCell()also works equivalently togetCurrentCell()in this scenario. But getActiveCell() is deprecated, probably since it erroneously always refers to the top left cell in a selection, even if the selection of a group of cells is made starting from any other corner (e.g from the lower right corner).

Keywords: Google Sheets, Spreadsheets, JavaScript, Google Apps Script.

Alternative entrypoints: “How can I assign a red background to a cell through code?”, “I want to assign formatting through code”, “ I need to have everything configured in a single script”.

Source of inspiration: https://stackoverflow.com/questions/48195002/how-to-change-format-of-cell-that-runs-function-google-sheets-script-editor