Smartsheet: Secure Your Sheets with X-Sheet References
A Common Request
One of the common requests I see on the Smartsheet Community and hear from my customers is how can we make the data more secure?
“I want to share only certain data to my _____” (vendor/customer/peer/team member)
Repeat after me: Smartsheet is not a relational database. But it is getting closer to acting like one. Two weeks after writing a post about the new X-Sheet referencing (here), I’m still just scratching the surface, I think.
I thank the Norn Fates that I have customers and the Smartsheet Community users that make me want to get out of bed everyday and think.
Until the 2018-02-06 Smartsheet release, I would often need to go elsewhere (outside of Smartsheet) to solve the “share only certain data” question. Depending on requirements, sometimes careful design would allow Smartsheet usage alone, but more often AppSheet, Zapier, or another third party tool would be pulled in to round out the solution.
I’m closer* to a Smartsheet only solution.
Defining The Problem
The data currently resides in Smartsheet. It could be a list of projects with columns for customers and vendors, a project sheet with columns for selected teams and financial data that they don’t need to see, it could a team skill sheets with HR and management notes on growth and performance goals should not be shared to other members of team.
The data may currently reside on a single Sheet, where the problem becomes how to parse the data so eyes on see what they should see and nothing more. The data may currently reside across several Sheets, where the problem becomes how to consolidate and roll-up the data to give certain individuals or organizations are consolidated view of the data.
Unlike in a relational database, Smartsheet does not have Sheets keys that can be used join two rows from different Sheets into a single row that is used to describe and define the data therein. Smartsheet’s Reports only show a single row from a single Sheet, one row after another. Careful design of two related Sheets in Smartsheet can result in a Report that has row 1 and 2 pulling data about the same “thing” from the two Sheets. This works in very limited cases, primarily because the burden on getting those Reports to present the data in the way the user needs is moved from the tool back to the user.
From One to Many
I’ll explain the concepts using the starting point of a single sheet (the Main Sheet) that has rows and/or columns that we want to hide from certain users. The concept applies as well when wanted to combine multiple sheets into a unified whole.
Step 1: Look at the Sheet
Look at it. Really.
Look at the columns and the rows.
Does it need to split by rows, for example on an [Employee] column? Reports are already great for that, and that’s probably not what is causing concerns.
More likely, you want to ‘hide’ some of the columns from certain users.
Even more likely, ,you’ll want to do both.
Step 2: Define the Columns
If we just made a copy of the Main Sheet for each of the sheets by the category (or categories), what would those sheets need to be. I’ll call those sheets the Sub Sheets.
For a specific Sub Sheet, there are four types of columns to consider, columns that are:
- edited on the Sub Sheet (and we want the Main Sheet to reflect those changes)
- edited on the Main Sheet (and we want the changes to be reflected on the Sub Sheet)
- on the Main Sheet but not shown on the Sub Sheet
- edited on the Sub Sheet (but don’t need to the Main Sheet to see them)
Back on the Main Sheet, we make the same decisions, where we don’t really care about #4.
There needs to be one column that has the same data. In a database, that would be your key field. This will be the only duplicate manual entry on both the Main Sheet and the row’s associated Sub Sheet. If that column does not exist (for example [Job Number], [Project ID], [Activity Code]), then create it.
A short aside about Column Types
Since we are talking about columns, this needs to be said. It may make a bit more sense later, after the concept is fully formed, but I want to point out the gotchas along the way, so you don’t think I’m promising everything. I did say “closer to a Smartsheet only solution”.
Formulas are used to pass the data between Sheets.
Smartsheet does not currently allow formulas within Contact List type columns. If this column type is being passed over, it will need to land in a Text/Number column on the destination sheet. There is a some potential functionality loss (Alerts & Actions) here or small administrative burden to have the Contact List type column ‘recovered’ on the destination sheet.
On the destination sheet, you may be tempted to use Text/Number columns for the Dropdown List columns that are edited on a different sheet (and thus, updated by formula). Don’t. Keep it as a Dropdown List column and add the same options as on the source sheet. This will make your Reports more functional (or rather, not break them if you use Text/Number columns) and may provide some QA / documentation aid on the destination sheet. The first is much more important.
Step 3: Create First Sub Sheet (and then the rest)
Depending on how many columns will be on each sheet type (Main or Sub), I’ll make a copy of the Main Sheet as the first Sub Sheet.
The Main Sheets columns will likely remain the same or a few added to tighten up the functionality described here.
On the first Sub Sheet,
- the columns not needed at all are deleted,
- the Contact List type columns edited on the Main Sheet are changed to Text/Number
- new columns needed/desired are added
- and the first X-Sheet references from the Main Sheet are created
What we are going to end up with this:
and we’ll be using INDEX and MATCH to accomplish this. I won’t go into too much detail here, my goal is to explain the concept, not the nitty-gritty. Each destination column will have a formula referencing the unique matching column (the key field) and column in the source sheet.
Each column is updated, tested, and then we move on.
Back on the Main Sheet
- the references from the Sub Sheet are created
and now the two sheets should be ‘talking’ to each other.
The functionality is tested and approved then each additional Sub Sheet is created, modified, and tested.
For each new Sub Sheet, the X-Sheet references to the Main Sheet will need to be updated. A Save as New operation will keep the incoming links on each new Sub Sheet, but not the outgoing ones.
I use Filters on the Main Sheet to make this process faster.
Step 4: More Stuff
At this point, I have Sub Sheets and a Main Sheet that are talking to each other. Most the remaining I leave to the reader as the requirements will dictate further additions. I will, however, touch on a few
The Sub Sheets are shared to the appropriate people, and only the appropriate people. In most cases, I propose having those Sheets all together in a single Workspace, but to share the individual sheets individually. This will make Reports easier to build and maintain (both for the persons who can see them all and for the individual Reports)
There is a slight admin burden to duplicate the entry of the unique column. A complicated system to update the correct row on the “other sheets” could be built but I view the seconds or minute to update the data useful if I also use it for periodic QA of the sheets. It is not unlikely that something may cause the system to stop working, and periodically opening the sheet may prompt me to see it before it becomes an issue known to the end-user.
When adding a new Sub Sheet, there a limited set of actions to take. Write them down. Some or all of those could also be automated away, but I don’t automate just to automate. If I spend 1 hour per quarter creating a new sheet, I’m probably not going to spend 8 hours automating it down to 15 minutes. I need shorter ROI.
Once you create the columns (names and types), don’t rename them unless you have to. A general rule of Smartsheet is define the columns early and as completely as possible. You’ll save time later.
Here’s a diagram showing some of the “More Stuff”
I really could go on and on. I’m not sure how universal this will be. My first test had about 25 columns being passed back to the Sub Sheets and less than half-dozen coming back. I can see use cases where that is reversed.
I think this will work well for cases where a sheet of 90 columns has been split into sheets that are only passing 20 columns back and forth. The limit of 25000 incoming cells gives a top limit of 50 columns for sheets up to 500 rows. Smartsheet isn’t a database. But it is getting closer.
If you like this post, please “Like” it. Every time my post gets liked, a fish learns to swim. Or I’m more likely to post another one. It is one of those two, I’m sure.
If you are new to Smartsheet and want to check it out, click here. If you have questions about this post, add them below. If you have questions about something else in Smartsheet, post on the Community. I occasionally stop by to answer them and so do a lot of other talented people.