AutoIt

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
; }