SQL Server
SQL Server
Google Sheets Conditional Formatting - Color Gradient
See more Google Sheets Examples
Add a conditional color gradient across a rowChilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
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
-- }
-- }
-- ]
-- }
DECLARE @sheetId nvarchar(4000)
SELECT @sheetId = 'YOUR_SHEET_ID'
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[0].addConditionalFormatRule.rule.ranges[0].sheetId', @sheetId
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'requests[0].addConditionalFormatRule.rule.ranges[0].startRowIndex', 9
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'requests[0].addConditionalFormatRule.rule.ranges[0].endRowIndex', 10
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.green', '0.2'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.color.red', '0.8'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[0].addConditionalFormatRule.rule.gradientRule.minpoint.type', 'MIN'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green', '0.9'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[0].addConditionalFormatRule.rule.gradientRule.maxpoint.type', 'MAX'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'requests[0].addConditionalFormatRule.index', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[1].addConditionalFormatRule.rule.ranges[0].sheetId', @sheetId
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'requests[1].addConditionalFormatRule.rule.ranges[0].startRowIndex', 10
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'requests[1].addConditionalFormatRule.rule.ranges[0].endRowIndex', 11
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.green', '0.8'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.color.red', '0.8'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.type', 'NUMBER'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.minpoint.value', '0'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.blue', '0.9'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.green', '0.5'
EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.color.red', '0.5'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.type', 'NUMBER'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'requests[1].addConditionalFormatRule.rule.gradientRule.maxpoint.value', '256'
EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'requests[1].addConditionalFormatRule.index', 1
-- Adds the "Authorization: Bearer ACCESS_TOKEN" header.
EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'
EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/json'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate', @json, 'application/json', @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Status code: ' + @iTmp0
PRINT 'Response body:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
END
GO