How do i access private google sheets?

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.

Though the sharing process is quite seamless, there is a powerful functionality behind it that is capable of supporting a great number of various scenarios. Needless to say, there are multiple ways to execute them. 

For example, sharing a tab in Google Sheets is not the same as sharing the whole spreadsheet, and 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.

The first step is to go to your Google Drive and find the spreadsheet you want to share. Then right-click on it. Alternatively, you can open the document first and then click the “Share” button located in the top-right corner.

Once the “Share with people and groups” pop-up opens:

1. Add emails of people or groups that you want to share the document with.
2. Choose the access rights that you give to the account holder [a group]. By default, Google suggests assigning the Editor permission.
3. 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.
4. At this point, the pop-up window will close.

Google Sheets sharing permissions: how a spreadsheet is shared and managed

Google supports three types of user permissions: Viewer, Commenter, and Editor. 

Where:

• Viewer can only view your document [can’t share or edit it]. 
• Commenter can view and leave comments [can’t share or edit it].
• Editor can view, share, and edit the document.

Once you’ve shared your document, you may need to get a shared link to it. To find it, you may either locate your file in Google Drive, right-click on it and then select “Get link”.

Or, you may open the document, navigate to the top-right corner and click “Share”.

After the “Share with people and groups” pop-up opens, please:

1. Navigate to the bottom part of the pop-up.

1. There you will see whether this document is already shared with a Google group — for example, an organization or department. In my case, this file has been shared with the entire Railsware Google group, so that its members have “Viewer” permissions for it.
2. To change this default setting, click “Change”.
3. In the drop down, pick either “Restricted”, “Your Group / Organization” or “Anyone with the link”.

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 in the “Share with people and groups” pop-up.
• “Google organization / group” — sharing a document with your company or department specifying the access rights. If you pick this option, you can also navigate and click on the settings gear located in the top-right corner of the “Get link” part of the pop-up, and tick the box if you want to let people in your organization be able to search for your spreadsheet.
• “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 to being very attentive when adjusting the sharing settings in this].

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 by name”], will land on the first sheet [tab] of your Google Sheets spreadsheet.

To share multiple Google Sheets files, hold Shift on your keyboard and pick several files. Then right click on the selected area to call the sharing pop-up window. After that you can follow the steps described under “How to share a Google Sheets document [spreadsheet]” section of this article.

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 with people and groups”. 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], make the person an owner of the document, or Remove them from the list.

In the “Get link” part of the pop-up window, you can also update group’s permissions, either by removing it and assigning the “Restricted” permissions, switching to the “Anyone with the link”, or keeping it and changing Viewer to Editor or Commenter rights.

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.

//docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId

Here is an example of a link to a Google spreadsheet:

//docs.google.com/spreadsheets/d/1q_Ch8q9ZesuSmHSmiBR-51w7n7wsri_hDXgAJLIipBI/edit#gid=992289955

Where SpreadsheetID = 1q_Ch8q9ZesuSmHSmiBR-51w7n7wsri_hDXgAJLIipBI

sheetID = 992289955

Below I’ll take a closer look at the two different use cases that people frequently ask about on forums. Both of them relate to a user’s need to share only one tab in Google Sheets. However, each of them has a unique condition that needs to be considered as well. So, below you’ll find possible solutions for both.

For example, I have a spreadsheet that consists of three tabs [Sheet 1, Sheet 2, and Sheet 3]. What I want is to share only one tab in Google Sheets [Sheet 3] with my colleague.

To share a Google sheet, follow these steps:

• Protect the sheets you don’t want to share with another person.
  • To protect the sheet, find the tab you want to protect, then right-click on it and select “Protect sheet”.
  • Choose “Set permissions”, then “Restrict who can edit this range” by leaving only those people who require access to the sheet. Then click “Done”.
• Hide these sheets.
  • To hide the sheet, find the tab you want to hide, then right-click on it and select “Hide sheet”.

Note: If you only hide the sheet, the user with Editor permissions will be able to unhide, edit and share it.

• Then give them access to the spreadsheet by providing a shareable link.

As a result, the user will be only able to edit “Sheet 3” while the others will be protected and hidden from them. If they try to open it, they will get the following error message.

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:

1. Create +2 separate spreadsheets.
2. Pull data from Sheet 1 of the master document to a spreadsheet A.
3. Fetch data from Sheet 2 of the master document to a spreadsheet B.
4. In the master spreadsheet, protect and hide Sheet 1 and Sheet 2, leaving Sheet 3 with shared access.
5. Share the three documents with the corresponding users.

Ways to pull data from one Google sheet to another sheet or spreadsheet

A side case is when you need to not share a Google sheet, but import data from it. In this case, it is important not only to pull data from one sheet to another but also to have this data synchronized across both documents. 

To do it, you can use:

• IMPORTRANGE formula 
• Google Sheets integration by Coupler.io

Google Sheets integration allows you to import data on a set schedule [for instance: every hour, on weekdays, from 9:00 am to 6:00 pm].

To set up this synchronization, you need to sign up to Coupler.io, click Add importer, and select Google Sheets as both the source and destination apps. Then execute the following steps:

• Source 
  • Connect your Google account and select a file on your Google Drive to transfer data from. Opt for one or several sheets to export data.

• Destination
  • Connect to your Google account and choose a file on your Google Drive to transfer data to. Type a name to create a new sheet or pick an existing one.

Voila! You can run the import right away if you click Save and Run or you can automate data import on a schedule using the Automatic data refresh.

There is an option to use Coupler.io as a Google Sheets add-on to have faster access to the tool in your spreadsheet. For this install it from the Google Workspace Marketplace and customize it as we described above.

Coupler.io allows you to combine multiple sources with three destinations – Excel, Google Sheets, and BigQuery. So, for example, you can easily export Google Drive to Excel.

You can also use Coupler.io to merge data from two or more different sheets into one. Basically, you perform a similar setup of the product, adding one more step to specify the sheets to join. 

Note: 

• If you join two or more sheets with the same data structure [the same number of columns and the same names], then your imported information will be merged vertically [see the gif below].
• If you join two or more sheets with different data structures [columns have different names and their number is also not equal], then your imported information will be merged horizontally.

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.

If you don’t want to share documents with visitors, then I would recommend following these instructions when sharing files with non-Google account users:

1. Click the “Share” button in the top-right corner of your document.
2. Click on the “Get link” section and pick “Anyone with the link”.
3. Select the Google Sheets sharing permissions:: Viewer, Commenter, or Editor.
4. Copy the link and share it with the non-Google account user.

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.

Back to Blog