![]() |
Chilkat HOME Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi DLL Go Java Node.js Objective-C PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(Visual FoxPro) Google Sheets - Update (Set Values in a Range)Sets values in a range of a spreadsheet. This example will demonstrate by first getting a range, then changing some values in the JSON, and then HTTPS PUT the changes back to the Google Sheet. Note: This example requires Chilkat v11.0.0 or greater.
LOCAL lnSuccess LOCAL loJsonToken LOCAL loHttp LOCAL lcJsonResponse LOCAL loJson LOCAL lcUrlToUpdate LOCAL lcXyz LOCAL loResp lnSuccess = 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.. loJsonToken = CreateObject('Chilkat.JsonObject') lnSuccess = loJsonToken.LoadFile("qa_data/tokens/googleSheets.json") IF (loJsonToken.HasMember("access_token") = 0) THEN ? "No access token found." RELEASE loJsonToken CANCEL ENDIF loHttp = CreateObject('Chilkat.Http') loHttp.AuthToken = loJsonToken.StringOf("access_token") * First get the cells in the range A1:B5 loHttp.SetUrlVar("range","Sheet1!A1:B5") loHttp.SetUrlVar("spreadsheetId","1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA") lcJsonResponse = loHttp.QuickGetStr("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}") IF (loHttp.LastMethodSuccess = 0) THEN ? loHttp.LastErrorText RELEASE loJsonToken RELEASE loHttp CANCEL ENDIF ? lcJsonResponse loJson = CreateObject('Chilkat.JsonObject') loJson.EmitCompact = 0 loJson.Load(lcJsonResponse) * A sample response is shown below. * { * "range": "Sheet1!A1:B5", * "majorDimension": "ROWS", * "values": [ * [ * "Item", * "Cost" * ], * [ * "Wheel", * "$20.50" * ], * [ * "Door", * "$15" * ], * [ * "Engine", * "$100" * ], * [ * "Totals", * "$135.50" * ] * ] * } * We're going to change the cost of the Engine to $120, and the Totals to $155.50 loJson.I = 3 loJson.J = 1 loJson.UpdateString("values[i][j]","$120") loJson.I = 4 loJson.UpdateString("values[i][j]","$155.50") * Show the updated JSON. ? loJson.Emit() * Update the Google Sheet using a PUT request. loJson.EmitCompact = 1 lcUrlToUpdate = "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}?valueInputOption=USER_ENTERED" lcXyz = loHttp.QuickGetStr(lcUrlToUpdate) loResp = CreateObject('Chilkat.HttpResponse') lnSuccess = loHttp.HttpJson("PUT",lcUrlToUpdate,loJson,"application/json",loResp) IF (lnSuccess = 0) THEN ? loHttp.LastErrorText RELEASE loJsonToken RELEASE loHttp RELEASE loJson RELEASE loResp CANCEL ENDIF * Examine the response.. ? "response status code = " + STR(loResp.StatusCode) ? "response body:" ? loResp.BodyStr * A sample response body: * { * "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA", * "updatedRange": "Sheet1!A1:B5", * "updatedRows": 5, * "updatedColumns": 2, * "updatedCells": 10 * } RELEASE loJsonToken RELEASE loHttp RELEASE loJson RELEASE loResp |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.