Google Sheets script conditional formatting for whenNumberLessThanOrEqualTo(string)

Question

In Google sheets, I've added some conditional formatting related to various letter codes. Here, A-D are red, E-F are yellow, and G and later are green:

This works how I expect and want.

In trying to do the same thing via an apps script, this doesn't seem to be supported, because there's stricter type checking than in the UI:

  SpreadsheetApp.newConditionalFormatRule()
    .whenNumberLessThanOrEqualTo('D')
    .setBackground('#ff0000')
    .setRanges([range])
    .build());

This shows the error message:

Cannot find method whenNumberLessThanOrEqualTo(string)

And so I can't create the rule object to apply it to the sheet.

Are there ways to create the same rule I've made in the UI via apps scripts? Or alternate ways to do this? The best path seems to just re-implement this in JS and use a TEXT_EQUAL_TO on the full enumeration of letter values, or something similar with CUSTOM_FORMULA (docs).

Answer 1

Unfortunately, the built-in service method whenNumberLessThanOrEqualTo() expects numeric values as arguments instead of strings.

However, you can leverage the Advanced Sheets service instead. The advanced sheets service is basically a wrapper for the Google Sheets REST API. Its more complex and it requires that you know many of the REST resource objects (see reference documentation) that make up the API's schema.

Moreover, you'll need to enable the Sheet's API advanced service in your GAS project before you can use it (see documentation on how to enable an advanced service).

Once you do that, you can leverage the service to add your formatting rule.

The following script is an example of how you might do that:

function buildRule() {
    var conditionValue = Sheets.newConditionValue();
    var booleanCondition = Sheets.newBooleanCondition();

    var color = Sheets.newColor();
    var cellFormat = Sheets.newCellFormat();

    var booleanRule = Sheets.newBooleanRule();
    var gridRange = Sheets.newGridRange();

    var formatRule = Sheets.newConditionalFormatRule();
    var addConditionalFormatRuleRequest = Sheets.newAddConditionalFormatRuleRequest();

    var request = Sheets.newRequest();

    var batchRequest = Sheets.newBatchUpdateSpreadsheetRequest();

    conditionValue.userEnteredValue = "D";

    booleanCondition.type = "NUMBER_GREATER_THAN_EQ";
    booleanCondition.values = [conditionValue];

    // #ff0000 in RGB format
    color.red = 1; // values range from 0 to 1
    color.blue = 0;
    color.green = 0;

    cellFormat.backgroundColor = color;

    booleanRule.condition = booleanCondition;
    booleanRule.format = cellFormat;

    // selects E2 as range
    gridRange.sheetId = 0;
    gridRange.startColumnIndex = 4;
    gridRange.endColumnIndex = 5;
    gridRange.startRowIndex = 1;
    gridRange.endRowIndex = 2;

    formatRule.booleanRule = booleanRule;
    formatRule.ranges = [gridRange];

    addConditionalFormatRuleRequest.rule = formatRule;
    addConditionalFormatRuleRequest.index = 0; // index of rule; increment to add other rules

    request.addConditionalFormatRule = addConditionalFormatRuleRequest;
    batchRequest.requests = [request];

    Sheets.Spreadsheets.batchUpdate(batchRequest, SpreadsheetApp.getActive().getId());
}

---

The above script is pretty verbose, so once you know your way around the resource types for the Google Sheets API schema, the following would also suffice:

function buildRule() {
    var batchRequest = {
       "requests":[
          {
             "addConditionalFormatRule":{
                "rule":{
                   "booleanRule":{
                      "condition":{
                         "type":"NUMBER_GREATER_THAN_EQ",
                         "values":[
                            {
                               "userEnteredValue":"D"
                            }
                         ]
                      },
                      "format":{
                         "backgroundColor":{
                            "red":1,
                            "blue":0,
                            "green":0
                         }
                      }
                   },
                   "ranges":[
                      {
                         "sheetId":0,
                         "startColumnIndex":4,
                         "endColumnIndex":5,
                         "startRowIndex":1,
                         "endRowIndex":2
                      }
                   ]
                },
                "index":0
             }
          }
       ]
    };

    Sheets.Spreadsheets.batchUpdate(batchRequest, SpreadsheetApp.getActive().getId());
}