Xbase++

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

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

LOCAL nSuccess
LOCAL oJsonToken
LOCAL oReq
LOCAL oHttp
LOCAL oResp
LOCAL oJson
LOCAL i
LOCAL nCount_i
LOCAL j
LOCAL nCount_j
LOCAL k
LOCAL nCount_k
LOCAL cSpreadsheetId
LOCAL cRange
LOCAL cMajorDimension
LOCAL cStrVal

nSuccess := 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..
oJsonToken := CreateObject("Chilkat.JsonObject")
nSuccess := oJsonToken:LoadFile("qa_data/tokens/googleSheets.json")
IF (oJsonToken:HasMember("access_token") == 0)
    ? "No access token found."
    oJsonToken:destroy()
    RETURN
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"
oReq := CreateObject("Chilkat.HttpRequest")
oReq:Path := "/v4/spreadsheets/spreadsheetId/values:batchGet"
oReq:HttpVerb := "GET"
//  Add each range to fetch.
oReq:AddParam("ranges", "Sheet1!A1:A2")
oReq:AddParam("ranges", "Sheet1!B1:B2")

oHttp := CreateObject("Chilkat.Http")
oHttp:AuthToken := oJsonToken:StringOf("access_token")

//  443 is the SSL/TLS port for HTTPS.
oResp := CreateObject("Chilkat.HttpResponse")
nSuccess := oHttp:HttpSReq("sheets.googleapis.com", 443, 1, oReq, oResp)
IF (nSuccess == 0)
    ? oHttp:LastErrorText
    oJsonToken:destroy()
    oReq:destroy()
    oHttp:destroy()
    oResp:destroy()
    RETURN
ENDIF

? oResp:BodyStr

oJson := CreateObject("Chilkat.JsonObject")
oJson:Load(oResp: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"
//          ]
//        ]
//      }
//    ]
//  }

cSpreadsheetId := oJson:StringOf("spreadsheetId")
i := 0
nCount_i := oJson:SizeOfArray("valueRanges")
DO WHILE i < nCount_i
    oJson:I := i
    cRange := oJson:StringOf("valueRanges[i].range")
    cMajorDimension := oJson:StringOf("valueRanges[i].majorDimension")
    j := 0
    nCount_j := oJson:SizeOfArray("valueRanges[i].values")
    DO WHILE j < nCount_j
        oJson:J := j
        k := 0
        nCount_k := oJson:SizeOfArray("valueRanges[i].values[j]")
        DO WHILE k < nCount_k
            oJson:K := k
            cStrVal := oJson:StringOf("valueRanges[i].values[j][k]")
            k := k + 1
        ENDDO
        j := j + 1
    ENDDO
    i := i + 1
ENDDO

oJsonToken:destroy()
oReq:destroy()
oHttp:destroy()
oResp:destroy()
oJson:destroy()