(CkPython) batchGet (Read Multiple Ranges)

Reads multiple ranges from a Google Sheets spreadsheet in one GET request.

Chilkat Python Downloads install with pip pip3 install chilkat or download... Python Module for Windows, MacOS, Linux, Alpine Linux, Solaris

import sys
import chilkat

#  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..
jsonToken = chilkat.CkJsonObject()
success = jsonToken.LoadFile("qa_data/tokens/googleSheets.json")
if (jsonToken.HasMember("access_token") == False):
    print("No access token found.")
    sys.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"
req = chilkat.CkHttpRequest()
req.put_Path("/v4/spreadsheets/spreadsheetId/values:batchGet")
req.put_HttpVerb("GET")
#  Add each range to fetch.
req.AddParam("ranges","Sheet1!A1:A2")
req.AddParam("ranges","Sheet1!B1:B2")

http = chilkat.CkHttp()
http.put_AuthToken(jsonToken.stringOf("access_token"))

#  443 is the SSL/TLS port for HTTPS.
# resp is a CkHttpResponse
resp = http.SynchronousRequest("sheets.googleapis.com",443,True,req)
if (http.get_LastMethodSuccess() != True):
    print(http.lastErrorText())
    sys.exit()

print(resp.bodyStr())

json = chilkat.CkJsonObject()
json.Load(resp.bodyStr())

#  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"
#          ]
#        ]
#      }
#    ]
#  }

spreadsheetId = json.stringOf("spreadsheetId")
i = 0
count_i = json.SizeOfArray("valueRanges")
while i < count_i :
    json.put_I(i)
    range = json.stringOf("valueRanges[i].range")
    majorDimension = json.stringOf("valueRanges[i].majorDimension")
    j = 0
    count_j = json.SizeOfArray("valueRanges[i].values")
    while j < count_j :
        json.put_J(j)
        k = 0
        count_k = json.SizeOfArray("valueRanges[i].values[j]")
        while k < count_k :
            json.put_K(k)
            strVal = json.stringOf("valueRanges[i].values[j][k]")
            k = k + 1

        j = j + 1

    i = i + 1