|
|
(VBScript) Google Sheets - Append Values to an Existing Spreadsheet
Appends values to an existing Google spreadsheet.
Dim fso, outFile
Set fso = CreateObject("Scripting.FileSystemObject")
'Create a Unicode (utf-16) output text file.
Set outFile = fso.CreateTextFile("output.txt", True, True)
' 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")
set jsonToken = CreateObject("Chilkat.JsonObject")
success = jsonToken.LoadFile("qa_data/tokens/googleSheets.json")
If (jsonToken.HasMember("access_token") = 0) Then
outFile.WriteLine("No access token found.")
WScript.Quit
End If
' For versions of Chilkat < 10.0.0, use CreateObject("Chilkat_9_5_0.Http")
set http = CreateObject("Chilkat.Http")
http.AuthToken = jsonToken.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")
set json = CreateObject("Chilkat.JsonObject")
success = json.UpdateString("range","Sheet1!A1:B5")
success = json.UpdateString("majorDimension","ROWS")
json.I = 0
json.J = 1
success = json.UpdateString("values[i][j]","Paint")
json.J = 1
success = json.UpdateString("values[i][j]","$100")
json.I = 1
json.J = 0
success = json.UpdateString("values[i][j]","Brakes")
json.J = 1
success = json.UpdateString("values[i][j]","$100")
json.I = 2
json.J = 0
success = json.UpdateString("values[i][j]","Totals")
json.J = 1
success = json.UpdateString("values[i][j]","$335.50")
json.EmitCompact = 0
outFile.WriteLine(json.Emit())
' Send the POST to:
' https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED
success = http.SetUrlVar("spreadsheetId","1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA")
success = http.SetUrlVar("range","Sheet1!A1:B5")
' resp is a Chilkat.HttpResponse
Set resp = http.PostJson("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",json.Emit())
If (http.LastMethodSuccess <> 1) Then
outFile.WriteLine(http.LastErrorText)
WScript.Quit
End If
outFile.WriteLine("response status code = " & resp.StatusCode)
outFile.WriteLine("response JSON = " & resp.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:
' 
outFile.Close
|
