Attempting PUT method on Reports "sourceSheets" field... Does anyone know what the API is expecting?
Hello!
I am attempting to use the PUT method on a Report to update the source sheets, but cannot figure out how it wants the source sheets to be formatted.. Like what information do I need to put for each sheet in my array of sourceSheets?
I am using Powershell.
Here is my code so far:
$token = <API TOKEN> $reportID = <ID of desired Report> $headers = @{} $headers.Add("Authorization", "Bearer $token") $headers.Add("Content-Type", "application/json") $mystring = '{"sourceSheets": <Not sure what goes here...>}' $response = Invoke-RestMethod ('https://api.smartsheet.com/2.0/reports/' + $reportID) -Method 'PUT' -Headers $headers -Body $mystring
I have tried the following two methods...
$folder = <Folder containing sheets that I want the report to show> $mystring = '{"sourceSheets": {' foreach ($s in $folder.sheets.GetEnumerator()) { if($mystring -ne '{"sourceSheets": {') { $mystring = $mystring + "," } $mystring = $mystring + $s } $mystring = $mystring + "}}"
$folder = <Folder containing sheets that I want the report to show> $mystring = '{"sourceSheets": {' foreach ($s in $sheetsInFolder.GetEnumerator()) { if($mystring -ne '{"sourceSheets": {') { $mystring = $mystring + "," } $sheet = Invoke-RestMethod ('https://api.smartsheet.com/2.0/sheets/' + $s.id) -Method 'GET' -Headers $headers $mystring = $mystring + "\@{id=" + $sheet.id + "; name=" + $sheet.name + "; accessLevel=" + $sheet.accessLevel + "; dependenciesEnabled=" + $sheet.dependenciesEnabled + "; cellImageUploadEnable=" + $sheet.cellImageUploadEnable + "; permalink=" + $sheet.permalink + "; columns={" foreach ($col in $sheet.columns) { if ($col.primary) { $mystring = $mystring + "\@{id=" + $col.id + "; version=" + $col.version + "; index=" + $col.index + "; title=" + $col.title + "; type=" + $col.type + "; primary=" + $col.primary + "; validation=" + $col.validation + "; width=" + $col.width + "}" } else { $mystring = $mystring + ",\@{id=" + $col.id + "; version=" + $col.version + "; index=" + $col.index + "; title=" + $col.title + "; type=" + $col.type + "; primary=" + "False" + "; validation=" + $col.validation + "; width=" + $col.width + "}" } } $mystring = $mystring + "}}" } $mystring = $mystring + "}}"
The latter of the above methods I had to add the "\" in front of the "@" in order to fix the formatting on here, but the original code I was using did NOT have them in front of the "@".
The thought behind using the latter method is that that data is what is contained in the sourceSheets field when you use the GET method on a report that is using sheets.
Here is an example of that GET method:
$token = <API TOKEN> $reportID = <ID of desired Report> $headers = @{} $headers.Add("Authorization", "Bearer $token") $headers.Add("Content-Type", "application/json") $response = Invoke-RestMethod ('https://api.smartsheet.com/2.0/reports/' + $reportID + '?include=sourceSheets&pageSize=1000000') -Method 'GET' -Headers $headers
Attempting this came from finding out that you can use the API to rename a Report from this other post: https://community.smartsheet.com/discussion/107615/update-reports-using-the-api
That post used the following method:
$token = <API TOKEN> $reportID = <ID of desired Report> $headers = @{} $headers.Add("Authorization", "Bearer $token") $headers.Add("Content-Type", "application/json") $response = Invoke-RestMethod ('https://api.smartsheet.com/2.0/reports/' + $reportID) -Method 'PUT' -Headers $headers -Body '{"name": "New Report Name"}'
Thanks.
Answers
-
Hey @ryanwilliams
There currently is no public endpoint to update a Report in this way; the documented methods are as follows:
I'm impressed that someone else was able to update a Report name, that's great. However that suggestion is a workaround posted in a Product Idea where it's being discussed that these types of updates are not currently supported. It would be helpful if that post received your Vote and comment so that the Product Team could hear about your specific use-case and request: Update Reports using the API
Thank you!
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 349 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives