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 the Script application?

I have a script that generates many spreadsheets. I set the rules for validating data and data using the Sheet and DataValidation classes. However, I cannot do the same for conditional formatting. I was expecting to find a ConditionalFormattingRule object or something like that.

Note. I know that a custom script can be written that listens onEdit trigger . However, for the script to be added to each of the generated spreadsheets. In addition, non-declarative rules like this will not appear on the sidebar of conditional formatting .

I'm just trying to apply a red background to cells that don't matter.

Is it just not possible?

Answer 1

I believe that you will have to use a workaround in this case, if that suits your circumstances.

You will need to create a template sheet in which conditional formatting (manually made by you) was formatted in the original spreadsheet.

Then your script will copy this template sheet to your target table and then apply the copyTo method with advanced options {formatOnly: true} copy the format of one or more cells to the template sheet on the sheet of your choice (finally, you can delete this template sheet from the target table) . (The copyTo method only copies from one range to another in the same spreadsheet).

Something like that:

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

Advanced Sheets Services is also available to add conditional formatting from Google Apps Script.

Be sure to Enable Google Advanced Services from Script. You can then use Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId) to add conditional formatting using the Google Sheets API v4 and Advanced Sheet Services

Go through each JSON view object to collect 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

The following code will format variable rows in a 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()) }