Simple row update via API/Powershell
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
Best Answer
-
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"}]}]'
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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"}]}]'
-
Hi @DarylVogan
Thank you for posting your solution! I really appreciate it 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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! #>
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives