(Tcl) Google Sheets - Append Values to an Existing Spreadsheet

Appends values to an existing Google spreadsheet.

Chilkat Tcl Extension Downloads Chilkat Tcl Extension Downloads

load ./chilkat.dll

# 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..
set jsonToken [new_CkJsonObject]

set success [CkJsonObject_LoadFile $jsonToken "qa_data/tokens/googleSheets.json"]
if {[CkJsonObject_HasMember $jsonToken "access_token"] == 0} then {
    puts "No access token found."
    delete_CkJsonObject $jsonToken
    exit
}

set http [new_CkHttp]

CkHttp_put_AuthToken $http [CkJsonObject_stringOf $jsonToken "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"
#     ]
#   ]
# }

set json [new_CkJsonObject]

CkJsonObject_UpdateString $json "range" "Sheet1!A1:B5"
CkJsonObject_UpdateString $json "majorDimension" "ROWS"

CkJsonObject_put_I $json 0
CkJsonObject_put_J $json 1
CkJsonObject_UpdateString $json "values[i][j]" "Paint"
CkJsonObject_put_J $json 1
CkJsonObject_UpdateString $json "values[i][j]" "$100"

CkJsonObject_put_I $json 1
CkJsonObject_put_J $json 0
CkJsonObject_UpdateString $json "values[i][j]" "Brakes"
CkJsonObject_put_J $json 1
CkJsonObject_UpdateString $json "values[i][j]" "$100"

CkJsonObject_put_I $json 2
CkJsonObject_put_J $json 0
CkJsonObject_UpdateString $json "values[i][j]" "Totals"
CkJsonObject_put_J $json 1
CkJsonObject_UpdateString $json "values[i][j]" "$335.50"

CkJsonObject_put_EmitCompact $json 0
puts [CkJsonObject_emit $json]

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

CkHttp_SetUrlVar $http "spreadsheetId" "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA"
CkHttp_SetUrlVar $http "range" "Sheet1!A1:B5"
# resp is a CkHttpResponse
set resp [CkHttp_PostJson $http "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED" [CkJsonObject_emit $json]]
if {[CkHttp_get_LastMethodSuccess $http] != 1} then {
    puts [CkHttp_lastErrorText $http]
    delete_CkJsonObject $jsonToken
    delete_CkHttp $http
    delete_CkJsonObject $json
    exit
}

puts "response status code = [CkHttpResponse_get_StatusCode $resp]"
puts "response JSON = [CkHttpResponse_bodyStr $resp]"

delete_CkHttpResponse $resp

# 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:
# 

delete_CkJsonObject $jsonToken
delete_CkHttp $http
delete_CkJsonObject $json