SQL Server
SQL Server
Google Sheets - Create a New Spreadsheet
See more Google Sheets Examples
Demonstrates how to create a new and empty spreadsheet.Chilkat 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 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
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
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
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- Create the following JSON:
-- The JSON code can be generated using this online tool: Generate JSON create code
-- {
-- "sheets": [
-- {
-- "properties": {
-- "title": "Sample Tab"
-- }
-- }
-- ],
-- "properties": {
-- "title": "Create Spreadsheet using Sheets API v4"
-- }
-- }
-- This code generates the above JSON:
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'sheets[0].properties.title', 'Sample Tab'
EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'properties.title', 'Create Spreadsheet using Sheets API v4'
-- Send the POST to create the new Google spreadsheet.
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', @json, 'application/json', @resp
IF @success = 0
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
EXEC @hr = sp_OADestroy @resp
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
PRINT 'response JSON:'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- A sample response is shown below.
-- To generate the parsing source code for a JSON response, paste
-- the JSON into this online tool: Generate JSON parsing code
-- {
-- "spreadsheetId": "1ueEQu3WDBkIAOUhzLnY4zr6JO5SrJx0dQ-YkQlUVYD0",
-- "properties": {
-- "title": "Create Spreadsheet using Sheets API v4",
-- "locale": "en_US",
-- "autoRecalc": "ON_CHANGE",
-- "timeZone": "Etc/GMT",
-- "defaultFormat": {
-- "backgroundColor": {
-- "red": 1,
-- "green": 1,
-- "blue": 1
-- },
-- "padding": {
-- "top": 2,
-- "right": 3,
-- "bottom": 2,
-- "left": 3
-- },
-- "verticalAlignment": "BOTTOM",
-- "wrapStrategy": "OVERFLOW_CELL",
-- "textFormat": {
-- "foregroundColor": {},
-- "fontFamily": "arial,sans,sans-serif",
-- "fontSize": 10,
-- "bold": false,
-- "italic": false,
-- "strikethrough": false,
-- "underline": false
-- }
-- }
-- },
-- "sheets": [
-- {
-- "properties": {
-- "sheetId": 1629642057,
-- "title": "Sample Tab",
-- "index": 0,
-- "sheetType": "GRID",
-- "gridProperties": {
-- "rowCount": 1000,
-- "columnCount": 26
-- }
-- }
-- }
-- ],
-- "spreadsheetUrl": "https://docs.google.com/spreadsheets/d/1ueEQu3WDBkIAOUhzLnY4zr6JO5SrJx0dQ-YkQlUVYD0/edit"
-- }
--
DECLARE @i int
DECLARE @count_i int
DECLARE @spreadsheetId nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @spreadsheetId OUT, 'spreadsheetId'
DECLARE @propertiesTitle nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesTitle OUT, 'properties.title'
DECLARE @propertiesLocale nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesLocale OUT, 'properties.locale'
DECLARE @propertiesAutoRecalc nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesAutoRecalc OUT, 'properties.autoRecalc'
DECLARE @propertiesTimeZone nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesTimeZone OUT, 'properties.timeZone'
DECLARE @propertiesDefaultFormatBackgroundColorRed int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatBackgroundColorRed OUT, 'properties.defaultFormat.backgroundColor.red'
DECLARE @propertiesDefaultFormatBackgroundColorGreen int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatBackgroundColorGreen OUT, 'properties.defaultFormat.backgroundColor.green'
DECLARE @propertiesDefaultFormatBackgroundColorBlue int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatBackgroundColorBlue OUT, 'properties.defaultFormat.backgroundColor.blue'
DECLARE @propertiesDefaultFormatPaddingTop int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatPaddingTop OUT, 'properties.defaultFormat.padding.top'
DECLARE @propertiesDefaultFormatPaddingRight int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatPaddingRight OUT, 'properties.defaultFormat.padding.right'
DECLARE @propertiesDefaultFormatPaddingBottom int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatPaddingBottom OUT, 'properties.defaultFormat.padding.bottom'
DECLARE @propertiesDefaultFormatPaddingLeft int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatPaddingLeft OUT, 'properties.defaultFormat.padding.left'
DECLARE @propertiesDefaultFormatVerticalAlignment nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesDefaultFormatVerticalAlignment OUT, 'properties.defaultFormat.verticalAlignment'
DECLARE @propertiesDefaultFormatWrapStrategy nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesDefaultFormatWrapStrategy OUT, 'properties.defaultFormat.wrapStrategy'
DECLARE @propertiesDefaultFormatTextFormatFontFamily nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesDefaultFormatTextFormatFontFamily OUT, 'properties.defaultFormat.textFormat.fontFamily'
DECLARE @propertiesDefaultFormatTextFormatFontSize int
EXEC sp_OAMethod @json, 'IntOf', @propertiesDefaultFormatTextFormatFontSize OUT, 'properties.defaultFormat.textFormat.fontSize'
DECLARE @propertiesDefaultFormatTextFormatBold int
EXEC sp_OAMethod @json, 'BoolOf', @propertiesDefaultFormatTextFormatBold OUT, 'properties.defaultFormat.textFormat.bold'
DECLARE @propertiesDefaultFormatTextFormatItalic int
EXEC sp_OAMethod @json, 'BoolOf', @propertiesDefaultFormatTextFormatItalic OUT, 'properties.defaultFormat.textFormat.italic'
DECLARE @propertiesDefaultFormatTextFormatStrikethrough int
EXEC sp_OAMethod @json, 'BoolOf', @propertiesDefaultFormatTextFormatStrikethrough OUT, 'properties.defaultFormat.textFormat.strikethrough'
DECLARE @propertiesDefaultFormatTextFormatUnderline int
EXEC sp_OAMethod @json, 'BoolOf', @propertiesDefaultFormatTextFormatUnderline OUT, 'properties.defaultFormat.textFormat.underline'
DECLARE @spreadsheetUrl nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @spreadsheetUrl OUT, 'spreadsheetUrl'
SELECT @i = 0
EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'sheets'
WHILE (@i < @count_i)
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
DECLARE @propertiesSheetId int
EXEC sp_OAMethod @json, 'IntOf', @propertiesSheetId OUT, 'sheets[i].properties.sheetId'
EXEC sp_OAMethod @json, 'StringOf', @propertiesTitle OUT, 'sheets[i].properties.title'
DECLARE @propertiesIndex int
EXEC sp_OAMethod @json, 'IntOf', @propertiesIndex OUT, 'sheets[i].properties.index'
DECLARE @propertiesSheetType nvarchar(4000)
EXEC sp_OAMethod @json, 'StringOf', @propertiesSheetType OUT, 'sheets[i].properties.sheetType'
DECLARE @propertiesGridPropertiesRowCount int
EXEC sp_OAMethod @json, 'IntOf', @propertiesGridPropertiesRowCount OUT, 'sheets[i].properties.gridProperties.rowCount'
DECLARE @propertiesGridPropertiesColumnCount int
EXEC sp_OAMethod @json, 'IntOf', @propertiesGridPropertiesColumnCount OUT, 'sheets[i].properties.gridProperties.columnCount'
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @resp
END
GO