In moving to the cloud, spreadsheets became collaborative, solving many productivity and workflow problems. But collaboration on spreadsheets is all-or-nothing. What if you wanted to share a single row for your customer to view? Or a few cells for an employee to check and update if needed? The inability to limit which data is shared in a document is a big obstacle to many real-world needs.
Users of traditional spreadsheets come up with workarounds, but those are inefficient and insecure. For example, you might hide columns from view, but those columns are visible on export, and thus the data is not secure. Or you might make copies of the spreadsheet with certain fields deleted, but the copies quickly fall out of sync with the original, creating new headaches. With either approach, you need manual steps and great care. It is inefficient to say the least! These are workarounds, not solutions.
What spreadsheet users really need is the ability to control access to data in a granular way, to be able to restrict who can see what row, in what situation, and which cells.
We have an answer to this need, and it is Access Rules. They sound like a way to limit sharing, but they are (also) quite the opposite. Restrictions allow sharing that otherwise would not have happened at all.
For instance, would you share a spreadsheet with a customer that contained other customers’ data? That’s unthinkable. But if you had a way to let each customer see just their own data, it suddenly becomes shareable.
Access rules allow sharing that otherwise would not have happened at all.
I like to compare access rules to a traffic light system in a city. Just as traffic lights regulate the flow of vehicles to prevent collisions and ensure efficient movement, access rules regulate the flow of information within a team or organization to prevent confusion and ensure effective collaboration. Just like red lights stop vehicles and green lights allow them to proceed, access rules allow or restrict access to parts of your document, creating a clear and organized system that facilitates teamwork.
In Grist, access rules are a general-purpose feature, as fundamental as formulas, and with as many applications. It is impossible to discuss its benefits without some examples.
Let’s talk about viewing first.
- Example 1. Your company has personal data of employees. Some of it, such as department and role, should be visible to everyone. Other data, like salaries and SSNs should be limited to a few select people who need it.
- Example 2. You have staff responsible for different regions. You want them to only have access to the customers and leads in their region. But some managers can see all customers, and everyone can see summary dashboards for all regions.
- Example 3. You keep track of students signed up for classes. You’d like to let each teacher to see just the basic info of the students signed up for their class. Oh, and there is more: you want any student to be able to see a list of their classes.
Grist makes all of this possible. It is the only spreadsheet-like product that does it. Granular access control is difficult to add to an existing product. It can’t be bolted on. To be secure, it must be added at the foundational level. For instance, rules must apply to API requests as much as to the user-facing app.
Rules in Grist are configured using conditions, which are simple formulas. For instance, the condition for Example 1 would be simply the formula
, which is true only for those customers whose record’s “Region” field matches the region of the logged-in user. Those customers would be visible, while the rest would not be.
$Region == user.Sales.Region
This explainer video from our Access Rules playlist describes just such an example:
Granular control over editing access is no less powerful, and unlocks several other commonly-useful workflows.
- Example 4. Certain records are worked on by multiple people, and eventually finalized, not to be changed again. Once marked “locked”, no one can edit them, either by accident or intentionally.
- Example 5. At the end of the year, your HR department would like all employees to check and update their mailing address. Access rules can restrict employees to view only their own record, and edit only the address part of it.
- Example 6. You have a publicly shared dataset, and would like to let anyone contribute to it, i.e. to crowdsource data. You configure rules to allow the public (i.e. non-signed-in users) to create only records marked unverified, which are hidden from everyone else. Moderators may review, edit, and accept those, to make them part of the public dataset.
Edit rules are useful also for simple protections: to stop people from changing formulas, or to protect certain columns even from yourself making accidental changes. For another example, the need to prevent duplicate records comes up so often that we have a dedicated recipe for using access rules to achieve that too: https://support.getgrist.com/examples/2023-01-acl-memo/.
Many templates available in Grist include access rules, and unlock use cases that would be impossible without them. As of the writing of this article, these include:
Let me share another real-life story about access rules, a favorite for the simplicity of it. A science researcher had a list of experiments — with important info about each, and a photo of the result (a Petri dish). Another researcher, whom we’ll call the “counter”, would look through the photos and count up some visual markers in them. This is a bit subjective, and to remove room for bias on the part of the “counter”, the experiment info must be hidden from them. This is traditionally done by sharing a copy of a spreadsheet with random identifiers in place of experimental info, then mapping the data back using those identifiers. It’s a painstaking and error-prone workflow. Using Grist, the team could work in the same document: access rules simply blocked the experimental info from the “counter”. It was simple to set up, removed all friction, and solved the need perfectly.
Access rules in Grist are an advanced feature. The stakes are usually high: a wrong rule could be disastrous. Grist provides ways to test them, and to preview different situations. For example, with Grist’s “View As” feature, it is possible to view data while spoofing different users to test your rules. Much is possible, but it is important to read documentation, be careful, and do those tests.
To learn Access Rules, the main resources are:
- Main detailed documentation, at https://support.getgrist.com/access-rules/.
- Our Access Rules playlist on Youtube.
- The Recordings of our webinars on Youtube.
- The Community Forum is a great place to ask questions.
If you’d rather not invest your own time, you can always ask us. Reach out to us at https://www.getgrist.com/sprouts-program/, and get in touch with our solutions team, who can build and configure your documents for you.
So go ahead and add traffic lights to your city of data. They will allow you and your team to open up more of your city, and to go safely to places you couldn’t visit before.