AutoIt
AutoIt
Google Sheets Conditional Formatting - Color Gradient
See more Google Sheets Examples
Add a conditional color gradient across a rowChilkat AutoIt Downloads
Local $bSuccess = False
; This example assumes the Chilkat API to have been previously unlocked.
; See Global Unlock Sample for sample code.
$oHttp = ObjCreate("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
; }
; }
; ]
; }
Local $sheetId = "YOUR_SHEET_ID"
$oJson = ObjCreate("Chilkat.JsonObject")
$oJson.UpdateString("requests[0].addConditionalFormatRule.rule.ranges[0].sheetId",$sheetId)
$oJson.UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex",9)
$oJson.UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex",10)
$oJson.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.2")
$oJson.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
$oJson.UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type","MIN")
$oJson.UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.9")
$oJson.UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type","MAX")
$oJson.UpdateInt("requests[0].addConditionalFormatRule.index",0)
$oJson.UpdateString("requests[1].addConditionalFormatRule.rule.ranges[0].sheetId",$sheetId)
$oJson.UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex",10)
$oJson.UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex",11)
$oJson.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.8")
$oJson.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
$oJson.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type","NUMBER")
$oJson.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value","0")
$oJson.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue","0.9")
$oJson.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.5")
$oJson.UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red","0.5")
$oJson.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type","NUMBER")
$oJson.UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value","256")
$oJson.UpdateInt("requests[1].addConditionalFormatRule.index",1)
; Adds the "Authorization: Bearer ACCESS_TOKEN" header.
$oHttp.AuthToken = "ACCESS_TOKEN"
$oHttp.SetRequestHeader "Content-Type","application/json"
$oResp = ObjCreate("Chilkat.HttpResponse")
$bSuccess = $oHttp.HttpJson("POST","https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate",$oJson,"application/json",$oResp)
If ($bSuccess = False) Then
ConsoleWrite($oHttp.LastErrorText & @CRLF)
Exit
EndIf
ConsoleWrite("Status code: " & $oResp.StatusCode & @CRLF)
ConsoleWrite("Response body:" & @CRLF)
ConsoleWrite($oResp.BodyStr & @CRLF)