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