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?
