如何在Google Sheets API v4中使用条件格式 [英] How to use conditional formatting in Google sheets api v4

问题描述

美好的一天.请告诉我如何将这个脚本转换为使用Google Sheets API v4并减少请求的费用.正确理解我需要深入一面: https://developers.google.com/sheets/api/samples/conditional-formatting?hl = zh-CN#add_a_conditional_formatting_rule_to_a_set_of_ranges ?

Good day. Please tell me how I can convert this script to use Google sheets api v4 and reduce the cost of the request. Understand correctly that I need to dig to the side: https://developers.google.com/sheets/api/samples/conditional-formatting?hl=en#add_a_conditional_formatting_rule_to_a_set_of_ranges ?

下面的示例代码

while (folders.hasNext()) {
      var folder = folders.next().getId();
      var sheet1 = SpreadsheetApp.openById(folder);
      var sheet = sheet1.getActiveSheet();
      var r1 = sheet.getRange('Q4:Q');var r2 = sheet.getRange('S4:S'); 
      var rule = SpreadsheetApp.newConditionalFormatRule()
        .setGradientMaxpoint("#06ff00")
        .setGradientMidpointWithValue("#ffef00", SpreadsheetApp.InterpolationType.PERCENTILE, "50")
        .setGradientMinpoint("#ff0000")
        .setRanges([r1,r2,r3,r4,r5,r6,r7,r8,r9,r10,
        r11,r12,r13,r14,r15,r16,r17,r18,r19,r20,
        r21,r22,r23,r24,r25,r26,r27,r28,r29,r30,
        r31,r32,r33,r34,r35,r36,r37,r38,r39,r40,
        r41,r42,r43,r44,r45,r46,r47,r48,r49,r50,
        r51,r52,r53,r54,r55,r56,r57,r58,r59,r60,
        r61,r62,r63,r64,r65])
        .build()
      var rules = sheet.getConditionalFormatRules();
      rules.push(rule);
      sheet.setConditionalFormatRules(rules);
      }

如果有任何帮助，我将不胜感激

I will be grateful for any help

推荐答案

答案

我了解您要使用 Sheet API v4 而不是电子表格服务以降低请求费用.我不知道使用这种方法可以减少多少费用，但是我将向您解释如何做到这一点.

Answer

I understand that you want to use Sheet API v4 instead of Spreadsheet Service to reduce the cost of the request. I don't know how much the cost will be reduced using that way, but I will explain to you how to do it.

• 使用方法 batchUpdate.它带有一个 request正文，您可以在其中定义 Conditional Format Rule 和 spreadsheetId .您可以使用尝试此API 部分轻松构造请求正文，它可以帮助您放置和定义所需的所有参数.

• Use the method batchUpdate. It takes a request body where you can define the Conditional Format Rule and the spreadsheetId. You can easily construct the request body using the section Try this API, it helps you to put and define all the parameters that you need.

使用请求正文"rel ="nofollow noreferrer"> AddConditionalFormatRuleRequest 对象.它有两个字段，用于描述条件格式的<规则>规则和用于定义应在何处插入<规则>规则的<规则>索引.

Define the request body with a AddConditionalFormatRuleRequest object. It has two fields, the rule that describes the conditional format and the index that defines where the rule should be inserted.

使用

Define the rule field with a ConditionalFormatRule object. It takes two fields, the ranges and the gradientRule or the boolearnRule (you can only choose one).

• 使用range ="nofollow noreferrer"> GridRange 对象.

• Define the range with a GridRange object.

定义 gradientRule 及其三个字段: minpoint ， midpoint 和 maxpoint .每一个都由 InterpolationPoint 定义.对象.

Define the gradientRule with its three fields: minpoint, midpoint and maxpoint. Each of these is defined by an InterpolationPoint object.

最后，您的代码将类似于以下内容:

Finally your code will look similar to the following:

function main(){
  // start here
  var folders = // your definition
  const gridRangeList = createGridRange() // create the GridRange object
  while (folders.hasNext()) {
      var spreadsheetId = folders.next().getId();     
      applyConditionalFormating(spreadsheetId, gridRangeList) // apply the conditional format
  }
}

function createGridRange(){
  const ranges = ["Q4:Q", "S4:S"]
  const temp = SpreadsheetApp.create("temp")
  const rangeList = temp.getSheets()[0].getRangeList(ranges).getRanges()
  const gridRangeList = rangeList.map(r => ({startRowIndex: r.getRow() - 1, startColumnIndex: r.getColumn() - 1, endColumnIndex: r.getColumn() + r.getNumColumns() - 1}))
  DriveApp.getFileById(temp.getId()).setTrashed(true) // move the file to the trash
  return gridRangeList
}

function applyConditionalFormating(spreadsheetId, gridRangeList){
  const request = {
    "requests": [
      {
        "addConditionalFormatRule": {
          "rule": {
            "gradientRule": {
              "maxpoint": {
                "type": "MAX",
                "color": {red:6/255,green:255/255,blue:0}
              },
              "midpoint": {
                "type": "PERCENTILE",
                "value": "50",
                "color": {red:255/255,green:239/255,blue:0}
              },
              "minpoint": {
                "type": "MIN",
                "color":{red:255/255,green:0,blue:0}
              }
            },
            "ranges": [gridRangeList]
        },
        "index": 0
        }
      }
    ]
  }
  Sheets.Spreadsheets.batchUpdate(request,spreadsheetId)
}

参考

• Sheet API v4
• 电子表格服务
• 条件格式规则
• 方法:sheets.batchUpdate
• AddConditionalFormatRuleRequest
• ConditionalFormatRule
• GridRange
• gradientRule
• InterpolationPoint

Reference

• Sheet API v4
• Spreadsheet Service
• Conditional Format Rule
• Method: spreadsheets.batchUpdate
• AddConditionalFormatRuleRequest
• ConditionalFormatRule
• GridRange
• gradientRule
• InterpolationPoint

这篇关于如何在Google Sheets API v4中使用条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！