Google Sheets is a free, web based spreadsheet application.
It is part of the Google Docs Editors suite (the other applications being Google Docs, Google Slides, Google Drawings, Google Forms, Google Sites, and Google Keep).
Being a web based application, one of its advantages is that it allows several users to collaborate on the same document at the same time.
While this adds tremendous power and flexibility, while at the same time opening up an array of possibilities, it also creates a problem when two users simultaneously edit or update the same piece of content, each possibly overwriting the other.
Perhaps, even more importantly, it also opens up the issue of security, especially with sensitive content.
Shares
When a Google Sheets document is created, the owner of the document can choose to share the document with colleagues and other users.
There are three levels of sharing offered:
- Editors, who can also be given rights to change permissions and share the document with other users
- Viewers
- Commenters
Only Editors can make changes to the contents of the spreadsheet, while Viewers and Commenters cannot.
Apart from viewing the spreadsheet, Viewers and Commenters can further be given the ability to download, print, and copy the document.
While this level of access may seem a minor concern, when the document contains sensitive information, placing such restrictions or permissions on it may be a necessity, helping safeguard (to some degree) against it being made available to those outside the collaborating team.
When it comes to contention occurring while editing content, the above restrictions really only limit users to either being able to make any changes at all (as in the case of Editors), or to simply viewing the document (Viewers and Commenters).
Shares therefore, only offer a very basic form of protection and are barely adequate under a collaborative effort.
They certainly do not solve the problem of contention when two or more Editors attempt to alter the same piece of content.
Locks
To solve these issues, Google Sheets offers greater access control through the use of locks, which allow restrictions to be placed on who can make changes and updates to specific content (referred to as a range of cells).
These locks still necessitate that users be given share access as Editors to the document, if they are to be able to make updates and changes.
There are a few reasons for locking content, including, to ensure that headings and titles, or cells with formulas, which do not need to be changed, are not changed (whether intentionally or not).
Another reason is to avoid conflict between Editors while edits are taking place, the lock being relinquished once the changes are completed.
This solves the problem of contention between two or more Editors who have been granted access to the same content, when working simultaneously on the same cell or cells and overwriting each other.
Still a third reason, is to compartmentalize the work allocated to various Editors, each being able to edit content only within the areas that have been assigned to them.
We will be taking a look at how to lock content and allocate permissions that apply to each of these cases.
Since spreadsheets are made up of cells arranged in rows and columns, it makes sense that locks are therefore applied to cells, rows, columns, and even individual sheets.
Unlike shares, this offers much greater granularity than simply offering (or revoking) editing rights for the whole document.
We’ll be taking a look at a typical spreadsheet document to which will be applied a simple lock.
Google Sheets refers to locks as Protected sheets and ranges, but in this article, both terms refer to the same functionality.
The very top row of our typical sheet contains the column headings (Date, Description, Category, Amount, etc.).
Headings are usually thought of as being permanent, and once set, don’t need to be changed.
A lock will therefore be applied to this row where only the owner of the spreadsheet (i.e. you) will be able to make changes.
When creating locks, the lock initially references whatever cell or range of cells is currently selected.
This can be a single cell, a range of cells, a single row or column, a range of rows or columns, or an entire sheet within the spreadsheet document.
To lock the entire top row, start by selecting the row.
From here, there are two ways to invoke the Protected sheets and ranges pane that will then allow us to set any restrictions and permissions.
Right mouse click anywhere within the selected top row, and from the dropdown menu that appears, choose Protect range.
The other way is to select the Data option from the application’s horizontal navigation menu, and then choose Protected sheets and ranges from the dropdown menu.
In either case, the Protected sheets and ranges pane will open to the right of the sheet.
A form will be available to create the new lock.
You can enter a name or description for the lock, which will help identify it more easily, especially handy if many locks will be created.
The next field below has two tabs, Range and Sheet, which determines if the lock will be applied to a range of cells or to one of the sheets contained in the spreadsheet. Locking sheets is discussed later in this article.
Initially, the Range tab is highlighted with the currently selected cell (or cells) reference address in the text entry field.
On the far right of the text entry field is a cell table icon, the purpose of which we’ll get to in a moment.
In this particular case, the cell reference address is Sheet1!1:1, which is the address for the entire first row.
If you are familiar with sheet cell addressing, you could manually enter the address range of the locked cells/rows/columns, otherwise stick to selecting the range of cells on the sheet with the mouse.
The cell table icon on the far right hand side of the reference address field is for changing the range of cells selected for locking.
Clicking on it, will allow you to select a new range of cells with the mouse, directly from the sheet.
The cell reference address will be updated to reflect the new selection. Pressing the Ok button confirms selection of the new range.
At the bottom of the lock creation form are two buttons, Cancel and Set permissions.
If you don’t want to continue with creating the lock, simply cancel.
Otherwise, clicking the Set permissions button will allow you to select who will have permission to edit the selected cells.
Clicking on the Set permissions button opens the Range editing permissions dialog box in the middle of the screen.
Selecting the radio button option, Show a warning when editing this range, won’t restrict anyone from making changes, but it will display a warning message (Heads up! dialog box) whenever anyone attempts to edit the selected range of cells.
Edits can still be performed on the cells, but as the Heads up dialog box shows, they need to be confirmed. This helps avoid accidental changes.
However, if changes are intentional, and a series of edits will be carried out on the range of cells, the Don’t show this again for 5 minutes checkbox can be selected so that the popup isn’t presented after every edit, at least for the next five minutes.
The second radio button in the Range editing permission dialog box, Restrict who can edit this range, allows you to select which Editors can make updates and changes.
There is an associated dropdown below, that is set by default to Only you, and this is fine if you don’t intend to allow anyone else to be able to edit the selected range of cells.
Clicking the Done button at this stage would create a lock for the top row of the sheet, where only you would be allowed to make any changes.
But in a collaborative effort, you will most likely want to add permissions to other ranges of cells for other editors.
Opening the Restrict who can edit this range dropdown, reveals two other options, Custom and Copy permissions from another range.
Copy permissions from another range will bring up a list of previously created, existing locks, from which to copy permission settings.
This makes it quick and easy to set permissions, especially when there is a large number of editors to select from, and multiple locks with the same permissions need to be created.
Selecting Custom from the Restrict who can edit this range dropdown, will produce a list of Editors that have been granted access to this document.
Select those that are to have editing rights for the selected cells.
There is also a textarea available, Add editors, where the names or email addresses of additional Editors can be added manually.
When all required Editors have been selected (and/or manually added), click the Done button to set the permissions.
If names or email addresses were manually added in the Add editors textarea, and if any don’t have share rights to the document, the dialog box Someone needs access to the file, is displayed requesting that Share access with Editing rights to the document be granted.
Note that any email addresses added manually that are not associated with a Google account, will produce the error message Some email addresses couldn’t be added.
There are two possible solutions for this.
One, is for the intended recipient to create a Google account.
The other is to select the radio button Turn link sharing on, and then anyone to whom you send the link will be able to access and edit the document.
At this stage, clicking on Share will save the permissions and create the lock.
The new lock entry will appear in the Protected sheets and ranges pane, along with any other locks that have been created.
Clicking on a lock entry will expand that entry and allow the lock to be modified.
You can change the name, delete the lock altogether (rubbish bin icon), change the permissions, or even change the range of cells.
While we’ve created a lock for an entire row, the procedure is exactly the same when creating a lock for a column or range of cells.
The procedure is also the same when locking sheets.
To create a lock on a sheet, with the Protected sheets and ranges pane open, click on the + Add a sheet or range link.
The same form opens up as the one when we created the lock for the top row.
To create a lock on a sheet, simply select the Sheet tab (below the Enter a description text entry field).
The Sheet dropdown will initially be set to the current sheet, but opening the dropdown allows the selection of any sheet from the current document.
Once the sheet has been selected, clicking on the Set permissions button will open up the usual Range editing permissions dialog box where permissions can be assigned.
Hiding Sheets
One final feature that is worth mentioning is the ability to hide sheets.
This does not exactly fall within the realm of cell security, as do shares and locks (or editing permissions), but it can come in handy when Viewers and Commenters are also collaborating on the same document.
Hiding sheets is only effective for shares with level Viewers and Commenters. Editors are able to unhide hidden sheets.
A spreadsheet document can contain several sheets.
Some of these sheets may have information where access is not required in order for a Viewer or Commenter to perform their job. They may also contain formulas or sensitive information, in which case it may be a requirement to hide these sheets.
Hiding a sheet is very simple. At the bottom of the spreadsheet document, either right mouse click the sheet to be hidden, or with the mouse select the sheet to be hidden, and click on the down arrow.
From the dropdown menu, select Hide sheet.
That’s it. Now, any Viewers and Commenters will not be able to view that sheet.
As was mentioned previously, all Editors can unhide hidden sheets.
There are a couple of ways to do this.
From the application’s horizontal navigation menu, select View, then Hidden sheets and the sheet to unhide.
The other way is to click on the hamburger menu at the bottom left of the document and select the hidden sheet.
While hidden sheets will be grayed out, selecting them will automatically unhide them.
Conclusion
Shares and locks are a very powerful feature of Google Sheets. In fact, similar features are available for an entire range of Google applications, including the Google Docs Editors suite, Google Forms, and Google Drive.
Apart from the security offered by limiting who has access, they also allow a team of users to work simultaneously on a document or documents, without conflict.
Shares allow delegating the level of access to the document into those that can only view, and those that can make changes, called Editors.
Locks, in the case of Google Sheets, go a step further and allow the placing of restrictions on ranges of cells.
