Lua

Google Sheets Conditional Formatting - Color Gradient

See more Google Sheets Examples

Add a conditional color gradient across a row Lua

local chilkat = require("chilkat")

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

local http = chilkat.Http.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
--       }
--     }
--   ]
-- }

local sheetId = "YOUR_SHEET_ID"

local json = chilkat.JsonObject.new()
json:update_string("requests[0].addConditionalFormatRule.rule.ranges[0].sheetId", sheetId)
json:update_int("requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex", 9)
json:update_int("requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex", 10)
json:update_number("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green", "0.2")
json:update_number("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red", "0.8")
json:update_string("requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type", "MIN")
json:update_number("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green", "0.9")
json:update_string("requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type", "MAX")
json:update_int("requests[0].addConditionalFormatRule.index", 0)
json:update_string("requests[1].addConditionalFormatRule.rule.ranges[0].sheetId", sheetId)
json:update_int("requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex", 10)
json:update_int("requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex", 11)
json:update_number("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green", "0.8")
json:update_number("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red", "0.8")
json:update_string("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type", "NUMBER")
json:update_string("requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value", "0")
json:update_number("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue", "0.9")
json:update_number("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green", "0.5")
json:update_number("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red", "0.5")
json:update_string("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type", "NUMBER")
json:update_string("requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value", "256")
json:update_int("requests[1].addConditionalFormatRule.index", 1)

-- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
http.auth_token = "ACCESS_TOKEN"
http:set_request_header("Content-Type", "application/json")

local resp = chilkat.HttpResponse.new()
local success = http:http_json("POST", "https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate", json, "application/json", resp)
if not success then
    print(http.last_error_text)
    return
end

print("Status code: " .. resp.status_code)
print("Response body:")
print(resp.body_str)