In case you have a user-triggered script, you can assign the color of a cell through code, while keeping everything in the script.
How to format cells through code in Google Sheets
First: It is correct as @Cameron Roberts say's that a custom function executed automatically as a formula in a cell doesn't have access privileges to change formatting of any cells (including its own), or to access the Sheet to change the conditional formatting rules (since the Sheet is at a higher level).
I have focused on answering the highlighted parts here:
How can I assign a red background to the cell through code? Even
though it's a simple example, my script is more complex and I want to
assign formatting through code (I don't want to use the Format >
Conditional Formatting... option; I need to have everything configured
in a single 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
If you have a complex script as you say, it is likely that the user has to initiate it somehow. Relying on that, you could do something like this, to change the formatting of a cell through code.
Option 1 — Automatically when the spreadsheet opens:
// This does not set the color of a user-selected cell like using getActiveCell does,
// but it necessarily relies on getActiveSheet(), since ranges are defined as referencing cells within sheets.
function setBackgroundOfScriptDeclaredCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1");
range.setBackground("red");
}
// Will run when user opens/refreshes spreadsheet.
function onOpen() {
setBackgroundOfScriptDeclaredCell();
}
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 reasonable limitations 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
// Add custom menu to spreadsheet
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Run custom script')
.addItem('Set background of a script-declared cell (A1)', 'setBackgroundOfScriptDeclaredCell')
.addToUi();
}
// This does not set the color of a user-selected cell like using getActiveCell does, but it necessarily relies on getActiveSheet(), since ranges are defined as referencing cells within sheets.
function setBackgroundOfScriptDeclaredCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange("A1");
range.setBackground("red");
}
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).
function yourScript() {
// ...
var cellsInA1Notation = "A1"; // could also have been e.g. "C3:D4"
setRangeToRedBackground(cellsInA1Notation);
// ...
}
// This is a custom convenience function I made which is not provided directly by the Google Sheets API.
function addConditionalFormatRule(sheet, rule) {
var rules = sheet.getConditionalFormatRules();
rules.push(rule);
sheet.setConditionalFormatRules(rules)
}
// Adds a conditional format rule to a sheet that causes a cell to have red background color if it contains a 1.
// To illustrate that conditional formatting rules do not need to be spread out across (and hidden) in the spreadsheet GUI,
// but can be manipulated entirely in your script.
function setRangeToRedBackground(cellsInA1Notation) {
var sheet = SpreadsheetApp.getActiveSheet();
var range = sheet.getRange(cellsInA1Notation);
var customFormulaString = "=A1=1";
var rule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied(customFormulaString)
.setBackground("red")
.setRanges([range])
.build();
addConditionalFormatRule(sheet, rule);
}
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.
I also wrote this up in a blog post where I went into more detail on two points:
- How would setRangeToRedBackground look if implemented using the more generic
SpreadsheetApp.newConditionalFormatRule().withCriteria function, rather than with using only the special case .whenFormulaSatisfied?
- Is it is possible to "Execute custom functions in a custom formula used for conditional formatting, like you would in a cell?"
Here: https://medium.com/@magne/how-to-format-cells-through-code-in-google-sheets-9d727ecc6053
I hope this answer may help someone else who might stumble across this challenge.
Source of inspiration for this answer, beside having roughly the same question myself: https://developers.google.com/apps-script/reference/spreadsheet/conditional-format-rule-builder
