Google表格脚本的whenNumberLessThanOrEqualTo(string)的条件格式 [英] Google Sheets script conditional formatting for whenNumberLessThanOrEqualTo(string)

问题描述

在Google表格中，我添加了一些与各种字母代码相关的条件格式.在这里，A-D是红色，E-F是黄色，G和后面的是绿色:

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.

在尝试通过应用脚本执行相同操作时，似乎不支持此操作，因为与UI相比，类型检查更严格:

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

这将显示错误消息:

Cannot find method whenNumberLessThanOrEqualTo(string)

因此，我无法创建将规则对象应用于表格的规则对象.

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

是否可以通过应用程序脚本创建与我在UI中相同的规则?还是其他方法可以做到这一点?最佳路径似乎只是在JS中重新实现，并在字母值的完整枚举上使用 TEXT_EQUAL_TO ，或与 CUSTOM_FORMULA (

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

推荐答案

不幸的是，内置服务方法 whenNumberLessThanOrEqualTo()需要数字值作为参数而不是字符串.

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

但是，您可以改为使用高级表格服务.高级表格服务基本上是 Google表格REST API 的包装.它比较复杂，它要求您了解许多REST资源对象(请参阅参考文档)组成API的架构.

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.

此外，您需要先在GAS项目中启用表格的API高级服务，然后才能使用它(

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

---

上面的脚本非常冗长，因此一旦您了解了Google Sheets API架构的资源类型，下面的内容就足够了:

---

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

这篇关于Google表格脚本的whenNumberLessThanOrEqualTo(string)的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！