Tcl Requires Chilkat v11.0.0+

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