How to use conditional formatting in Google sheets api v4

Question

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 ?

Sample code below

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

Answer 1

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.

How to apply a Conditional Format Rule in Sheets API v4

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

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

  • Define the range with a GridRange object.

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

Reference

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

Answer 2

I believe your goal as follows.

• You want to reduce the process cost of your script.

Modification points:

• When I saw your script, it seems that a conditional format rule with multiple ranges is added to a sheet in a Google Spreadsheet by one call. In this case, even when this script is converted to Sheets API instead of Spreadsheet service, the process cost might not be the large change. So please test the following modified script.
• As the modification point, I would like to propose as follows.
  • In your script, the ranges are declared in the loop. When this is converted to Sheets API, the ranges can be created at outside of the loop.
  • When the file list is retrieved using Drive API, the process cost will be reduced a little.

When above points are reflected to your script, it becomes as follows.

Modified script:

Before you use this script, please enable Sheets API and Drive API at Advanced Google services. And, please set the variables of topFolderId and ranges. ranges is from your script. When you want to more ranges, please add them to the array.

function myFunction() {
  var topFolderId = "###";  // Please set the top folder ID of the folder including the Spreadsheet.

  // Retrieve file list using Drive API v3.
  const headers = {authorization: `Bearer ${ScriptApp.getOAuthToken()}`};
  const q = `'${topFolderId}' in parents and mimeType='${MimeType.GOOGLE_SHEETS}' and trashed=false`;
  const url = `https://www.googleapis.com/drive/v3/files?pageSize=1000&q=${q}&fields=${encodeURIComponent("nextPageToken,files(id)")}`;
  let pageToken = "";
  let files = [];
  do {
    const res = UrlFetchApp.fetch(url + "&pageToken=" + pageToken, {headers: headers, muteHttpExceptions: true});
    if (res.getResponseCode() != 200) throw new Error(res.getContentText());
    const obj = JSON.parse(res.getContentText());
    files = files.concat(obj.files);
    pageToken = obj.nextPageToken || "";
  } while(pageToken);

  // Create range list.
  const ranges = ["Q4:Q", "S4:S"]; // Please set the ranges as A1Notation. These ranges are used for addConditionalFormatRule.
  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);

  // Request Sheets API for a sheet in each Spreadsheet.
  files.forEach(({id}) => {
    const sheet1 = SpreadsheetApp.openById(id);
    const gr = gridRangeList.map(({startRowIndex, startColumnIndex, endColumnIndex}) => ({sheetId: sheet1.getSheetId(), startRowIndex, startColumnIndex, endColumnIndex}))
    const requests = {addConditionalFormatRule:{rule:{gradientRule:{maxpoint:{color:{red:6/255,green:255/255,blue:0},type:"MAX"},midpoint:{color:{red:255/255,green:239/255,blue:0},type:"PERCENTILE",value:"50"},minpoint:{color:{red:255/255,green:0,blue:0},type:"MIN"}},ranges:[gr]},index:0}};
    Sheets.Spreadsheets.batchUpdate({requests: requests}, id);
  });
}

References:

• Method: spreadsheets.batchUpdate
• AddConditionalFormatRuleRequest