Moving on to another stop of our "Back to Basics" journey, today I will tell you more about managing your spreadsheets when collaborating with others. You will learn how to share, move and protect your data in Google Sheets.
As you may already know from my previous article, the main advantage of Google Sheets is the possibility for several people to work with the tables simultaneously. There's no need to email the files or to guess what changes were made by your colleagues anymore. All you need to do is share Google Sheets documents and start working.
How to share Google Sheets files
To grant access to your tables, press the Share button at the upper right corner of the Google Sheets and enter the names of those users who will work with the table. Decide whether to give each person the right to edit or comment on the table or only to view the data:
What's more, you can get an external link to your table and send it to your colleagues and partners. To do that, change the setting under the General access section from Restricted to Anyone with the link:
Every person with a link can view the spreadsheet by default. For them to be able to edit it, you should either share the spreadsheet with them directly (by adding their email address and personalizing the access) or change the role for everyone with the link:
Note. If you skip it, the users will have to request access if they want to comment or edit the file.
Further on, you will find some extra sharing options if you click the Settings icon at the upper-right corner of the sharing window:
Here you can limit some editor permissions as well as prohibit downloading, printing and copying the files for viewers & commenters:
Finally, when the spreadsheet is already shared, you can appoint a new owner to it. Just open the drop-down with the permissions next to the required account name and pick Transfer ownership.
How to move Google Spreadsheets
Saving the files has never been so easy. You don't need to do anything special to save the changes anymore. Google Sheets saves the data automatically with every change you make. Let's see how to organize your data in Google Drive.
To move your spreadsheet to any other folder, just find it in your Drive, right-click and choose Organize > Move:
Or open the spreadsheet and click the Move icon at its top:
Tip. Of course, you can also drag and drop spreadsheets in Drive or make use of the File > Move menu options in the opened file.
How to lock cells in Google Sheets
When many people access your Google Sheets, you may want to protect the data: certain cells or the entire tab.
"What for?", you may ask. Well, one of your teammates may happen to change or remove the data accidentally. And they may not even notice that.
Of course, you can always view the version or cell-edit history and undo the changes. But it will take some time to look through the whole list and, besides, it will cancel the rest of the "correct" changes. To avoid that, you can protect your Google Sheets data.
Protect the entire spreadsheet
Since you already know how to give access to your tables and what rights you can grant the users, the first very simple piece of advice would be to try and allow viewing the table instead of editing. Thus, you'll reduce the number of unintentional changes to the minimum.
How to lock a Google sheet
Right-click the tab of interest and choose Protect the sheet:
Make sure Sheet is selected in the opened pane:
Tip. The Enter a description field is not required, yet I'd recommend filling it in to remember what and why you decided to protect from the changes.
Tip. If you're wondering how to protect Google sheet except for certain cells, simply check the box with the exact same name: Except certain cells. You will need to enter these cells or ranges:
This way, you will still allow access to some cells on the locked sheet.
The next step would be to adjust the settings for the users. Press the Set permissions button and you'll see the Range editing permissions pop-up:
- If you select Show a warning when editing this range, everyone with access to the file will be able to edit this sheet as well. Once they try to change something, they will get a warning about editing the protected cells in Google Sheets and they will have to confirm the action. At the same time, you will get an email with the actions the users perform in the spreadsheet.
- If you select Restrict who can edit this range, you'll have to enter every single user who'll be able to edit the sheet.
As a result, you'll see a padlock on a Google sheet meaning it's protected:
To unlock a Google sheet, go to Data > Protect sheets and ranges in the menu:
Pick the protection you'd like to remove:
Then change its settings or remove the protection by clicking the trash bin icon:
Lock & unlock multiple Google sheets at once
In case you need to protect multiple Google sheets, doing it one by one can be frustrating. Luckily, there's an easy solution in the Power Tools add-on called Sheets Manager.
You'll see all your tabs listed with checkboxes. You just tick off those Google sheets you'd like to lock and click the Protect button:
Then select the way to protect these sheets:
- Restrict editing will make the sheet accessible only to you and the owner of the spreadsheet.
- Show warning will make the users confirm whether they want to make the changes.
In a similar manner, you can use Sheets Manager to unlock all protected Google sheets in one go. You also select them all, and this time pick Unprotect:
Watch this demo video and you'll see how simple the tool is and how easy it is to use. Tip. To follow the video along, install just Sheets Manager from the Google Workspace Marketplace or get it as part of Power Tools: a collection of 30+ tools for hundreds of daily operations in spreadsheets.
Video: How to protect/unprotect multiple Google sheets
Watch this demo video and you'll see how simple the tool is and how easy it is to use.
Tip. To follow the video along, install just Sheets Manager from the Google Workspace Marketplace or get it as part of Power Tools: a collection of 30+ tools for hundreds of daily operations in spreadsheets.
Protect cells in Google Sheets
To protect certain cells in Google Sheets, select the range, right-click on it and choose View more cell action > Protect range:
You'll see a familiar settings pane and be able to set the necessary permissions.
Tip. If in time you forget what is protected and who can access the data, you can easily recall it from the same Data > Protected sheets and ranges menu that you used to unlock Google sheets.
Now you know how to organize your files in Drive, share them with others and protect cells and sheets in Google Sheets without fear of losing or corrupting any important pieces of information.
Next time I'll dig deeper into some aspects of editing the tables and share some peculiar aspects of working in Google Sheets. See you then!