(Visual FoxPro) Google Sheets - Create a New Spreadsheet

Demonstrates how to create a new and empty spreadsheet.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

LOCAL loJsonToken
LOCAL lnSuccess
LOCAL loHttp
LOCAL loJson
LOCAL loResp
LOCAL i
LOCAL lnCount_i
LOCAL lcSpreadsheetId
LOCAL lcPropertiesTitle
LOCAL lcPropertiesLocale
LOCAL lcPropertiesAutoRecalc
LOCAL lcPropertiesTimeZone
LOCAL lnPropertiesDefaultFormatBackgroundColorRed
LOCAL lnPropertiesDefaultFormatBackgroundColorGreen
LOCAL lnPropertiesDefaultFormatBackgroundColorBlue
LOCAL lnPropertiesDefaultFormatPaddingTop
LOCAL lnPropertiesDefaultFormatPaddingRight
LOCAL lnPropertiesDefaultFormatPaddingBottom
LOCAL lnPropertiesDefaultFormatPaddingLeft
LOCAL lcPropertiesDefaultFormatVerticalAlignment
LOCAL lcPropertiesDefaultFormatWrapStrategy
LOCAL lcPropertiesDefaultFormatTextFormatFontFamily
LOCAL lnPropertiesDefaultFormatTextFormatFontSize
LOCAL lnPropertiesDefaultFormatTextFormatBold
LOCAL lnPropertiesDefaultFormatTextFormatItalic
LOCAL lnPropertiesDefaultFormatTextFormatStrikethrough
LOCAL lnPropertiesDefaultFormatTextFormatUnderline
LOCAL lcSpreadsheetUrl
LOCAL lnPropertiesSheetId
LOCAL lnPropertiesIndex
LOCAL lcPropertiesSheetType
LOCAL lnPropertiesGridPropertiesRowCount
LOCAL lnPropertiesGridPropertiesColumnCount

* 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..
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.JsonObject')
loJsonToken = CreateObject('Chilkat.JsonObject')
lnSuccess = loJsonToken.LoadFile("qa_data/tokens/googleSheets.json")
IF (loJsonToken.HasMember("access_token") = 0) THEN
    ? "No access token found."
    RELEASE loJsonToken
    CANCEL
ENDIF

* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.Http')
loHttp = CreateObject('Chilkat.Http')
loHttp.AuthToken = loJsonToken.StringOf("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:
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.JsonObject')
loJson = CreateObject('Chilkat.JsonObject')
loJson.UpdateString("sheets[0].properties.title","Sample Tab")
loJson.UpdateString("properties.title","Create Spreadsheet using Sheets API v4")

* Send the POST to create the new Google spreadsheet.
loResp = loHttp.PostJson3("https://sheets.googleapis.com/v4/spreadsheets","application/json",loJson)
IF (loHttp.LastMethodSuccess <> 1) THEN
    ? loHttp.LastErrorText
    RELEASE loJsonToken
    RELEASE loHttp
    RELEASE loJson
    CANCEL
ENDIF

? "response status code = " + STR(loResp.StatusCode)
? "response JSON:"

loJson.Load(loResp.BodyStr)
loJson.EmitCompact = 0
? loJson.Emit()

RELEASE loResp

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

lcSpreadsheetId = loJson.StringOf("spreadsheetId")
lcPropertiesTitle = loJson.StringOf("properties.title")
lcPropertiesLocale = loJson.StringOf("properties.locale")
lcPropertiesAutoRecalc = loJson.StringOf("properties.autoRecalc")
lcPropertiesTimeZone = loJson.StringOf("properties.timeZone")
lnPropertiesDefaultFormatBackgroundColorRed = loJson.IntOf("properties.defaultFormat.backgroundColor.red")
lnPropertiesDefaultFormatBackgroundColorGreen = loJson.IntOf("properties.defaultFormat.backgroundColor.green")
lnPropertiesDefaultFormatBackgroundColorBlue = loJson.IntOf("properties.defaultFormat.backgroundColor.blue")
lnPropertiesDefaultFormatPaddingTop = loJson.IntOf("properties.defaultFormat.padding.top")
lnPropertiesDefaultFormatPaddingRight = loJson.IntOf("properties.defaultFormat.padding.right")
lnPropertiesDefaultFormatPaddingBottom = loJson.IntOf("properties.defaultFormat.padding.bottom")
lnPropertiesDefaultFormatPaddingLeft = loJson.IntOf("properties.defaultFormat.padding.left")
lcPropertiesDefaultFormatVerticalAlignment = loJson.StringOf("properties.defaultFormat.verticalAlignment")
lcPropertiesDefaultFormatWrapStrategy = loJson.StringOf("properties.defaultFormat.wrapStrategy")
lcPropertiesDefaultFormatTextFormatFontFamily = loJson.StringOf("properties.defaultFormat.textFormat.fontFamily")
lnPropertiesDefaultFormatTextFormatFontSize = loJson.IntOf("properties.defaultFormat.textFormat.fontSize")
lnPropertiesDefaultFormatTextFormatBold = loJson.BoolOf("properties.defaultFormat.textFormat.bold")
lnPropertiesDefaultFormatTextFormatItalic = loJson.BoolOf("properties.defaultFormat.textFormat.italic")
lnPropertiesDefaultFormatTextFormatStrikethrough = loJson.BoolOf("properties.defaultFormat.textFormat.strikethrough")
lnPropertiesDefaultFormatTextFormatUnderline = loJson.BoolOf("properties.defaultFormat.textFormat.underline")
lcSpreadsheetUrl = loJson.StringOf("spreadsheetUrl")
i = 0
lnCount_i = loJson.SizeOfArray("sheets")
DO WHILE (i < lnCount_i)
    loJson.I = i
    lnPropertiesSheetId = loJson.IntOf("sheets[i].properties.sheetId")
    lcPropertiesTitle = loJson.StringOf("sheets[i].properties.title")
    lnPropertiesIndex = loJson.IntOf("sheets[i].properties.index")
    lcPropertiesSheetType = loJson.StringOf("sheets[i].properties.sheetType")
    lnPropertiesGridPropertiesRowCount = loJson.IntOf("sheets[i].properties.gridProperties.rowCount")
    lnPropertiesGridPropertiesColumnCount = loJson.IntOf("sheets[i].properties.gridProperties.columnCount")
    i = i + 1
ENDDO

RELEASE loJsonToken
RELEASE loHttp
RELEASE loJson