Simple row update via API/Powershell

DarylVogan

Just getting started with the API. Just want to show a proof of concept of updating a cell. I'm able to authenticate and get sheet, row and column details. I'm getting the error:

Invoke-RestMethod : { "errorCode" : 1008, "message" : "Unable to parse request. The following error occurred: Unrecognized token 'value': was expecting ('true', 'false' or 'null')\n at [Source: REST input; line: 1, column: 7]","refId" : "1s91b4mmwsgg2"}

I saw a similar error here but can't get it to work. Sorry for the noob question.....

$uri="https://api.smartsheet.com/2.0/sheets/8497703870064516/rows"

$body = @{

 'columnId ' = '229799331424132'

 'value'  = 'true'

}

 Invoke-RestMethod -Method put -Uri $uri -Headers $headers -body $body

DarylVogan

Thanks. Fresh eyes and coffee helped out. Got it working. For those that may run into this:

$uri="https://api.smartsheet.com/2.0/sheets/aaaaaaaaaaaaaa/rows"

$body = '[{"id": "rowid", "cells": [{"columnId": yyyyyy,"value": "zzzzzz"}]}]'

Genevieve P.

Hi @DarylVogan

I'm not familiar with Powershell syntax but based on the error received it sounds like the formatting may not be correct. Here's the Powershell Documentation.

I would recommend posting in the Powershell Community to see if other users familiar with Powershell can help out.

Here are some other community threads that may be helpful as well:

• Stack Overflow: Unexpected token errors in Powershell
• Stack Overflow: Powershell Invoke-RestMethod Smartsheet API add attachment to sheet
• What is the proper syntax for using "Invoke-WebRequest" command with api.smartsheet.com?
• SmartSheet Powershell Module proposal
• (See: SmartSheet_Powershell_Module in GitHub)

Cheers,

Genevieve

DarylVogan

Thanks. Fresh eyes and coffee helped out. Got it working. For those that may run into this:

$uri="https://api.smartsheet.com/2.0/sheets/aaaaaaaaaaaaaa/rows"

$body = '[{"id": "rowid", "cells": [{"columnId": yyyyyy,"value": "zzzzzz"}]}]'

Genevieve P.

Hi @DarylVogan

Thank you for posting your solution! I really appreciate it 🙂

Drakkon

Should anyone else stumble upon this I thought I would post a more nuts to bolts script for PowerShell

<# start #>

$SheetID = "your_sheet_id" <#change this#>

$Authtoken = "Bearer your_authtoken_here"<#Change this#>

<# DONOT CHANGE BELOW THIS #>

$headers = @{

  'Authorization' = $Authtoken

  'Content-Type' = 'application/json'

  }

$uri = "https://api.smartsheet.com/2.0/sheets/$sheetID/rows"

$body_start = '[{"id": '

$body_2 = ', "cells": ['

$body_last = ']}]'

$cel_start = '{"columnId": '

$cell_2 = ',"value": "'

$cell_end = '"}'

$cell_divider =', '

<# DONOT CHANGE ABOVE THIS #>

<#change the variables below#>

$row_id = 'you row id'

$column_id = 'column id 1'

$column2_id = 'column id 2'

$value = 'your value here'

$value2 = 'your other value'

<# end editable variables #>

<# I am using string concatenation to build the body #>

$body = $body_start+$row_id+$body_2+$cel_start+$column_id+$cell_2+$value+$cell_end+$cell_divider+$cel_start+$column2_id+$cell_2+$value2+$cell_end+$body_last<# UP DATE 2 CELLS you can do more than 2 !#>

$body = $body_start+$row_id+$body_2+$cel_start+$column_id+$cell_2+$value+$cell_end+$body_last<# UPDATE ONE CELL #>

 <# execute the invoke note the var to capture the response #>

$ssresp = Invoke-RestMethod -Method put -Uri $uri -Headers $headers -body $body

<# the end enjoy! #>