Tcl

Google Sheets - Update (Set Values in a Range)

See more Google Sheets Examples

Sets values in a range of a spreadsheet. This example will demonstrate by first getting a range, then changing some values in the JSON, and then HTTPS PUT the changes back to the Google Sheet. Tcl

load ./chilkat.dll

set success 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..
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"]

# First get the cells in the range A1:B5
CkHttp_SetUrlVar $http "range" "Sheet1!A1:B5"
CkHttp_SetUrlVar $http "spreadsheetId" "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA"
set jsonResponse [CkHttp_quickGetStr $http "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}"]
if {[CkHttp_get_LastMethodSuccess $http] == 0} then {
    puts [CkHttp_lastErrorText $http]
    delete_CkJsonObject $jsonToken
    delete_CkHttp $http
    exit
}

puts "$jsonResponse"

set json [new_CkJsonObject]

CkJsonObject_put_EmitCompact $json 0
CkJsonObject_Load $json $jsonResponse

# A sample response is shown below.

# {
#   "range": "Sheet1!A1:B5",
#   "majorDimension": "ROWS",
#   "values": [
#     [
#       "Item",
#       "Cost"
#     ],
#     [
#       "Wheel",
#       "$20.50"
#     ],
#     [
#       "Door",
#       "$15"
#     ],
#     [
#       "Engine",
#       "$100"
#     ],
#     [
#       "Totals",
#       "$135.50"
#     ]
#   ]
# }

# We're going to change the cost of the Engine to $120, and the Totals to $155.50

CkJsonObject_put_I $json 3
CkJsonObject_put_J $json 1
CkJsonObject_UpdateString $json "values[i][j]" "$120"
CkJsonObject_put_I $json 4
CkJsonObject_UpdateString $json "values[i][j]" "$155.50"

# Show the updated JSON.
puts [CkJsonObject_emit $json]

# Update the Google Sheet using a PUT request.
CkJsonObject_put_EmitCompact $json 1
set urlToUpdate "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}?valueInputOption=USER_ENTERED"
set xyz [CkHttp_quickGetStr $http $urlToUpdate]
set resp [new_CkHttpResponse]

set success [CkHttp_HttpJson $http "PUT" $urlToUpdate $json "application/json" $resp]
if {$success == 0} then {
    puts [CkHttp_lastErrorText $http]
    delete_CkJsonObject $jsonToken
    delete_CkHttp $http
    delete_CkJsonObject $json
    delete_CkHttpResponse $resp
    exit
}

# Examine the response..
puts "response status code = [CkHttpResponse_get_StatusCode $resp]"
puts "response body:"
puts [CkHttpResponse_bodyStr $resp]

# A sample response body:

# {
#   "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
#   "updatedRange": "Sheet1!A1:B5",
#   "updatedRows": 5,
#   "updatedColumns": 2,
#   "updatedCells": 10
# }

delete_CkJsonObject $jsonToken
delete_CkHttp $http
delete_CkJsonObject $json
delete_CkHttpResponse $resp