Update Reports using the API

Tobias Krause

I found an old thread in the community about this, so mayn users are looking for it a long time already.

I need to be able to update reports using the API, most importantly I want to add or change the source sheets of a report using the API.

In my current case I have multiple reports using the same sources and if a new source is added I need to add it manually to all my reports. If the API could do that, this would be awesome.

Paul Newcome

Based on the API documentation, it looks like you should be able to.

https://smartsheet.redoc.ly/tag/reportsObjects

Tobias Krause

https://community.smartsheet.com/discussion/comment/385248#Comment_385248

Hi,

thanks for the comment. But as far as I understand this , I can only get a list of the source sheets, but not update it using the API. Correct me if I am wrong.

Paul Newcome

You may be right. Sorry about that. A lot of the things I have seen with the API, if you can get it, you can edit it (not all but quite a bit). Are we not able to edit the "sourceSheets" or "source"? What about the Scope Object piece?

https://smartsheet.redoc.ly/tag/reportsObjects#section/Scope-Object

BKing

This would be really useful.

If you use a template to standardise projects your users are left with lots of 'My Project Report' rather than 'Prj1 Report' and 'Prj2 Report.

They end up not knowing which one is for which project.

If you get the folder object back you can do something like:

          foreach (Sheet tracker in folder.Sheets)

          {

            if (tracker.Id != null)

            {

              string newsheetName = namePrefix + "-" + tracker.Name;

              updOk = await UpdateName(Convert.ToInt64(tracker.Id.ToString()), newsheetName);

              if (updOk)

                okCount++;

            }

          }

Nice and easy.

You can loop over the reports, however there is no Report.Update method. You probably only need the ability to change the name.

Thanks,

Brian

Paul Newcome

@BKing You can create a "master" report that pulls in from all project sheets and have that filtered by user. Then you wouldn't need to worry about a bunch of individual "My Project Reports". Or when you create a new project form the template, you can rename the report to include the project name.

BKing

Thanks Paul,

I will revisit the full Smartsheet template feature. We've not used it.

For the moment 'our' template is just a folder with some sheets and reports and we use api copyfolder on new project create.

The purpose of my contribution was to support Thomas' change request. The api around Reports needs to be extended to allow Update and Save As.

Paul Newcome

@BKing I didn't realize you were using the API to copy your template. I agree that the API capabilities surrounding reports could be expanded. I was just trying to help find you a solution that could work.

I still feel that in the case of a "My Tasks" type of report, you could create just one report that pulls from all projects. Are you creating new workspaces for each project/template copy? If all template copies are going into the same workspace then you can create a report that references the entire workspace which will automatically include new sheets as they are added. You can include a filter in the report to only pull from sheets that have something like "Project Plan" in the sheet name and then filter by current user.

BKing

@Paul Newcome thanks for the extra help here. We are going to try various ideas, including yours.

samueljlawson

It's hard to believe after more than two years of users constantly asking for it that Smartsheet still hasn't completed the Reports part of the API.

You should at least be able to update the name of a report like you can a dashboard.

mrmcbee

I've created a workspace to manage my team's agile process. I've created sheets for projects, and reports for a scrum board, planning, backlog maintenance, measuring velocity/capacity, reports for individual team members to see only their tasks, and dashboards to keep my finger on the pulse of work in any given sprint.

The one chink in this system is the fact that I have to update each of these reports manually as each new sprint begins. I have about 10, which doesn't seem like that much work but I inevitably forget one and I don't notice until something falls through the cracks in my sprint.

I would love the ability to update my report filter programmatically so that my reports are always current. Alternatively, being able to set my filter value to a value stored somewhere in SmarthSheet would at least allow me to have only one spot to change.

Samuel Mueller

@samueljlawson you are able to update the name of a report via API

I don't think there is documentation, but you can call a PUT request with the body {"name":"New Report Name"}

Paul Newcome

@Samuel Mueller It doesn't look like it. Here is what is listed in the documentation:

Samuel Mueller

@Paul Newcome It's not documented but it works. I just tried it. I didn't expect it to work, but it did lol

Samuel Mueller

My PowerShell code, try it out!

$headers = @{}
$headers.Add("Authorization", "Bearer xxx")
$headers.Add("Content-Type", "application/json")
$reportID = xxxx
$response = Invoke-RestMethod ('https://api.smartsheet.com/2.0/reports/' + $reportID) -Method 'PUT' -Headers $headers -Body '{"name": "New Report Name"}'

BKing

@Samuel Mueller - that's amazing. Nice one.

As I've been on this thread here an update of what we did in the end:

Smartsheet drives race events to an ecommerce payment solution.

Our API produces lots of 'projects' like this from a Folder that is our template. In the end we renamed the Dashboard. (And all the sheets).

We introduced a Linker sheet that contains one row for each report and a report for this Linker that is placed on the dashboard.

After Copy (Save As New) on the folder, we run round publishing each report and save that url in Linker row for the relevant report.

Dashboard works without repointing any Widgets.

Race organisers can take the dashboard onto the mountain, with the mobile app, for: Start List, Emergency Contact and can add late entries and deal with last minute withdrawals.

Hope this is of use,

Brian