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

Download Excel Class Modules Chilkat Excel Class Modules

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

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp

' 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"

Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
Dim success As Boolean
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"


Set resp = http.PostJson3("https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate","application/json",json)
If (http.LastMethodSuccess = False) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

Debug.Print "Status code: "; resp.StatusCode
Debug.Print "Response body:"
Debug.Print resp.BodyStr