(VBScript) Google Sheets Conditional Formatting - Color Gradient

Add a conditional color gradient across a row

For more information, see https://developers.google.com/sheets/api/samples/conditional-formatting#add_a_conditional_color_gradient_across_a_row

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

Dim fso, outFile
Set fso = CreateObject("Scripting.FileSystemObject")
'Create a Unicode (utf-16) output text file.
Set outFile = fso.CreateTextFile("output.txt", True, True)

' This example assumes the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

' For versions of Chilkat < 10.0.0, use CreateObject("Chilkat_9_5_0.Http")
set http = CreateObject("Chilkat.Http")

' Implements the following CURL command:

' curl -H "Content-Type: application/json" \
'    -H "Authorization: Bearer ACCESS_TOKEN" \
'    -X POST \
'    -d '{
'   "requests": [
'     {
'       "addConditionalFormatRule": {
'         "rule": {
'           "ranges": [
'             {
'               "sheetId": sheetId,
'               "startRowIndex": 9,
'               "endRowIndex": 10,
'             }
'           ],
'           "gradientRule": {
'             "minpoint": {
'               "color": {
'                 "green": 0.2,
'                 "red": 0.8
'               },
'               "type": "MIN"
'             },
'             "maxpoint": {
'               "color": {
'                 "green": 0.9
'               },
'               "type": "MAX"
'             },
'           }
'         },
'         "index": 0
'       }
'     },
'     {
'       "addConditionalFormatRule": {
'         "rule": {
'           "ranges": [
'             {
'               "sheetId": sheetId,
'               "startRowIndex": 10,
'               "endRowIndex": 11,
'             }
'           ],
'           "gradientRule": {
'             "minpoint": {
'               "color": {
'                 "green": 0.8,
'                 "red": 0.8
'               },
'               "type": "NUMBER",
'               "value": "0"
'             },
'             "maxpoint": {
'               "color": {
'                 "blue": 0.9,
'                 "green": 0.5,
'                 "red": 0.5
'               },
'               "type": "NUMBER",
'               "value": "256"
'             },
'           }
'         },
'         "index": 1
'       }
'     },
'   ]
' }' https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate

' Use the following online tool to generate HTTP code from a CURL command
' Convert a cURL Command to HTTP Source Code

' Use this online tool to generate code from sample JSON:
' Generate Code to Create JSON

' The following JSON is sent in the request body.

' {
'   "requests": [
'     {
'       "addConditionalFormatRule": {
'         "rule": {
'           "ranges": [
'             {
'               "sheetId": sheetId,
'               "startRowIndex": 9,
'               "endRowIndex": 10
'             }
'           ],
'           "gradientRule": {
'             "minpoint": {
'               "color": {
'                 "green": 0.2,
'                 "red": 0.8
'               },
'               "type": "MIN"
'             },
'             "maxpoint": {
'               "color": {
'                 "green": 0.9
'               },
'               "type": "MAX"
'             }
'           }
'         },
'         "index": 0
'       }
'     },
'     {
'       "addConditionalFormatRule": {
'         "rule": {
'           "ranges": [
'             {
'               "sheetId": sheetId,
'               "startRowIndex": 10,
'               "endRowIndex": 11
'             }
'           ],
'           "gradientRule": {
'             "minpoint": {
'               "color": {
'                 "green": 0.8,
'                 "red": 0.8
'               },
'               "type": "NUMBER",
'               "value": "0"
'             },
'             "maxpoint": {
'               "color": {
'                 "blue": 0.9,
'                 "green": 0.5,
'                 "red": 0.5
'               },
'               "type": "NUMBER",
'               "value": "256"
'             }
'           }
'         },
'         "index": 1
'       }
'     }
'   ]
' }

sheetId = "YOUR_SHEET_ID"

' For versions of Chilkat < 10.0.0, use CreateObject("Chilkat_9_5_0.JsonObject")
set json = CreateObject("Chilkat.JsonObject")
success = json.UpdateString("requests[0].addConditionalFormatRule.rule.ranges[0].sheetId",sheetId)
success = json.UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex",9)
success = json.UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex",10)
success = json.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.2")
success = json.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
success = json.UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type","MIN")
success = json.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.9")
success = json.UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type","MAX")
success = json.UpdateInt("requests[0].addConditionalFormatRule.index",0)
success = json.UpdateString("requests[1].addConditionalFormatRule.rule.ranges[0].sheetId",sheetId)
success = json.UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex",10)
success = json.UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex",11)
success = json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.8")
success = json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
success = json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type","NUMBER")
success = json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value","0")
success = json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue","0.9")
success = json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.5")
success = json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red","0.5")
success = json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type","NUMBER")
success = json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value","256")
success = json.UpdateInt("requests[1].addConditionalFormatRule.index",1)

' Adds the "Authorization: Bearer ACCESS_TOKEN" header.
http.AuthToken = "ACCESS_TOKEN"
http.SetRequestHeader "Content-Type","application/json"

' resp is a Chilkat.HttpResponse
Set resp = http.PostJson3("https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate","application/json",json)
If (http.LastMethodSuccess = 0) Then
    outFile.WriteLine(http.LastErrorText)
    WScript.Quit
End If

outFile.WriteLine("Status code: " & resp.StatusCode)
outFile.WriteLine("Response body:")
outFile.WriteLine(resp.BodyStr)


outFile.Close