How can I remove direct access to a Sheet, but still allow editing via Report?

Mike E

TL:DR

We are looking for a way to change/break the url to an existing Sheet (340+ columns) without breaking the connection to hundreds of Reports, thus preventing direct access to a Sheet people already have the secure link to, while still allowing view/edit access to the data via Reports.

We've considered some options surrounding making a copy of the Sheet and updating Report references, but there are downsides to them (see Full Story below).

How else might this be achieved with a bare minimum of impact to User experience?

Full Story

The structure of our Smartsheets was built prior to having a particularly solid understanding of exactly how access worked and without really understanding just how huge our primary data intake sheet would grow up to.

The problem we have is that people have links to the data intake sheet and tend to work directly out of it instead of using the Reports that we've built for them. Education is not preventing this from happening, and form time to time an entire row of data will get accidentally deleted from the primary data intake sheet.

Our goal is to take away direct access to the primary sheet, but still point Reports to it so they can view and modify the specific data they need to do their jobs while also mitigating (but clearly not eliminating) the impact of an 'entire row' deletion.

There are more than 300 Reports that exist and all point to this primary data Sheet.

Our problem would be easily solved if we move the primary Sheet to a Workspace that users don't have access to (easy to do) and provide edit access to just the sheet (not the workspace!) and then change the permanent link of the primary Sheet (impossible to do, I suspect?) so none of the bookmarks etc. that go directly to that Sheet would work anymore, but people could still make their changes via existing Reports.

• We've tried copying the primary Sheet to the obfuscated Workspace, with the intent to then go through each Reports being actively used and change Sheet references from the old Sheet to the copied one. Cumbersome, but manageable. There are 2 problems with this approach:
  • (1) Cell history does not also transfer
  • (2) More importantly, our 60 or so Automations fail to get copied (and error is provided when this occurs - I already submitted a ticket and am awaiting a response, though that doesn't solve the cell history issue).
• We considered setting access to the primary Sheet to View-only and making a copy of the Sheet which they would have edit (but not direct) access to, and then DataMesh data from the New Sheet to the Old Sheet. That would preserve some semblance of data history but the user making the change would always be the user that owns the DataMesh. It would also leave all the Workflows intact. The detriment here is that any time a column is added or changed, the DataMesh would also need to be changed. It would also be a rather huge DataMesh (340+ columns to setup, and also extra load on the Smartsheets system (unless perhaps we set it up to trigger Hourly instead of Immediately? Some Workflow triggers rely on up to the minute data)

Unfortunately the average user (actually, majority of users) have trouble understanding the difference between Sheets and Reports to begin with, let alone why they shouldn't work out of the primary Sheet, and have after months, proven they are unable/unwilling to stop working out of the Sheet after multiple and long standing requests, so a method of forcing this change to happen is required if we want to see it through.

How might this be achieved with the bare minimum impact on User experience?

Mike E

Turns out leaving everyone as an Editor and just hiding columns does a pretty good job of discouraging direct use of the primary data sheet. They still need to use it to set reminders, but otherwise, it seems to be doing the job!

Julio S.

Hi @Mike E,

The most straight forward way of achieving what you intend would be through Dynamic view, as this would allow giving specific users only access to the data that is relevant to them directly in the sheet without interacting through a Report. Note that Dynamic View is a premium app and you may need to purchase it if your account doesn't have it yet.

The problem with Reports is that users will always need to be shared to the underlying data sheet(s), otherwise they wouldn't be able to see or edit the information within (depending on their user type).

Although it may require some work, the most reasonable alternative to Dynamic View would be to create individual versions of the master sheet for each user (maybe creating a template and asking each user to save their own version) and then consolidate all data from all Project sheets into a single Report where only the program supervisors (not the updaters) have access to. If you'd need to preserve existing data in each of the sheets, you may selectively move/copy rows from the current master sheet to each new project sheets with the help of automation (Automatically move or copy rows between sheets)

This would be a new approach that wouldn't preserve the cell history but since each user should only have access to their own sheet, a new cell history / activity log will be created for each sheet allowing individual tracking of the data.

I hope that this can be of help.

Cheers!

Julio

Mike E

Thanks Julio - I appreciate your fresh ideas!

It sounds like Dynamic View may come fairly close. The downside is that it would require quite a lot of manually replacing of users' Reports and, worse, asking the company for an additional purchase 😅

I think individual versions of the primary Sheet would get tricky to manage because changes would have to merge across all of the Sheets, which is really exactly what the Reports are designed to facilitate and is not really functionally possible with cell links (we tried), and I think DataMesh would either error if we tried, or be inconsistent if it even allows meshing data in both directions between sheets (race condition?)

"The problem with Reports is that users will always need to be shared to the underlying data sheet(s), otherwise they wouldn't be able to see or edit the information within (depending on their user type)."

Exactly! I'm honestly surprised that Smartsheets doesn't have an option for editing data on a Sheet via Reports only. An 'Editor - via reports only' sharing status would be handy for cases like mine.

Thanks again!

Mike

Mike E

Turns out leaving everyone as an Editor and just hiding columns does a pretty good job of discouraging direct use of the primary data sheet. They still need to use it to set reminders, but otherwise, it seems to be doing the job!