PowerBuilder

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

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

integer li_rc
integer li_Success
oleobject loo_JsonToken
oleobject loo_Req
oleobject loo_Http
oleobject loo_Resp
oleobject loo_Json
integer i
integer li_Count_i
integer j
integer li_Count_j
integer k
integer li_Count_k
string ls_SpreadsheetId
string ls_Range
string ls_MajorDimension
string ls_StrVal

li_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..
loo_JsonToken = create oleobject
li_rc = loo_JsonToken.ConnectToNewObject("Chilkat.JsonObject")
if li_rc < 0 then
    destroy loo_JsonToken
    MessageBox("Error","Connecting to COM object failed")
    return
end if
li_Success = loo_JsonToken.LoadFile("qa_data/tokens/googleSheets.json")
if loo_JsonToken.HasMember("access_token") = 0 then
    Write-Debug "No access token found."
    destroy loo_JsonToken
    return
end if

// 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"
loo_Req = create oleobject
li_rc = loo_Req.ConnectToNewObject("Chilkat.HttpRequest")

loo_Req.Path = "/v4/spreadsheets/spreadsheetId/values:batchGet"
loo_Req.HttpVerb = "GET"
// Add each range to fetch.
loo_Req.AddParam("ranges","Sheet1!A1:A2")
loo_Req.AddParam("ranges","Sheet1!B1:B2")

loo_Http = create oleobject
li_rc = loo_Http.ConnectToNewObject("Chilkat.Http")

loo_Http.AuthToken = loo_JsonToken.StringOf("access_token")

// 443 is the SSL/TLS port for HTTPS.
loo_Resp = create oleobject
li_rc = loo_Resp.ConnectToNewObject("Chilkat.HttpResponse")

li_Success = loo_Http.HttpSReq("sheets.googleapis.com",443,1,loo_Req,loo_Resp)
if li_Success = 0 then
    Write-Debug loo_Http.LastErrorText
    destroy loo_JsonToken
    destroy loo_Req
    destroy loo_Http
    destroy loo_Resp
    return
end if

Write-Debug loo_Resp.BodyStr

loo_Json = create oleobject
li_rc = loo_Json.ConnectToNewObject("Chilkat.JsonObject")

loo_Json.Load(loo_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"
//         ]
//       ]
//     }
//   ]
// }

ls_SpreadsheetId = loo_Json.StringOf("spreadsheetId")
i = 0
li_Count_i = loo_Json.SizeOfArray("valueRanges")
do while i < li_Count_i
    loo_Json.I = i
    ls_Range = loo_Json.StringOf("valueRanges[i].range")
    ls_MajorDimension = loo_Json.StringOf("valueRanges[i].majorDimension")
    j = 0
    li_Count_j = loo_Json.SizeOfArray("valueRanges[i].values")
    do while j < li_Count_j
        loo_Json.J = j
        k = 0
        li_Count_k = loo_Json.SizeOfArray("valueRanges[i].values[j]")
        do while k < li_Count_k
            loo_Json.K = k
            ls_StrVal = loo_Json.StringOf("valueRanges[i].values[j][k]")
            k = k + 1
        loop
        j = j + 1
    loop
    i = i + 1
loop


destroy loo_JsonToken
destroy loo_Req
destroy loo_Http
destroy loo_Resp
destroy loo_Json