PureBasic

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

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

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

Procedure ChilkatExample()

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

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

    success = CkHttp::ckHttpSReq(http,"sheets.googleapis.com",443,1,req,resp)
    If success = 0
        Debug CkHttp::ckLastErrorText(http)
        CkJsonObject::ckDispose(jsonToken)
        CkHttpRequest::ckDispose(req)
        CkHttp::ckDispose(http)
        CkHttpResponse::ckDispose(resp)
        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))

    ; 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)
    CkHttpResponse::ckDispose(resp)
    CkJsonObject::ckDispose(json)


    ProcedureReturn
EndProcedure