DataFlex
DataFlex
Google Sheets Conditional Formatting - Color Gradient
See more Google Sheets Examples
Add a conditional color gradient across a rowChilkat DataFlex Downloads
Use ChilkatAx-win32.pkg
Procedure Test
Boolean iSuccess
Handle hoHttp
String sSheetId
Variant vJson
Handle hoJson
Variant vResp
Handle hoResp
String sTemp1
Integer iTemp1
Move False To iSuccess
// This example assumes the Chilkat API to have been previously unlocked.
// See Global Unlock Sample for sample code.
Get Create (RefClass(cComChilkatHttp)) To hoHttp
If (Not(IsComObjectCreated(hoHttp))) Begin
Send CreateComObject of hoHttp
End
// 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
// }
// }
// ]
// }
Move "YOUR_SHEET_ID" To sSheetId
Get Create (RefClass(cComChilkatJsonObject)) To hoJson
If (Not(IsComObjectCreated(hoJson))) Begin
Send CreateComObject of hoJson
End
Get ComUpdateString Of hoJson "requests[0].addConditionalFormatRule.rule.ranges[0].sheetId" sSheetId To iSuccess
Get ComUpdateInt Of hoJson "requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex" 9 To iSuccess
Get ComUpdateInt Of hoJson "requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex" 10 To iSuccess
Get ComUpdateNumber Of hoJson "requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green" "0.2" To iSuccess
Get ComUpdateNumber Of hoJson "requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red" "0.8" To iSuccess
Get ComUpdateString Of hoJson "requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type" "MIN" To iSuccess
Get ComUpdateNumber Of hoJson "requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green" "0.9" To iSuccess
Get ComUpdateString Of hoJson "requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type" "MAX" To iSuccess
Get ComUpdateInt Of hoJson "requests[0].addConditionalFormatRule.index" 0 To iSuccess
Get ComUpdateString Of hoJson "requests[1].addConditionalFormatRule.rule.ranges[0].sheetId" sSheetId To iSuccess
Get ComUpdateInt Of hoJson "requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex" 10 To iSuccess
Get ComUpdateInt Of hoJson "requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex" 11 To iSuccess
Get ComUpdateNumber Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green" "0.8" To iSuccess
Get ComUpdateNumber Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red" "0.8" To iSuccess
Get ComUpdateString Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type" "NUMBER" To iSuccess
Get ComUpdateString Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value" "0" To iSuccess
Get ComUpdateNumber Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue" "0.9" To iSuccess
Get ComUpdateNumber Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green" "0.5" To iSuccess
Get ComUpdateNumber Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red" "0.5" To iSuccess
Get ComUpdateString Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type" "NUMBER" To iSuccess
Get ComUpdateString Of hoJson "requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value" "256" To iSuccess
Get ComUpdateInt Of hoJson "requests[1].addConditionalFormatRule.index" 1 To iSuccess
// Adds the "Authorization: Bearer ACCESS_TOKEN" header.
Set ComAuthToken Of hoHttp To "ACCESS_TOKEN"
Send ComSetRequestHeader To hoHttp "Content-Type" "application/json"
Get Create (RefClass(cComChilkatHttpResponse)) To hoResp
If (Not(IsComObjectCreated(hoResp))) Begin
Send CreateComObject of hoResp
End
Get pvComObject of hoJson to vJson
Get pvComObject of hoResp to vResp
Get ComHttpJson Of hoHttp "POST" "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate" vJson "application/json" vResp To iSuccess
If (iSuccess = False) Begin
Get ComLastErrorText Of hoHttp To sTemp1
Showln sTemp1
Procedure_Return
End
Get ComStatusCode Of hoResp To iTemp1
Showln "Status code: " iTemp1
Showln "Response body:"
Get ComBodyStr Of hoResp To sTemp1
Showln sTemp1
End_Procedure