PureBasic
PureBasic
Google Sheets Conditional Formatting - Color Gradient
See more Google Sheets Examples
Add a conditional color gradient across a rowChilkat PureBasic Downloads
IncludeFile "CkHttpResponse.pb"
IncludeFile "CkHttp.pb"
IncludeFile "CkJsonObject.pb"
Procedure ChilkatExample()
success.i = 0
; This example assumes the Chilkat API to have been previously unlocked.
; See Global Unlock Sample for sample code.
http.i = CkHttp::ckCreate()
If http.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
; 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.s = "YOUR_SHEET_ID"
json.i = CkJsonObject::ckCreate()
If json.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
CkJsonObject::ckUpdateString(json,"requests[0].addConditionalFormatRule.rule.ranges[0].sheetId",sheetId)
CkJsonObject::ckUpdateInt(json,"requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex",9)
CkJsonObject::ckUpdateInt(json,"requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex",10)
CkJsonObject::ckUpdateNumber(json,"requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.2")
CkJsonObject::ckUpdateNumber(json,"requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
CkJsonObject::ckUpdateString(json,"requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type","MIN")
CkJsonObject::ckUpdateNumber(json,"requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.9")
CkJsonObject::ckUpdateString(json,"requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type","MAX")
CkJsonObject::ckUpdateInt(json,"requests[0].addConditionalFormatRule.index",0)
CkJsonObject::ckUpdateString(json,"requests[1].addConditionalFormatRule.rule.ranges[0].sheetId",sheetId)
CkJsonObject::ckUpdateInt(json,"requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex",10)
CkJsonObject::ckUpdateInt(json,"requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex",11)
CkJsonObject::ckUpdateNumber(json,"requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.8")
CkJsonObject::ckUpdateNumber(json,"requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8")
CkJsonObject::ckUpdateString(json,"requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type","NUMBER")
CkJsonObject::ckUpdateString(json,"requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value","0")
CkJsonObject::ckUpdateNumber(json,"requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue","0.9")
CkJsonObject::ckUpdateNumber(json,"requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.5")
CkJsonObject::ckUpdateNumber(json,"requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red","0.5")
CkJsonObject::ckUpdateString(json,"requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type","NUMBER")
CkJsonObject::ckUpdateString(json,"requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value","256")
CkJsonObject::ckUpdateInt(json,"requests[1].addConditionalFormatRule.index",1)
; Adds the "Authorization: Bearer ACCESS_TOKEN" header.
CkHttp::setCkAuthToken(http, "ACCESS_TOKEN")
CkHttp::ckSetRequestHeader(http,"Content-Type","application/json")
resp.i = CkHttpResponse::ckCreate()
If resp.i = 0
Debug "Failed to create object."
ProcedureReturn
EndIf
success = CkHttp::ckHttpJson(http,"POST","https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate",json,"application/json",resp)
If success = 0
Debug CkHttp::ckLastErrorText(http)
CkHttp::ckDispose(http)
CkJsonObject::ckDispose(json)
CkHttpResponse::ckDispose(resp)
ProcedureReturn
EndIf
Debug "Status code: " + Str(CkHttpResponse::ckStatusCode(resp))
Debug "Response body:"
Debug CkHttpResponse::ckBodyStr(resp)
CkHttp::ckDispose(http)
CkJsonObject::ckDispose(json)
CkHttpResponse::ckDispose(resp)
ProcedureReturn
EndProcedure