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);
}

Answer 2

Also available is the Advanced Sheets Services to add Conditional Formatting from Google Apps Script.

Be sure to Enable Advanced Google Services from your script. Then you can use Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId) to add conditional formatting using Google Sheets API v4 and Advanced Sheets Services

Navigate through each JSON representation object to piece together the full request:

• AddConditionalFormatRuleRequest
  • ConditionalFormatRule
    • GridRange
    • BooleanRule
      • BooleanCondition
        • ConditionType
      • CellFormat

// Conditionally sets the background of cells to red within range B2:J1000
function setConditionalFormat() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheets()[0]

  var format_req = {
    "requests": [{
      "addConditionalFormatRule": { 
        "rule": {
          "ranges": [{
            "sheetId": sheet.getSheetId(),
            "startRowIndex": 1,
            "endRowIndex": sheet.getMaxRows(),
            "startColumnIndex": 1,
            "endColumnIndex": 10
            }],
          "booleanRule": {
            "condition": {
              "type": "BLANK"
            },
            "format": {
              "backgroundColor": {
                "red": 1,
                "green": 0,
                "blue": 0,
                "alpha": 1
              }
            }
          }
        },
        "index": 0,
      }
    }],
  "includeSpreadsheetInResponse": false,
  }

  Sheets.Spreadsheets.batchUpdate(JSON.stringify(format_req), ss.getId())
}

Answer 3

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);

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

Answer 4

The following code will format alternating rows in your Google Sheets table:

    function lastLine() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getSheets()[0];

      var range = sheet.getRange("A1:A").getValues();

      return range.filter(String).length;
    }

    function setConditionalFormat() {
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var sheet = ss.getSheets()[0]

      var myRange = {
        'sheetId': sheet.getSheetId(),
        'startRowIndex': 1,
        'endRowIndex': lastLine(),
        'startColumnIndex': 0,
        'endColumnIndex': 8
      }


      var format_req = {
        'requests': [
          {'addConditionalFormatRule': {
             'index': 0,
               'rule': {
                 'ranges': [ myRange ],
                  'booleanRule': {
                     'format': {'backgroundColor': {'red': 1, 'green': 1, 'blue': 1, 'alpha': 1}},
                      'condition': {
                        'type': 'CUSTOM_FORMULA',
                        'values': [{'userEnteredValue': '=ISEVEN(ROW())'}]
                      },
                  },
               },

             }
          },
          {'addConditionalFormatRule': {
             'index': 0,
               'rule': {
                 'ranges': [ myRange ],
                  'booleanRule': {
                     'format': {'backgroundColor': {'red': 222/255, 'green': 235/255, 'blue': 246/255, 'alpha': 0.7}},
                      'condition': {
                        'type': 'CUSTOM_FORMULA',
                        'values':
                          [{'userEnteredValue': '=ISODD(ROW())'}]
                      },
                  },
               },

             }
          }      
        ],
      'includeSpreadsheetInResponse': false,
      }

      Sheets.Spreadsheets.batchUpdate(JSON.stringify(format_req), ss.getId())
    }