Skip to content

Instantly share code, notes, and snippets.

@redbar0n
Last active July 26, 2020 04:11
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save redbar0n/68f68d432ff79e274a9077eeba54fcd8 to your computer and use it in GitHub Desktop.
Save redbar0n/68f68d432ff79e274a9077eeba54fcd8 to your computer and use it in GitHub Desktop.
Google Sheets - How to format cells through code in Google Sheets. Option 3: Setting a conditional formatting rule through your script.
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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment