Google, with its sharing-friendly Google Drive, created a working environment to encourage collaboration across teams. It became super-easy to share spreadsheets, inviting colleagues to contribute at any time. The sharing process is quite seamless but there are multiple ways to execute it.
For example, sharing a tab in Google Sheets is not the same as sharing the whole spreadsheet. Sharing a document with a Google account holder can differ from sharing it with a person who doesn’t have one.
In this blog post, I’ll cover all possible options and approaches of how you can share a Google sheet and a spreadsheet, giving precise instructions on how to do it.
So, you have a spreadsheet and you want to invite your colleagues or stakeholders to check it, leave some comments, or even make edits. Go to the top right corner of the spreadsheet and click Share.
In the pop-up, enter the email address of the users you want to share the spreadsheet with. If you are a part of a Google Workspace or you have their emails among your contacts, you can just type the names. Google will offer the matching options.
Then change the user role if needed (Editor is set by default) and click Send. The user will get a notification to their email about access to the shared spreadsheet.
Now your spreadsheet is shared. You’ll see the icon of the Share button changed from a lock to a human silhouette.
This is how you can quickly share a spreadsheet with a person. However, Google offers other options that may be useful for your needs. So, check out this guide that provides other insights into sharing Google Sheets.
The most common case is to share data with an individual. These could be both Google and non-Google users.
At the same time, you can share spreadsheets with multiple users at a time. These are called Google groups. It’s a good option for businesses to make information accessible to specific teams and contributors.
Another available option is to set domain-level access to Google Sheets files. So, the members of your Google Workspace domain (former G Suite) will be able to view, comment, or edit data in the shared spreadsheets.
By default, a user with whom you shared the link to a spreadsheet by either giving it to them or sending them an email notification (if you picked Notify people), will land on the first sheet (tab) of your Google Sheets spreadsheet.
Google Sheets permission roles
There are three types of Google Sheets sharing permissions: Viewer, Commenter, and Editor:
- Viewer can only view your document (can’t share or edit it).
- Commenter can view and leave comments in the spreadsheet (can’t share or edit it).
- Editor can view, share, and edit the document.
The first step is to go to your Google Drive and find the spreadsheet you want to share. Then right-click on it => Select Share then click Share.
Note: Alternatively you can just select a spreadsheet and use a shortcut to Share: Ctrl+Alt+A
Once the Share pop-up opens:
- Add emails of people or groups that you want to share the document with.
- Choose the access rights that you give to the account holder (a group). By default, Google suggests assigning the Editor permission.
- Under the list of users who you want to share the document with, you will see the ticked by default box Notify people. You can either keep it and send a notification email to them, or clear the checkbox and then press the Share button (it will emerge instead of the Send button).
To get a shareable link to your spreadsheet, you can either locate your file in Google Drive, right-click on it, select Share => Copy link.
Alternatively, open the document, click Share in the top-right corner, and click Copy link in the pop-up window.
You’ve probably noticed the General access section on the screenshot above. I am a part of a Railsware Google group and hence I can share spreadsheets with the members of this group. For this, I need to change the Restricted option to the group.
Then I need to choose the access permission for the entire group, for example, Commenter.
Also, you have the option to make this spreadsheet searchable if you select Can find in search results. In my case, I don’t need it, so I’ll leave the default Must have link to access.
Eventually, the document will be shared with the entire Railsware Google group, so that its members have ‘Commenter’ permissions for it.
What other access options are available?
Google allows you to pick between the following options:
- Restricted — when access to the document is restricted to those emails and groups that you’ve specified.
- Google organization/group — sharing a document with your company or department specifying the access rights and searchability of the document.
- Anyone with the link — is when anyone with the link can view, comment, or edit the document without signing in to a Google account. By default, the latter has the Viewer access rights enabled (but I would recommend being very attentive when adjusting the sharing settings in this).
To share multiple Google Sheets files, select them using Ctrl (for the files that are located not consecutively) or Shift on your keyboard. Then right-click on the selected area, select Share => click Share. You will even see a small popup note indicating the number of files you want to share.
The rest of the flow you already know:
- Add users
- Choose their access role
- Decide whether you want to notify them
When sharing multiple spreadsheets as above, you can get their shareable links all at once without the need to copy them separately.
Just click the link button and the links to all the shared files (in our case, there are 4 links) will be copied to the clipboard.
Specifics of sharing multiple spreadsheets with a group
If you want to share multiple documents with a Google group, you have more interesting options.
First, you can share separate files with separate groups. This is useful if you are a member of different Google groups.
The same thing is with roles. It’s not necessary to choose one access role for all spreadsheets. In our example of 4 files, we can have one Viewer, one Commenter, and two Editor roles for instance.
As a result, you’ll see that your access will have mixed audiences and mixed roles.
How to amend sharing settings for a person or group for a Google Sheets spreadsheet
To change the sharing settings for a person, navigate to the Share pop-up window. Find the person for whom you want to make the changes. Then adjust their settings by selecting one of the options (Viewer, Commenter, or Editor). Additionally, you can:
- Add expiration of the access – you can specify the expiration date for the access.
- Transfer ownership – you can make the person an owner of the document.
- Remove access – you can remove access for the individual.
To change the sharing setting for a Google group, perform the steps described above. The only difference with managing sharing settings for a person is that you won’t be able to make the group an owner of the document.
Let’s say you need to share just one sheet with a user, not the entire spreadsheet. There is no dedicated functionality to do this in Google Sheets. However, there are a couple of workarounds that will help you do the job.
Hide sheets for users with view/comment access
The simplest way to let users only access the limited information in the spreadsheet is to hide the sheets you don’t want to share.
However, this only works for users with view access, including commenters. They won’t be able to see the hidden sheets.
It won’t work for users with editor role permission.
Protect sheets from editing
If it is okay for you to let users view your other sheets but you only want to avoid them to make edits, then protect the sheets as follows:
- Right-click on the tab you want to protect and select Protect sheet.
- Click Set permissions, then choose the option for Restrict who can edit this range. You can leave only those people who require access to the sheet or simply select Only you. Then click Done.
Then share the spreadsheet with the users you want. They will be able only to view the contents of the protected sheets, not edit them.
A better option is to create a separate spreadsheet that will only contain the sheets that you want to share. Then you synchronize these sheets using either Coupler.io or IMPORTRANGE function and share the entire spreadsheet. Check out the example:
I have a spreadsheet with three sheets: Sales_data, Sales_data2, and Sheet3. Of these three, only Sales_data must be shareable.
So, I’ve created a new spreadsheet and shared it with my colleague. Then I used Coupler.io to import data from Sales_data sheet to the new spreadsheet. It’s very simple:
Click Proceed in the form below:
1. Select a spreadsheet and a sheet to import data from
2. Preview and transform data. For example, you can hide or rename some columns, add new ones, filter data, and even blend it with data from other sheets or sources.
3. Select a spreadsheet and a sheet where you want to load data.
To keep your information in the destination sheet up-to-date, turn on automatic data refresh.
That’s it! Now, you can share this spreadsheet with your stakeholders and can be 100% sure that they won’t access other information.
Coupler.io is a reporting automation platform so it can do much more than synchronizing spreadsheets. It allows you to create insightful reports using data from marketing apps, sales tools, databases, and more. You can connect your data source to Google Sheets and automate data refresh on a desired schedule. Moreover, Coupler.io is available as an add-on for Google Sheets, so you can manage the connection right from your spreadsheet.
Can you do all this with IMPORTRANGE?
Yes, you can. IMPORTRANGE is a native Google Sheets function that lets you reference one sheet to another. However, it’s known for its errors and troubles that we’ve covered in a blog post Why is IMPORTRANGE Not Working.
So, if for some reason, your formula got broken, users won’t be able to see the data in the shared spreadsheet. Why? Because IMPORTRANGE only refers to the data not imports it. Coupler.io is free of this issue hence it won’t bring you down.
FAQs on sharing spreadsheets in Google Sheets
How to provide a link to a specific sheet (tab) in your Google Sheets workbook
Sometimes it is required to draw the user’s attention to a specific sheet in a shared spreadsheet, or make sure they land on a specific tab when opening the document (other than the first one that opens by default). To do it, navigate to the sheet you want a user to land on, then go to the address bar and copy the URL while staying on the sheet.
This URL will have the following syntax, containing the GID number that corresponds to a unique number of a sheet, or a SheetID.
https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetIdHere is an example of a link to a Google spreadsheet:
https://docs.google.com/spreadsheets/d/1q_Ch8q9ZesuSmHSmiBR-51w7n7wsri_hDXgAJLIipBI/edit#gid=992289955- SpreadsheetID = 1q_Ch8q9ZesuSmHSmiBR-51w7n7wsri_hDXgAJLIipBI
- sheetID = 992289955
What if I need to share separate sheets of my spreadsheet with three different users, allowing them to view only one tab that was specifically shared with them?
My suggestion would be:
- Create +2 separate spreadsheets.
- Pull data from Sheet 1 of the master document to spreadsheet A.
- Fetch data from Sheet 2 of the master document to spreadsheet B.
- In the master spreadsheet, protect and hide Sheet 1 and Sheet 2, leaving Sheet 3 with shared access.
- Share the three documents with the corresponding users.
Sharing Google Sheets with non-Google users is no different. You can simply enter the needed email address in the field and assign the access role permission.
According to Google’s help materials, it’s possible to switch on the “Visitor sharing” option for your organization. By doing so, visitors can view, comment or edit your document for 7 days (once verifying their email). To switch on this sharing option, please communicate with your Google account administrator.
Google Sheets is one of the best spreadsheet apps for collaboration, isn’t it?
I hope that you’ve managed to find the best way to share a Google sheet or spreadsheet. However, when doing so, be attentive, since you may either open a document publicly or to a Google group without any intention of doing so. Make sure you can also trust the person you share your file with. A good practice is to create a backup version of your master spreadsheet in order to protect your data from damage or loss.
Automate data import to Google Sheets with Coupler.io
Get started for free
