|
|
(SQL Server) Google Sheets - Append Values to an Existing Spreadsheet
Appends values to an existing Google spreadsheet.
-- 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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
-- This example uses a previously obtained access token having permission for the
-- Google Sheets scope.
-- In this example, Get Google Sheets OAuth2 Access Token, the access
-- token was saved to a JSON file. This example fetches the access token from the file..
DECLARE @jsonToken int
-- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
DECLARE @success int
EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/googleSheets.json'
EXEC sp_OAMethod @jsonToken, 'HasMember', @iTmp0 OUT, 'access_token'
IF @iTmp0 = 0
BEGIN
PRINT 'No access token found.'
EXEC @hr = sp_OADestroy @jsonToken
RETURN
END
DECLARE @http int
-- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- To append values to an existing spreadsheet, our HTTP request body will
-- contain JSON in the format of a "ValueRange". For example, the spreadsheet we'll be
-- adding to in this example looks like this:
-- 
-- The JSON ValueRange for the cells in the above spreadsheet is:
-- {
-- "range": "Sheet1!A1:B5",
-- "majorDimension": "ROWS",
-- "values": [
-- [
-- "Item",
-- "Cost"
-- ],
-- [
-- "Wheel",
-- "$20.50"
-- ],
-- [
-- "Door",
-- "$15"
-- ],
-- [
-- "Engine",
-- "$100"
-- ],
-- [
-- "Totals",
-- "$135.50"
-- ]
-- ]
-- }
-- This example will append 6 cells (3 rows / 2 columns).
-- We'll be appending the following:
--
-- "Paint", "$100"
-- "Brakes", "$100"
-- "New Total", "$335.50"
--
-- The range of cells we'll be appending is A1:B5
-- Therefore, the ValueRange JSON we'll be sending in our POST body is:
-- {
-- "range": "Sheet1!A1:B5",
-- "majorDimension": "ROWS",
-- "values": [
-- [
-- "Paint",
-- "$100"
-- ],
-- [
-- "Brakes",
-- "$100"
-- ],
-- [
-- "New Total",
-- "$335.50"
-- ]
-- ]
-- }
DECLARE @json int
-- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'range', 'Sheet1!A1:B5'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'majorDimension', 'ROWS'
EXEC sp_OASetProperty @json, 'I', 0
EXEC sp_OASetProperty @json, 'J', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values[i][j]', 'Paint'
EXEC sp_OASetProperty @json, 'J', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values[i][j]', '$100'
EXEC sp_OASetProperty @json, 'I', 1
EXEC sp_OASetProperty @json, 'J', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values[i][j]', 'Brakes'
EXEC sp_OASetProperty @json, 'J', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values[i][j]', '$100'
EXEC sp_OASetProperty @json, 'I', 2
EXEC sp_OASetProperty @json, 'J', 0
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values[i][j]', 'Totals'
EXEC sp_OASetProperty @json, 'J', 1
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'values[i][j]', '$335.50'
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- Send the POST to:
-- https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'spreadsheetId', '1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'range', 'Sheet1!A1:B5'
DECLARE @resp int
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @http, 'PostJson', @resp OUT, 'https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED', @sTmp0
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT 'response JSON = ' + @sTmp0
EXEC @hr = sp_OADestroy @resp
-- Sample output:
--
-- response status code = 200
-- response JSON = {
-- "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
-- "tableRange": "Sheet1!A1:B5",
-- "updates": {
-- "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
-- "updatedRange": "Sheet1!A6:B8",
-- "updatedRows": 3,
-- "updatedColumns": 2,
-- "updatedCells": 6
-- }
-- }
--
-- Our Google Spreadsheet now looks like this:
-- 
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
END
GO
|
