|
|
(Visual FoxPro) Google Sheets - Append Values to an Existing Spreadsheet
Appends values to an existing Google spreadsheet.
LOCAL loJsonToken
LOCAL lnSuccess
LOCAL loHttp
LOCAL loJson
LOCAL loResp
* 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")
* 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"
* ]
* ]
* }
* For versions of Chilkat < 10.0.0, use CreateObject('Chilkat_9_5_0.JsonObject')
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 = loHttp.PostJson("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",loJson.Emit())
IF (loHttp.LastMethodSuccess <> 1) THEN
? loHttp.LastErrorText
RELEASE loJsonToken
RELEASE loHttp
RELEASE loJson
CANCEL
ENDIF
? "response status code = " + STR(loResp.StatusCode)
? "response JSON = " + loResp.BodyStr
RELEASE loResp
* 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
|
