Tcl
Tcl
Google Sheets Conditional Formatting - Color Gradient
See more Google Sheets Examples
Add a conditional color gradient across a rowChilkat Tcl Downloads
load ./chilkat.dll
set success 0
# This example assumes the Chilkat API to have been previously unlocked.
# See Global Unlock Sample for sample code.
set http [new_CkHttp]
# 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
# }
# }
# ]
# }
set sheetId "YOUR_SHEET_ID"
set json [new_CkJsonObject]
CkJsonObject_UpdateString $json "requests[0].addConditionalFormatRule.rule.ranges[0].sheetId" $sheetId
CkJsonObject_UpdateInt $json "requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex" 9
CkJsonObject_UpdateInt $json "requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex" 10
CkJsonObject_UpdateNumber $json "requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green" "0.2"
CkJsonObject_UpdateNumber $json "requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red" "0.8"
CkJsonObject_UpdateString $json "requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type" "MIN"
CkJsonObject_UpdateNumber $json "requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green" "0.9"
CkJsonObject_UpdateString $json "requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type" "MAX"
CkJsonObject_UpdateInt $json "requests[0].addConditionalFormatRule.index" 0
CkJsonObject_UpdateString $json "requests[1].addConditionalFormatRule.rule.ranges[0].sheetId" $sheetId
CkJsonObject_UpdateInt $json "requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex" 10
CkJsonObject_UpdateInt $json "requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex" 11
CkJsonObject_UpdateNumber $json "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green" "0.8"
CkJsonObject_UpdateNumber $json "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red" "0.8"
CkJsonObject_UpdateString $json "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type" "NUMBER"
CkJsonObject_UpdateString $json "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value" "0"
CkJsonObject_UpdateNumber $json "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue" "0.9"
CkJsonObject_UpdateNumber $json "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green" "0.5"
CkJsonObject_UpdateNumber $json "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red" "0.5"
CkJsonObject_UpdateString $json "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type" "NUMBER"
CkJsonObject_UpdateString $json "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value" "256"
CkJsonObject_UpdateInt $json "requests[1].addConditionalFormatRule.index" 1
# Adds the "Authorization: Bearer ACCESS_TOKEN" header.
CkHttp_put_AuthToken $http "ACCESS_TOKEN"
CkHttp_SetRequestHeader $http "Content-Type" "application/json"
set resp [new_CkHttpResponse]
set success [CkHttp_HttpJson $http "POST" "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate" $json "application/json" $resp]
if {$success == 0} then {
puts [CkHttp_lastErrorText $http]
delete_CkHttp $http
delete_CkJsonObject $json
delete_CkHttpResponse $resp
exit
}
puts "Status code: [CkHttpResponse_get_StatusCode $resp]"
puts "Response body:"
puts [CkHttpResponse_bodyStr $resp]
delete_CkHttp $http
delete_CkJsonObject $json
delete_CkHttpResponse $resp