![]() |
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
(DataFlex) 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.
Use ChilkatAx-win32.pkg Procedure Test Boolean iSuccess Variant vJson Token Handle hoJsonToken Handle hoHttp String sJsonResponse Variant vJson Handle hoJson String sUrlToUpdate String sXyz Variant vResp Handle hoResp String sTemp1 Integer iTemp1 Boolean bTemp1 Move False To iSuccess // 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.. Get Create (RefClass(cComChilkatJsonObject)) To hoJsonToken If (Not(IsComObjectCreated(hoJsonToken))) Begin Send CreateComObject of hoJsonToken End Get ComLoadFile Of hoJsonToken "qa_data/tokens/googleSheets.json" To iSuccess Get ComHasMember Of hoJsonToken "access_token" To bTemp1 If (bTemp1 = False) Begin Showln "No access token found." Procedure_Return End Get Create (RefClass(cComChilkatHttp)) To hoHttp If (Not(IsComObjectCreated(hoHttp))) Begin Send CreateComObject of hoHttp End Get ComStringOf Of hoJsonToken "access_token" To sTemp1 Set ComAuthToken Of hoHttp To sTemp1 // First get the cells in the range A1:B5 Get ComSetUrlVar Of hoHttp "range" "Sheet1!A1:B5" To iSuccess Get ComSetUrlVar Of hoHttp "spreadsheetId" "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA" To iSuccess Get ComQuickGetStr Of hoHttp "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}" To sJsonResponse Get ComLastMethodSuccess Of hoHttp To bTemp1 If (bTemp1 = False) Begin Get ComLastErrorText Of hoHttp To sTemp1 Showln sTemp1 Procedure_Return End Showln sJsonResponse Get Create (RefClass(cComChilkatJsonObject)) To hoJson If (Not(IsComObjectCreated(hoJson))) Begin Send CreateComObject of hoJson End Set ComEmitCompact Of hoJson To False Get ComLoad Of hoJson sJsonResponse To iSuccess // 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 Set ComI Of hoJson To 3 Set ComJ Of hoJson To 1 Get ComUpdateString Of hoJson "values[i][j]" "$120" To iSuccess Set ComI Of hoJson To 4 Get ComUpdateString Of hoJson "values[i][j]" "$155.50" To iSuccess // Show the updated JSON. Get ComEmit Of hoJson To sTemp1 Showln sTemp1 // Update the Google Sheet using a PUT request. Set ComEmitCompact Of hoJson To True Move "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}?valueInputOption=USER_ENTERED" To sUrlToUpdate Get ComQuickGetStr Of hoHttp sUrlToUpdate To sXyz Get Create (RefClass(cComChilkatHttpResponse)) To hoResp If (Not(IsComObjectCreated(hoResp))) Begin Send CreateComObject of hoResp End Get pvComObject of hoJson to vJson Get pvComObject of hoResp to vResp Get ComHttpJson Of hoHttp "PUT" sUrlToUpdate vJson "application/json" vResp To iSuccess If (iSuccess = False) Begin Get ComLastErrorText Of hoHttp To sTemp1 Showln sTemp1 Procedure_Return End // Examine the response.. Get ComStatusCode Of hoResp To iTemp1 Showln "response status code = " iTemp1 Showln "response body:" Get ComBodyStr Of hoResp To sTemp1 Showln sTemp1 // A sample response body: // { // "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA", // "updatedRange": "Sheet1!A1:B5", // "updatedRows": 5, // "updatedColumns": 2, // "updatedCells": 10 // } End_Procedure |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.