Permissions in Google Sheets: 'Viewers' can do even less than you might think

This article looks at the different user roles in Google Sheets and what each has permission to do. It goes over some surprising limitations with the 'Viewer' role, explores a potential workaround, and ends with a live example.

Google Sheets roles - official definitions

In one of my freelance roles at the moment, I have to share a lot of spreadsheet data with third parties using Google Sheets. I don't want these third parties to be able to change the data, so I give them the role of 'Viewer' (or sometimes 'Commenter') rather than 'Editor'. Google defines the roles like this:

Viewer: People can view, but can’t change or share the file with others.
Commenter: People can make comments and suggestions, but can’t change or share the file with others.
Editor: People can make changes, accept or reject suggestions, and share the file with others.

Surprising limitations

These definitions are factually correct, but for me they don't tell the whole story. The phrase "can't change" applies not only to the data in the Sheet, but also to how that data is presented. For example, Viewers (and Commenters) can't:

• Resize rows and columns. So if a cell is too small to show its entire contents, the Viewer won't be able to make it bigger. (They can, however, copy the data out of the cell if they want to see all of it.)
• Expand pivot tables. If a field in a pivot table has been collapsed, the Viewer will be able to see the '+' symbol to expand it - but clicking on this does nothing. In other words, they won't be able to access the data in the collapsed field. (Equally, they won't be able to collapse expanded pivot table fields in order to get a more concise view of the data.)

These limitations can be both confusing and frustrating for the Viewer. With this in mind, you should always resize columns and expand pivot tables in a way that makes all the data visible before giving the Viewer access to your spreadsheet.

Workaround: 'Make a Copy'

I also like to point out to the Viewer that they can take a copy of the shared Sheet (via File > Make a Copy in the Google Sheets menu). When working in the copy, they will be able to resize columns, expand pivot tables and perform any other operations they wish.

This is often the best option when you have hands-on clients that want to perform their own analysis. However in my case I also provide a written Google Docs report linking through to the data, and one drawback is that this will always link to the original spreadsheet rather than any copy the client makes.

What about sorting data?

So those are the limitations. On the other hand, and a little surprisingly, Viewers are able to sort data in a Sheet. They can do this either by going to Data > Sort in the Sheets menu when viewing the spreadsheet, or by clicking the little 'Down' arrow at the top of each column (it only appears when you hover over it).

In either case, this applies a 'temporary filter' view that is only visible to the Viewer and not to the spreadsheet creator or any other user:

While this is useful, it seems odd that Viewers can sort data but not resize columns.

Thoughts and live example

It feels to me that Viewers should be able to resize columns and expand pivot tables without affecting other users, in much the same way as the 'temporary filter' works when sorting.

Either that, or there should be an intermediate role between 'Commenter' and 'Editor' which lets the user change the presentation of the data but not the data itself. Maybe 'Analyst' or something similar (though an analyst should be able to clean the data, so that's not quite right either).

Incidentally, the limitations apply not just to individuals with the 'Viewer' role, but also when the Sheets doc is 'public on the web' and set to "Anyone on the internet with this link can view". This option can be enabled by going to File > Share:

To demonstrate this, I've created a small example spreadsheet which you can access here: https://docs.google.com/spreadsheets/d/1CTCu4pWU7OcTJyE3CMZ0C3-S2iwtwgWG8sOB4KuMFSg/edit?usp=sharing

You will see for yourself that, as a Viewer, you are not able to resize the columns in the Data tab or expand the pivot table to see the email addresses listed under each country. (Incidentally, the random data in this example was generated using the brilliant tool Mockaroo, which I thoroughly recommend).

Am I missing any other surprising limitations of being a Viewer in Google Sheets? If so, let me know!