Smartsheet limitations when collaborating with multiple teams

Pragmatist

I've been using Smartsheet for a few years now, but recently we have been rolling it out for more serious applications involving collaboration between multiple teams, including external teams and we seem to have come accross a fundamental limitation of Smartsheets, that being the permissions model.  For a platform whose reason to exist is collaboration this has surprised us.

We are using smartsheet to collect data on subcontractor activities, which relate to cases so that we can produce bills to our client.

We have multiple subcontractors, and ideally we would like to have them enter their cases into a single sheet (via a form), and then allow them to review their activities (and attach paperwork) before submitting for approval.

Reports seem to be designed for this use case, so we can produce a report which shares only those columns and rows that we want to share, with each specific contractor.  They each have their own filtered report - bingo - except that to view any data they need to have access to the underlying sheet which means they can see everyboidy's cases. Worse, to change their own records they need to be an editor, which means that they can edit entries from other contractors as well. We clearly can't have that.

Insisting that report users have access to the underlying sheets is the problem here and I'm sure was designed to fix a problem, but in doing so has made Smartsheets unsuitable as a tool to collaborate with external teams.  The two solutions I have read up on are the a "security by obscurity" method, where we try to hide the original worksheet and hope no-one goes looking for it.  We ruled that one out pretty quickly.  That leaves b), partitioning the data by setting up an identical sheet for each contractor.  This method means we have multiple sheets to maintain - every change has to be done 5 times, and also makes the collection formulas ridiculously and unecessarily complex.

A seemingly unrelated issue, but actually one that goes to the core of collaborative working, is that all editors are equal in Smartsheet.  We want to restrict what external teams can edit - like the approval status on their activities for example, whilst allowing our internal teams to change this.  The only way to do this is have our internal people assigned as admins, which means they can accidentally break forumulas.

We love Smartsheet and have a huge amount of time invested in it, but it seems that as long as we were developing low-grade applications to solve small-scale problems in individual departments it was fine.  As soon as we have stareted developing more serious business applications we appear to have outgrown it very quickly.

Any plans to overhaul the permissions structure Smartsheet? or do we need to go and find something else?

L_123

A couple of points:

1.

If you publish the Report they will be able to see all of the information in the report. There used to be a gear where you could toggle the information, but I can't seem to find that anymore. I know that isn't optimal, but it is a viable workaround.

 

2.

The easiest way to grant specific people permissions is to use update requests. I use a contact column and send them an update request to edit information, and change the viewable information inside of the update request. They never have access to the sheet, they don't see other peoples information, and you can restrict the available columns. Also you can automate it to send either based on a criteria, or a time basis. 

 

I'm not a smartsheet employee or a mod, so that is all I could come up with. They will be able to tell you more about the timetable for increasing the capabilities of the program.

Pragmatist

Thanks Luke for taking the time to reply.  I've just been playing with the published read-only report option but then realised that they may want to make changes to their rows after the initial submission.  Once they have reviewed they then check a box which starts the approval process - so unfortunately read-only access doesn't work.

I had also considered update requests and figured that this wouldn't work because of the volume of requests.  I wasn't aware that update requests could be timed or automated so will look at that but again its a highly compromised workaround to deal with a fundamental limitation.

The obvious solution to this seems to be reports. In fact reports are badly named. The fact that the reports allow modification of the underlying data means that they are not reports at all, but in fact views. And views are a feature of any database management system, designed to solve exactly the issue that I and many others are facing.  Unfortunately Smartsheet decided to make "views" aka Reports unworkable for many use cases by insisting on users having access to the source sheet.

So Smartsheet - quick fix to this issue that many people seem to be facing would be to allow the user to specify whether a report user needs access to the source sheets, or not.  In order not to mess up lots of existing solutions  this could default to yes.

 

 

 

J. Craig Williams

I'm not completely happy with this solution but it works in some cases:

http://ronin-global.com/2018/02/22/smartsheet-secure-your-sheets-with-x-sheet-references/

Craig

Pragmatist

Thanks. I don't think that works for me but will review fully when I have time as I'll undoubtedly learn something.  For now I am having to keep my subcontractor "visit" data on seperate sheets for each subcontractor, and amend the formulas to pull data from multiple sheets into the "cases" sheet which sits above visits in my hierarchy.  This is time consuming, especially because of the way Smartsheet deals with X sheet references - i.e. I can't just go and replace the name of one sheet with another, I need to create all these new ranges from scratch.

I believe that Smartsheet have actually confused "views" and "reports" and produced something that might be called "viewports".  After all, proper reports don't allow you to change the source data, that's a view, and views don't require a viewer to have access to every column and every row in the source sheet, that would be pointless.

FYI I submitted this feature request:

I have found Smartsheets to be a powerful platform for collaboration until I started using it accross multiple teams. With multi-team solutions it is necessary to control the data that each team can see, by displaying specific columns, and rows that meet certain criteria. In a typical database application this is achieved using VIEWS. As far as I can see Smartsheet incorporates this feature already but it is called Reports instead. Unfortunately it has been crippled it by mandating that report users need permisions for the source sheet. My request is that SS has options for both Reports (which require access to underlying data and do not provide edit rights) and Views (which do not require access to the source sheet but can allow edit rights on visible rows and columns).

 

Pragmatist

Actually Craig, I think that your article describes the solution I'm having to implement.

This will work, but its cumbersome enough to make me wish I'd picked a different platform than Smartsheet, and I won't be doing it again.

J. Craig Williams

I tend to agree with your assessment of the Report misnomer. 

If the Report could be adequately accessed from the API, they would be much more functional.

Craig

J. Craig Williams

If you are interested in saving a few hours of grunt work, contact me offline.

Craig