|
(DataFlex) Google Sheets - Append Values to an Existing Spreadsheet
Appends values to an existing Google spreadsheet. Note: This example requires Chilkat v11.0.0 or greater.
Use ChilkatAx-win32.pkg
Procedure Test
Boolean iSuccess
Variant vJson
Token Handle hoJsonToken
Handle hoHttp
Variant vJson
Handle hoJson
Variant vResp
Handle hoResp
String sTemp1
Integer iTemp1
Boolean bTemp1
Move False To iSuccess
// 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..
Get Create (RefClass(cComChilkatJsonObject)) To hoJsonToken
If (Not(IsComObjectCreated(hoJsonToken))) Begin
Send CreateComObject of hoJsonToken
End
Get ComLoadFile Of hoJsonToken "qa_data/tokens/googleSheets.json" To iSuccess
Get ComHasMember Of hoJsonToken "access_token" To bTemp1
If (bTemp1 = False) Begin
Showln "No access token found."
Procedure_Return
End
Get Create (RefClass(cComChilkatHttp)) To hoHttp
If (Not(IsComObjectCreated(hoHttp))) Begin
Send CreateComObject of hoHttp
End
Get ComStringOf Of hoJsonToken "access_token" To sTemp1
Set ComAuthToken Of hoHttp To sTemp1
// 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"
// ]
// ]
// }
Get Create (RefClass(cComChilkatJsonObject)) To hoJson
If (Not(IsComObjectCreated(hoJson))) Begin
Send CreateComObject of hoJson
End
Get ComUpdateString Of hoJson "range" "Sheet1!A1:B5" To iSuccess
Get ComUpdateString Of hoJson "majorDimension" "ROWS" To iSuccess
Set ComI Of hoJson To 0
Set ComJ Of hoJson To 1
Get ComUpdateString Of hoJson "values[i][j]" "Paint" To iSuccess
Set ComJ Of hoJson To 1
Get ComUpdateString Of hoJson "values[i][j]" "$100" To iSuccess
Set ComI Of hoJson To 1
Set ComJ Of hoJson To 0
Get ComUpdateString Of hoJson "values[i][j]" "Brakes" To iSuccess
Set ComJ Of hoJson To 1
Get ComUpdateString Of hoJson "values[i][j]" "$100" To iSuccess
Set ComI Of hoJson To 2
Set ComJ Of hoJson To 0
Get ComUpdateString Of hoJson "values[i][j]" "Totals" To iSuccess
Set ComJ Of hoJson To 1
Get ComUpdateString Of hoJson "values[i][j]" "$335.50" To iSuccess
Set ComEmitCompact Of hoJson To False
Get ComEmit Of hoJson To sTemp1
Showln sTemp1
// Send the POST to:
// https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED
Get ComSetUrlVar Of hoHttp "spreadsheetId" "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA" To iSuccess
Get ComSetUrlVar Of hoHttp "range" "Sheet1!A1:B5" To iSuccess
Get Create (RefClass(cComChilkatHttpResponse)) To hoResp
If (Not(IsComObjectCreated(hoResp))) Begin
Send CreateComObject of hoResp
End
Get pvComObject of hoJson to vJson
Get pvComObject of hoResp to vResp
Get ComHttpJson Of hoHttp "POST" "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED" vJson "application/json" vResp To iSuccess
If (iSuccess = False) Begin
Get ComLastErrorText Of hoHttp To sTemp1
Showln sTemp1
Procedure_Return
End
Get ComStatusCode Of hoResp To iTemp1
Showln "response status code = " iTemp1
Get ComBodyStr Of hoResp To sTemp1
Showln "response JSON = " sTemp1
// 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:
//
End_Procedure
|