Attempting PUT method on Reports "sourceSheets" field... Does anyone know what the API is expecting?

ryanwilliams

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.

Genevieve P.

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