Range Values - Append
Append values to a Google spreadsheet range using SQL Server
- See SQLHTTP easy setup for Google Sheets
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
CREATE PROCEDURE usp_Google_SpreadSheet_Range_Values_Append( @Profile nvarchar(100), @spreadsheetId varchar(MAX), @TabName nvarchar(50), @StartCell nvarchar(50), @EndCell nvarchar(50), @valueInputOption varchar(30) = NULL, @insertDataOption varchar(30) = NULL, @includeValuesInResponse bit = 0, @responseValueRenderOption varchar(20) = 'FORMATTED_VALUE', @responseDateTimeRenderOption varchar(20) = 'SERIAL_NUMBER', @MajorDimension varchar(30) = 'ROWS', @Values nvarchar(MAX), @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @Text_includeValuesInResponse varchar(5) SET @Text_includeValuesInResponse = CASE WHEN @includeValuesInResponse = 0 THEN 'false' ELSE 'true' END DECLARE @URL nvarchar(MAX) DECLARE @QueryString nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Range nvarchar(MAX) DECLARE @Body nvarchar(MAX) SET @Range = @TabName + '!' + @StartCell + ':' + @EndCell SET @URL = 'https://sheets.googleapis.com/v4' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'spreadsheets', @spreadsheetId, 'values', @Range SET @URL = @URL + ':append' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'includeValuesInResponse', @Text_includeValuesInResponse, 'valueInputOption', @valueInputOption, 'insertDataOption', @insertDataOption, 'responseValueRenderOption', @responseValueRenderOption, 'responseDateTimeRenderOption', @responseDateTimeRenderOption SET @URL = @URL + @QueryString SET @Body = '{ "range": "' + @Range + '", "majorDimension": "'+ @MajorDimension +'", "values": ' + @Values + ' }' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Google_Auth_Header @Profile, @HTTPSessionID EXEC SQLHTTP.net.HTTPRequest @HTTPSessionID, @URL = @URL, @Method = 'POST', @Body = @Body, @ContentType = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Values nvarchar(MAX) DECLARE @Profile nvarchar(100) SET @Profile = 'My google' --Google API Bearer Token expires after an hour EXEC usp_Google_Auth_Refresh @Profile SET @Values = '[ ["Wheel", "$20.50", "4", "3/1/2016"], ["Door", "$15", "2", "3/15/2016"], ["Engine", "$100", "1", "3/20/2016"] ]' EXEC usp_Google_SpreadSheet_Range_Values_Append @Profile = @Profile, @spreadsheetId = '1onNp_XckkNU7commGgc_tmKVyK-G5Xa_Bi7EoPvf6CI', @TabName = 'Sheet2', @StartCell = 'A2', @EndCell = 'D4', @valueInputOption = 'USER_ENTERED', @insertDataOption = 'INSERT_ROWS', @Values = @Values, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject' -- SELECT T.C.value(N'@spreadsheetId', N'nvarchar(MAX)') AS [spreadsheetId] ,T.C.value(N'updates[1]/@updatedRange', N'nvarchar(MAX)') AS [updatedRange] ,T.C.value(N'updates[1]/@updatedRows', N'nvarchar(MAX)') AS [updatedRows] ,T.C.value(N'updates[1]/@updatedColumns', N'nvarchar(MAX)') AS [updatedColumns] ,T.C.value(N'updates[1]/@updatedCells', N'nvarchar(MAX)') AS [updatedCells] FROM @X.nodes(N'/JsonObject') T(C) END |
1 2 3 4 5 |
spreadsheetId updatedRange updatedRows updatedColumns updatedCells --------------------- -------------- ------------ --------------- ------------- 1onNp_XckkNU7comm... Sheet2!A2:D4 3 4 12 |
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.