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

I believe that you will have to use a workaround in this case, if appropriate for your circumstance.

You would have to create a template sheet that had cells formatted by conditional formatting (manually done by you) in a source spreadsheet.

Then your script will copy this template sheet over to your target spreadsheet, and then use the copyTo method with advanced parameters of {formatOnly:true} to copy the format of one or a range of cells in the template sheet over to your chosen sheet (finally, you can delete this template sheet from the target spreadsheet). (The copyTo method only copies from one range to another within the same spreadsheet).

Something like this:

function transferFormatting() {
  var targetSs = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1dLv8V5fKmmxRLi5maLIOgrxVGVaqGGOD7STHbEremQs/edit#gid=0');
  var targetSsDisplaySheet = targetSs.getSheets()[0];
  var sourceSs = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/14RxLXktm0wj-lSFrl5Fas9B-smMYhknFt3-dYQbwH3o/edit#gid=933388275');
  var templateSheet = sourceSs.getSheetByName('Template');
  var targetSsFormatTemplateSheet =  templateSheet.copyTo(targetSs);

  targetSsFormatTemplateSheet.getRange("A1").copyTo(targetSsDisplaySheet.getRange("B:D"), {formatOnly:true});
  targetSs.deleteSheet(targetSsFormatTemplateSheet);
}