Chilkat • HOME • Android™ • Classic ASP • C • C++ • C# • Mono C# • .NET Core C# • C# UWP/WinRT • DataFlex • Delphi ActiveX • Delphi DLL • Visual FoxPro • Java • Lianja • MFC • Objective-C • Perl • PHP ActiveX • PHP Extension • PowerBuilder • PowerShell • PureBasic • CkPython • Chilkat2-Python • Ruby • SQL Server • Swift 2 • Swift 3,4,5... • Tcl • Unicode C • Unicode C++ • Visual Basic 6.0 • VB.NET • VB.NET UWP/WinRT • VBScript • Xojo Plugin • Node.js • Excel • Go
(Excel) batchGet (Read Multiple Ranges)Reads multiple ranges from a Google Sheets spreadsheet in one GET request.
' 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.. Dim jsonToken As Chilkat.JsonObject Set jsonToken = Chilkat.NewJsonObject success = jsonToken.LoadFile("qa_data/tokens/googleSheets.json") If (jsonToken.HasMember("access_token") = False) Then Debug.Print "No access token found." Exit Sub 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" Dim req As Chilkat.HttpRequest Set req = Chilkat.NewHttpRequest req.Path = "/v4/spreadsheets/spreadsheetId/values:batchGet" req.HttpVerb = "GET" ' Add each range to fetch. req.AddParam "ranges","Sheet1!A1:A2" req.AddParam "ranges","Sheet1!B1:B2" Dim http As Chilkat.Http Set http = Chilkat.NewHttp http.AuthToken = jsonToken.StringOf("access_token") ' 443 is the SSL/TLS port for HTTPS. Set resp = http.SynchronousRequest("sheets.googleapis.com",443,True,req) If (http.LastMethodSuccess <> True) Then Debug.Print http.LastErrorText Exit Sub End If Debug.Print resp.BodyStr Dim json As Chilkat.JsonObject Set json = Chilkat.NewJsonObject success = json.Load(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" ' ] ' ] ' } ' ] ' } spreadsheetId = json.StringOf("spreadsheetId") i = 0 count_i = json.SizeOfArray("valueRanges") Do While i < count_i json.I = i range = json.StringOf("valueRanges[i].range") majorDimension = json.StringOf("valueRanges[i].majorDimension") j = 0 count_j = json.SizeOfArray("valueRanges[i].values") Do While j < count_j json.J = j k = 0 count_k = json.SizeOfArray("valueRanges[i].values[j]") Do While k < count_k json.K = k strVal = json.StringOf("valueRanges[i].values[j][k]") k = k + 1 Loop j = j + 1 Loop i = i + 1 Loop |
© 2000-2022 Chilkat Software, Inc. All Rights Reserved.