Lua

Google Sheets - Create a New Spreadsheet

See more Google Sheets Examples

Demonstrates how to create a new and empty spreadsheet. Lua

local chilkat = require("chilkat")

-- 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..
local jsonToken = chilkat.JsonObject.new()
local success = jsonToken:load_file("qa_data/tokens/googleSheets.json")
if not jsonToken:has_member("access_token") then
    print("No access token found.")
    return
end

local http = chilkat.Http.new()
http.auth_token = jsonToken:string_of("access_token")

-- 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:
local json = chilkat.JsonObject.new()
json:update_string("sheets[0].properties.title", "Sample Tab")
json:update_string("properties.title", "Create Spreadsheet using Sheets API v4")

-- Send the POST to create the new Google spreadsheet.
local resp = chilkat.HttpResponse.new()
success = http:http_json("POST", "https://sheets.googleapis.com/v4/spreadsheets", json, "application/json", resp)
if not success then
    print(http.last_error_text)
    return
end

print("response status code = " .. resp.status_code)
print("response JSON:")

json:load(resp.body_str)
json.emit_compact = false
print(json:emit())

-- 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"
-- }
--

local spreadsheetId = json:string_of("spreadsheetId")
local propertiesTitle = json:string_of("properties.title")
local propertiesLocale = json:string_of("properties.locale")
local propertiesAutoRecalc = json:string_of("properties.autoRecalc")
local propertiesTimeZone = json:string_of("properties.timeZone")
local propertiesDefaultFormatBackgroundColorRed = json:int_of("properties.defaultFormat.backgroundColor.red")
local propertiesDefaultFormatBackgroundColorGreen = json:int_of("properties.defaultFormat.backgroundColor.green")
local propertiesDefaultFormatBackgroundColorBlue = json:int_of("properties.defaultFormat.backgroundColor.blue")
local propertiesDefaultFormatPaddingTop = json:int_of("properties.defaultFormat.padding.top")
local propertiesDefaultFormatPaddingRight = json:int_of("properties.defaultFormat.padding.right")
local propertiesDefaultFormatPaddingBottom = json:int_of("properties.defaultFormat.padding.bottom")
local propertiesDefaultFormatPaddingLeft = json:int_of("properties.defaultFormat.padding.left")
local propertiesDefaultFormatVerticalAlignment = json:string_of("properties.defaultFormat.verticalAlignment")
local propertiesDefaultFormatWrapStrategy = json:string_of("properties.defaultFormat.wrapStrategy")
local propertiesDefaultFormatTextFormatFontFamily = json:string_of("properties.defaultFormat.textFormat.fontFamily")
local propertiesDefaultFormatTextFormatFontSize = json:int_of("properties.defaultFormat.textFormat.fontSize")
local propertiesDefaultFormatTextFormatBold = json:bool_of("properties.defaultFormat.textFormat.bold")
local propertiesDefaultFormatTextFormatItalic = json:bool_of("properties.defaultFormat.textFormat.italic")
local propertiesDefaultFormatTextFormatStrikethrough = json:bool_of("properties.defaultFormat.textFormat.strikethrough")
local propertiesDefaultFormatTextFormatUnderline = json:bool_of("properties.defaultFormat.textFormat.underline")
local spreadsheetUrl = json:string_of("spreadsheetUrl")
local i = 0
local count_i = json:size_of_array("sheets")
while i < count_i do
    json.i = i
    local propertiesSheetId = json:int_of("sheets[i].properties.sheetId")
    propertiesTitle = json:string_of("sheets[i].properties.title")
    local propertiesIndex = json:int_of("sheets[i].properties.index")
    local propertiesSheetType = json:string_of("sheets[i].properties.sheetType")
    local propertiesGridPropertiesRowCount = json:int_of("sheets[i].properties.gridProperties.rowCount")
    local propertiesGridPropertiesColumnCount = json:int_of("sheets[i].properties.gridProperties.columnCount")
    i = i + 1
end