(Tcl) 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 Tcl Extension Downloads Chilkat Tcl Extension Downloads

load ./chilkat.dll

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

# resp is a CkHttpResponse
set resp [CkHttp_PostJson3 $http "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate" "application/json" $json]
if {[CkHttp_get_LastMethodSuccess $http] == 0} then {
    puts [CkHttp_lastErrorText $http]
    delete_CkHttp $http
    delete_CkJsonObject $json
    exit
}

puts "Status code: [CkHttpResponse_get_StatusCode $resp]"
puts "Response body:"
puts [CkHttpResponse_bodyStr $resp]

delete_CkHttpResponse $resp


delete_CkHttp $http
delete_CkJsonObject $json