(PureBasic) batchGet (Read Multiple Ranges)

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

Chilkat PureBasic Module Download Chilkat PureBasic Module

IncludeFile "CkHttpResponse.pb"
IncludeFile "CkHttp.pb"
IncludeFile "CkJsonObject.pb"
IncludeFile "CkHttpRequest.pb"

Procedure ChilkatExample()

    ;  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.i = CkJsonObject::ckCreate()
    If jsonToken.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    success.i = CkJsonObject::ckLoadFile(jsonToken,"qa_data/tokens/googleSheets.json")
    If CkJsonObject::ckHasMember(jsonToken,"access_token") = 0
        Debug "No access token found."
        CkJsonObject::ckDispose(jsonToken)
        ProcedureReturn
    EndIf

    ;  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.i = CkHttpRequest::ckCreate()
    If req.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkHttpRequest::setCkPath(req, "/v4/spreadsheets/spreadsheetId/values:batchGet")
    CkHttpRequest::setCkHttpVerb(req, "GET")
    ;  Add each range to fetch.
    CkHttpRequest::ckAddParam(req,"ranges","Sheet1!A1:A2")
    CkHttpRequest::ckAddParam(req,"ranges","Sheet1!B1:B2")

    http.i = CkHttp::ckCreate()
    If http.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkHttp::setCkAuthToken(http, CkJsonObject::ckStringOf(jsonToken,"access_token"))

    ;  443 is the SSL/TLS port for HTTPS.
    resp.i = CkHttp::ckSynchronousRequest(http,"sheets.googleapis.com",443,1,req)
    If CkHttp::ckLastMethodSuccess(http) <> 1
        Debug CkHttp::ckLastErrorText(http)
        CkJsonObject::ckDispose(jsonToken)
        CkHttpRequest::ckDispose(req)
        CkHttp::ckDispose(http)
        ProcedureReturn
    EndIf

    Debug CkHttpResponse::ckBodyStr(resp)

    json.i = CkJsonObject::ckCreate()
    If json.i = 0
        Debug "Failed to create object."
        ProcedureReturn
    EndIf

    CkJsonObject::ckLoad(json,CkHttpResponse::ckBodyStr(resp))

    CkHttpResponse::ckDispose(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"
    ;          ]
    ;        ]
    ;      }
    ;    ]
    ;  }

    i.i
    count_i.i
    j.i
    count_j.i
    k.i
    count_k.i

    spreadsheetId.s = CkJsonObject::ckStringOf(json,"spreadsheetId")
    i = 0
    count_i = CkJsonObject::ckSizeOfArray(json,"valueRanges")
    While i < count_i
        CkJsonObject::setCkI(json, i)
        range.s = CkJsonObject::ckStringOf(json,"valueRanges[i].range")
        majorDimension.s = CkJsonObject::ckStringOf(json,"valueRanges[i].majorDimension")
        j = 0
        count_j = CkJsonObject::ckSizeOfArray(json,"valueRanges[i].values")
        While j < count_j
            CkJsonObject::setCkJ(json, j)
            k = 0
            count_k = CkJsonObject::ckSizeOfArray(json,"valueRanges[i].values[j]")
            While k < count_k
                CkJsonObject::setCkK(json, k)
                strVal.s = CkJsonObject::ckStringOf(json,"valueRanges[i].values[j][k]")
                k = k + 1
            Wend
            j = j + 1
        Wend
        i = i + 1
    Wend


    CkJsonObject::ckDispose(jsonToken)
    CkHttpRequest::ckDispose(req)
    CkHttp::ckDispose(http)
    CkJsonObject::ckDispose(json)


    ProcedureReturn
EndProcedure