Sharing report without providing access to underlying Smartsheet

sahilhq

Hi,

Is there a possibility to share just the report without providing access to the underlying sheet to key stakeholders? I know that this is possible using the dynamic view add-on but does anyone have any sort of work arounds to be able to do this in some way or the other without using the add-on?

Also, if I share the same report with editor cannot share rights and provide only view rights to the undelying sheet, will the stakeholder still be able to make changes to the report?

Paul Newcome

It is a bit of work setting up, but I do have an idea. Here's the overview...

Make sure sheet has a column that replicates the row number.

Set up a form on a separate sheet where a user can enter row number and data for the columns.

Publish the report so that it can be viewed and put it on a dashboard using a Web Content widget.

Put the form on the same dashboard using another Web Content widget.

Users fill out the form, then cross sheet references pull the data from the form sheet to the project sheet based on the row number being the unique identifier.

Does all of that make sense?

Paul Newcome

You can publish the report and share that. That would keep you from having to share the underlying sheets.

sahilhq

@Paul Newcome Will they be able to update the report after publishing it? I gave it a shot and I could only view the report as read only....

Paul Newcome

That's correct. I didn't realize the users would need to edit the report. Would Update Requests work for you?

sahilhq

No, update requests might not be the best efficient. But thanks for your suggestions!!

Paul Newcome

What is the purpose of the report? Is it to simply filter by user, or...?

sahilhq

@Paul Newcome So basically I have set up this Smartsheet that has around 10 columns. However, I would only like my client to update 2-3 of those columns without seeing any of the other columns I have in my sheet. So I thought if I create a report, I could filter by those 2-3 columns and share with the client to edit. I guess I will not able to do thatanyomore. Do you have any suggestions other than an update request so that I can make my client update only those specific columns?

Paul Newcome

It is a bit of work setting up, but I do have an idea. Here's the overview...

Make sure sheet has a column that replicates the row number.

Set up a form on a separate sheet where a user can enter row number and data for the columns.

Publish the report so that it can be viewed and put it on a dashboard using a Web Content widget.

Put the form on the same dashboard using another Web Content widget.

Users fill out the form, then cross sheet references pull the data from the form sheet to the project sheet based on the row number being the unique identifier.

Does all of that make sense?

sahilhq

Paul, that's pretty impressive I must say. Yes, all of it made sense to me and I set up a test folder just now that contains sheets, report and dashboard and I tried to create this set up as per your advise. See below output of my test dashboard. The user can now use the form in the dashboard to populate the report and using cross sheet references the master sheet that the user will not have access to will also be updated which is great. The main disadvantage that I see in this solution is that when a user wants to update say multiple rows at once (e.g. rows 1-13 in one go) he will need to submit the form update one at a time (a row by row update) which is not really efficient. We can expand the form to provide row by row information but say if my master sheet has 1000 rows then having a form so long won't be very user friendly I feel.

I appreciate your suggestion man! It's always fun to create workarounds and alternative solutions to test Smartsheet capabilities especially when you are on a basic plan with them and don't have access to some of those nice add ons and API's.

Paul Newcome

For something like this, I have the form reset after each submission, and I remove the option to "Send me a copy of my responses". This makes it much easier to just tab through each of the fields. I also see that you have the Row Number AND the Description for users to fill out. If the description is already in the sheet, then the row number and status would be all that is needed. The less fields you have, the faster it can be filled out.

And while it may seem a bit of a hassle to submit the form 15 times to update 15 rows, it isn't much different than being able to only fill out one cell at a time while in the sheet for 15 different rows.

sahilhq

Hmm that's a good point. Thanks mate!

Paul Newcome

Happy to help. 👍️

Khaled Galal

Hi, very nice tip by the way, but is there a way for a collaborative user to request for update specific rows from a report?

Paul Newcome

@Khaled Galal Yes. It is outlined above. You would need to create a form, use cross sheet references to pull the most recent form entry's count/email address into the source sheet and set up an automated update request to send whenever the count changes to the email address in the column.

Publishing both to a dashboard would enable users to fill out the form to request the update form be sent to them then see the updated report all in one place.

Caitlin Smith

This seems to be a serious design gap, that really hinders data security and usability. Reports are how one exposes data to user. This is why it takes admin privileges to change the column and filters it is how one limits what the use can view/edit.

I know one can use workflows and forms to gather data from end users but this has several issues:

1. It depends on email. When I need to tell someone that can not just update the table they can see but they need to find the email with the form in their inbox. Usability craters.
2. The form is a one time use, so they user has no option to resubmit and correct on error. So I have to send the form again.
3. It is inconsistent, the end user can edit they data though the form, but not in the report.

The ability to use reports as collaboration tools is one of Smartsheets biggest strength, but since I am using Sheet Summary Reports to allow a user to update one field in the sheet summary, I must give him access to all the underlying sheets.