Visual FoxPro
Google Sheets - Append Values to an Existing Spreadsheet

Appends values to an existing Google spreadsheet.
Chilkat Visual FoxPro Downloads

Visual FoxPro
LOCAL lnSuccess
LOCAL loJsonToken
LOCAL loHttp
LOCAL loJson
LOCAL loResp

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

* To append values to an existing spreadsheet, our HTTP request body will
* contain JSON in the format of a "ValueRange".  For example, the spreadsheet we'll be
* adding to in this example looks like this:

* 

* The JSON ValueRange for the cells in the above spreadsheet is:
* {
*   "range": "Sheet1!A1:B5",
*   "majorDimension": "ROWS",
*   "values": [
*     [
*       "Item",
*       "Cost"
*     ],
*     [
*       "Wheel",
*       "$20.50"
*     ],
*     [
*       "Door",
*       "$15"
*     ],
*     [
*       "Engine",
*       "$100"
*     ],
*     [
*       "Totals",
*       "$135.50"
*     ]
*   ]
* }

* This example will append 6 cells (3 rows / 2 columns).
* We'll be appending the following:
* 
* "Paint", "$100"
* "Brakes", "$100"
* "New Total", "$335.50"
* 

* The range of cells we'll be appending is A1:B5
* Therefore, the ValueRange JSON we'll be sending in our POST body is:

* {
*   "range": "Sheet1!A1:B5",
*   "majorDimension": "ROWS",
*   "values": [
*     [
*       "Paint",
*       "$100"
*     ],
*     [
*       "Brakes",
*       "$100"
*     ],
*     [
*       "New Total",
*       "$335.50"
*     ]
*   ]
* }

loJson = CreateObject('Chilkat.JsonObject')
loJson.UpdateString("range","Sheet1!A1:B5")
loJson.UpdateString("majorDimension","ROWS")

loJson.I = 0
loJson.J = 1
loJson.UpdateString("values[i][j]","Paint")
loJson.J = 1
loJson.UpdateString("values[i][j]","$100")

loJson.I = 1
loJson.J = 0
loJson.UpdateString("values[i][j]","Brakes")
loJson.J = 1
loJson.UpdateString("values[i][j]","$100")

loJson.I = 2
loJson.J = 0
loJson.UpdateString("values[i][j]","Totals")
loJson.J = 1
loJson.UpdateString("values[i][j]","$335.50")

loJson.EmitCompact = 0
? loJson.Emit()

* Send the POST to:
* https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED

loHttp.SetUrlVar("spreadsheetId","1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA")
loHttp.SetUrlVar("range","Sheet1!A1:B5")
loResp = CreateObject('Chilkat.HttpResponse')
lnSuccess = loHttp.HttpJson("POST","https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",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 = " + loResp.BodyStr

* Sample output:
* 
* response status code = 200
* response JSON = {
*   "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
*   "tableRange": "Sheet1!A1:B5",
*   "updates": {
*     "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
*     "updatedRange": "Sheet1!A6:B8",
*     "updatedRows": 3,
*     "updatedColumns": 2,
*     "updatedCells": 6
*   }
* }
* 

* Our Google Spreadsheet now looks like this:
* 

RELEASE loJsonToken
RELEASE loHttp
RELEASE loJson
RELEASE loResp