Visual FoxPro

Google Sheets - Create a New Spreadsheet

See more Google Sheets Examples

Demonstrates how to create a new and empty spreadsheet. Visual FoxPro

LOCAL lnSuccess
LOCAL loJsonToken
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

lnSuccess = 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..
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

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:
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 = CreateObject('Chilkat.HttpResponse')
lnSuccess = loHttp.HttpJson("POST","https://sheets.googleapis.com/v4/spreadsheets",loJson,"application/json",loResp)
IF (lnSuccess = 0) THEN
    ? loHttp.LastErrorText
    RELEASE loJsonToken
    RELEASE loHttp
    RELEASE loJson
    RELEASE loResp
    CANCEL
ENDIF

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

loJson.Load(loResp.BodyStr)
loJson.EmitCompact = 0
? loJson.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"
* }
* 

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
RELEASE loResp