Perl

Google Sheets Conditional Formatting - Color Gradient

See more Google Sheets Examples

Add a conditional color gradient across a row Perl

use chilkat();

$success = 0;

# This example assumes the Chilkat API to have been previously unlocked.
# See Global Unlock Sample for sample code.

$http = chilkat::CkHttp->new();

# 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 = "YOUR_SHEET_ID";

$json = chilkat::CkJsonObject->new();
$json->UpdateString("requests[0].addConditionalFormatRule.rule.ranges[0].sheetId",$sheetId);
$json->UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex",9);
$json->UpdateInt("requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex",10);
$json->UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.2");
$json->UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8");
$json->UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type","MIN");
$json->UpdateNumber("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.9");
$json->UpdateString("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type","MAX");
$json->UpdateInt("requests[0].addConditionalFormatRule.index",0);
$json->UpdateString("requests[1].addConditionalFormatRule.rule.ranges[0].sheetId",$sheetId);
$json->UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex",10);
$json->UpdateInt("requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex",11);
$json->UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green","0.8");
$json->UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red","0.8");
$json->UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type","NUMBER");
$json->UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value","0");
$json->UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue","0.9");
$json->UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green","0.5");
$json->UpdateNumber("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red","0.5");
$json->UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type","NUMBER");
$json->UpdateString("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value","256");
$json->UpdateInt("requests[1].addConditionalFormatRule.index",1);

# Adds the "Authorization: Bearer ACCESS_TOKEN" header.
$http->put_AuthToken("ACCESS_TOKEN");
$http->SetRequestHeader("Content-Type","application/json");

$resp = chilkat::CkHttpResponse->new();
$success = $http->HttpJson("POST","https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate",$json,"application/json",$resp);
if ($success == 0) {
    print $http->lastErrorText() . "\r\n";
    exit;
}

print "Status code: " . $resp->get_StatusCode() . "\r\n";
print "Response body:" . "\r\n";
print $resp->bodyStr() . "\r\n";