(VB.NET) 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 .NET Downloads Chilkat .NET Framework Chilkat for .NET Core

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

Dim http As New Chilkat.Http
Dim success As Boolean

' 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
'       }
'     }
'   ]
' }

Dim sheetId As String = "YOUR_SHEET_ID"

Dim json As New Chilkat.JsonObject
json.UpdateString("requests[0].addConditionalFormatRule.rule.ranges[0].sheetId",sheetId)
json.UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex",9)
json.UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex",10)
json.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.2")
json.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
json.UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type","MIN")
json.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.9")
json.UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type","MAX")
json.UpdateInt("requests[0].addConditionalFormatRule.index",0)
json.UpdateString("requests[1].addConditionalFormatRule.rule.ranges[0].sheetId",sheetId)
json.UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex",10)
json.UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex",11)
json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.8")
json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type","NUMBER")
json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value","0")
json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue","0.9")
json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.5")
json.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red","0.5")
json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type","NUMBER")
json.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value","256")
json.UpdateInt("requests[1].addConditionalFormatRule.index",1)

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

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


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