Tcl

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

Reads multiple ranges from a Google Sheets spreadsheet in one GET request. 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
}

# We'll be sending a GET request with query params to this URL:  https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId/values:batchGet?ranges=Sheet1!A1:A2&ranges=Sheet1!B1:B2
# The domain is "sheets.googleapis.com"
# The path is "/v4/spreadsheets/spreadsheetId/values:batchGet"
set req [new_CkHttpRequest]

CkHttpRequest_put_Path $req "/v4/spreadsheets/spreadsheetId/values:batchGet"
CkHttpRequest_put_HttpVerb $req "GET"
# Add each range to fetch.
CkHttpRequest_AddParam $req "ranges" "Sheet1!A1:A2"
CkHttpRequest_AddParam $req "ranges" "Sheet1!B1:B2"

set http [new_CkHttp]

CkHttp_put_AuthToken $http [CkJsonObject_stringOf $jsonToken "access_token"]

# 443 is the SSL/TLS port for HTTPS.
set resp [new_CkHttpResponse]

set success [CkHttp_HttpSReq $http "sheets.googleapis.com" 443 1 $req $resp]
if {$success == 0} then {
    puts [CkHttp_lastErrorText $http]
    delete_CkJsonObject $jsonToken
    delete_CkHttpRequest $req
    delete_CkHttp $http
    delete_CkHttpResponse $resp
    exit
}

puts [CkHttpResponse_bodyStr $resp]

set json [new_CkJsonObject]

CkJsonObject_Load $json [CkHttpResponse_bodyStr $resp]

# A sample response is shown below.
# To generate the parsing source code for a JSON response, paste
# the JSON into this online tool: Generate JSON parsing code

# {
#   "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
#   "valueRanges": [
#     {
#       "range": "Sheet1!A1:A2",
#       "majorDimension": "ROWS",
#       "values": [
#         [
#           "Item"
#         ],
#         [
#           "Wheel"
#         ]
#       ]
#     },
#     {
#       "range": "Sheet1!B1:B2",
#       "majorDimension": "ROWS",
#       "values": [
#         [
#           "Cost"
#         ],
#         [
#           "$20.50"
#         ]
#       ]
#     }
#   ]
# }

set spreadsheetId [CkJsonObject_stringOf $json "spreadsheetId"]
set i 0
set count_i [CkJsonObject_SizeOfArray $json "valueRanges"]
while {$i < $count_i} {
    CkJsonObject_put_I $json $i
    set range [CkJsonObject_stringOf $json "valueRanges[i].range"]
    set majorDimension [CkJsonObject_stringOf $json "valueRanges[i].majorDimension"]
    set j 0
    set count_j [CkJsonObject_SizeOfArray $json "valueRanges[i].values"]
    while {$j < $count_j} {
        CkJsonObject_put_J $json $j
        set k 0
        set count_k [CkJsonObject_SizeOfArray $json "valueRanges[i].values[j]"]
        while {$k < $count_k} {
            CkJsonObject_put_K $json $k
            set strVal [CkJsonObject_stringOf $json "valueRanges[i].values[j][k]"]
            set k [expr $k + 1]
        }
        set j [expr $j + 1]
    }
    set i [expr $i + 1]
}

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