AutoIt Requires Chilkat v11.0.0+

Google Sheets - Update (Set Values in a Range)

See more Google Sheets Examples

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. 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

$oHttp = ObjCreate("Chilkat.Http")
$oHttp.AuthToken = $oJsonToken.StringOf("access_token")

;  First get the cells in the range A1:B5
$oHttp.SetUrlVar("range","Sheet1!A1:B5")
$oHttp.SetUrlVar("spreadsheetId","1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA")
Local $sJsonResponse = $oHttp.QuickGetStr("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}")
If ($oHttp.LastMethodSuccess = False) Then
    ConsoleWrite($oHttp.LastErrorText & @CRLF)
    Exit
EndIf

ConsoleWrite($sJsonResponse & @CRLF)

$oJson = ObjCreate("Chilkat.JsonObject")
$oJson.EmitCompact = False
$oJson.Load($sJsonResponse)

;  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

$oJson.I = 3
$oJson.J = 1
$oJson.UpdateString("values[i][j]","$120")
$oJson.I = 4
$oJson.UpdateString("values[i][j]","$155.50")

;  Show the updated JSON.
ConsoleWrite($oJson.Emit() & @CRLF)

;  Update the Google Sheet using a PUT request.
$oJson.EmitCompact = True
Local $sUrlToUpdate = "https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}?valueInputOption=USER_ENTERED"
Local $sXyz = $oHttp.QuickGetStr($sUrlToUpdate)
$oResp = ObjCreate("Chilkat.HttpResponse")
$bSuccess = $oHttp.HttpJson("PUT",$sUrlToUpdate,$oJson,"application/json",$oResp)
If ($bSuccess = False) Then
    ConsoleWrite($oHttp.LastErrorText & @CRLF)
    Exit
EndIf

;  Examine the response..
ConsoleWrite("response status code = " & $oResp.StatusCode & @CRLF)
ConsoleWrite("response body:" & @CRLF)
ConsoleWrite($oResp.BodyStr & @CRLF)

;  A sample response body:

;  {
;    "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
;    "updatedRange": "Sheet1!A1:B5",
;    "updatedRows": 5,
;    "updatedColumns": 2,
;    "updatedCells": 10
;  }