AutoIt

batchGet (Read Multiple Ranges)

See more Google Sheets Examples

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

Local $bSuccess = False

; 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 = ObjCreate("Chilkat.JsonObject")
$bSuccess = $oJsonToken.LoadFile("qa_data/tokens/googleSheets.json")
If ($oJsonToken.HasMember("access_token") = False) Then
    ConsoleWrite("No access token found." & @CRLF)
    Exit
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 = ObjCreate("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 = ObjCreate("Chilkat.Http")
$oHttp.AuthToken = $oJsonToken.StringOf("access_token")

; 443 is the SSL/TLS port for HTTPS.
$oResp = ObjCreate("Chilkat.HttpResponse")
$bSuccess = $oHttp.HttpSReq("sheets.googleapis.com",443,True,$oReq,$oResp)
If ($bSuccess = False) Then
    ConsoleWrite($oHttp.LastErrorText & @CRLF)
    Exit
EndIf

ConsoleWrite($oResp.BodyStr & @CRLF)

$oJson = ObjCreate("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"
;         ]
;       ]
;     }
;   ]
; }

Local $i
Local $iCount_i
Local $iJ
Local $iCount_j
Local $iK
Local $iCount_k

Local $spreadsheetId = $oJson.StringOf("spreadsheetId")
$i = 0
$iCount_i = $oJson.SizeOfArray("valueRanges")
While $i < $iCount_i
    $oJson.I = $i
Local $sRange = $oJson.StringOf("valueRanges[i].range")
Local $sMajorDimension = $oJson.StringOf("valueRanges[i].majorDimension")
    $iJ = 0
    $iCount_j = $oJson.SizeOfArray("valueRanges[i].values")
    While $iJ < $iCount_j
        $oJson.J = $iJ
        $iK = 0
        $iCount_k = $oJson.SizeOfArray("valueRanges[i].values[j]")
        While $iK < $iCount_k
            $oJson.K = $iK
Local $strVal = $oJson.StringOf("valueRanges[i].values[j][k]")
            $iK = $iK + 1
        Wend
        $iJ = $iJ + 1
    Wend
    $i = $i + 1
Wend