Add conditional formatting rule

Question

Google Sheets has a great interactive way to declaratively define conditional formatting. Is there a way to program the same declarative model using App Script?

I have a script that generates many spreadsheets. I set data and data validation rules using the Sheet and DataValidation classes. However, I can't seem to do the same for conditional formatting. I expected to find a ConditionalFormattingRule object or something like that.

Note: I know that custom script can be written that listens for an onEdit trigger. However, that script would need to be added to each of the generated spreadsheets. Also, non-declarative rules like that would not appear in the conditional formatting sidebar.

I'm simply trying to apply a red background to cells that have no value.

Is this just not possible?

Answer 1

With the latest Sheets api you can programmatically add a conditional format rule.

To add a rule that sets the background to red for all empty cells in a sheet you can do this:

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('MySheet');

  var numRows = sheet.getLastRow();
  var numCols = sheet.getLastColumn();
  var range = sheet.getRange(1,1, numRows, numCols);
  var rule = SpreadsheetApp.newConditionalFormatRule()
      .whenCellEmpty()
      .setBackground("red")
      .setRanges([range])
      .build();        
  var rules = sheet.getConditionalFormatRules();
  rules.push(rule);
  sheet.setConditionalFormatRules(rules);

The SpreadsheetApp newConditionalFormatRule() method returns a conditional format rule builder, that is used to build a conditional format rule.

You could run this code on each new spreadsheet you create.