Google Docs which had its initial release on March 9th 2006, almost 11 years ago, has now evolved into a great alternative for the Microsoft Office. The all-time advantage of Google Docs is the fact; it is a cloud based solution rather than the on-premise version of Office suite. Microsoft launched office 365, the latest offering from the Office series, support cloud capability but the pricing will burn a hole through your pocket. Taking a normal user into consideration, who wants to create light weight documents, quick presentations on the go and excel sheets to share with group members, Google Docs will come handy and above all, it is free!
Though every Google Spreadsheet file can be shared among anyone using sharable link that automatically gets generated or share among specific group of people by entering their email id, what if you want to share only specific sheet/single tab in Google spreadsheet? Today we are going to have a look at the solution this question. There is no out of the box product capability offered by Google for doing this but, it is achievable using excel functions. This workaround will come helpful when you don’t want to share sensitive information which should be visible only to you and not among other people.
Share Only Specific Sheet/Single Tab in Google Spreadsheet
For this solution to implement you may need to create new excel documents, this we will share with other people.
I am taking an example ‘Sales Commission’ document. This document has two sheets;
- My Commission – this will have information about my commission.
- XYZ – this will have information about the commission/sales made by my employee XYZ.
I want to share only the ‘XYZ’ sheet with my employee XYZ and not ‘My Commission’ sheet that has my sales information. By default, you can share the entire file with anyone but since I wanted to have my information protected from XYZ I need to do a workaround to protect my privacy.
Open the main ‘Sales Commission’ document; Click on the Share button on the top right corner.
Click on Advanced option on the bottom right corner.
This will open a window which has the ‘Link to share’ and below that, under the ‘who has access’ click on the Change… option.
Select Off – Specific people option and click Save.
If you don’t want the person whom you share the sheet with to share it further, then this step is important. We will be exposing the spreadsheet link to my employee XYZ and I don’t want him to share it with anyone since it contains sales information and commission details.
Once you click the Save button, you will be presented with the old screen which has Link to share text box. Copy that link and save it somewhere as this link will be used in later stage of this tutorial. If you don’t want to do this step now then, you can always come back to this screen and get it copied later.
To get the Sharable link; You can either click on the Share button on the top right corner or go to File Menu > Share > Advanced > Link to share.
Create a New Spreadsheet to Share and Import Data from Main Spreadsheet
Next step is to create new Google Spreadsheet; this spreadsheet file will be shared with XYZ. You don’t have to import any data from the main spreadsheet now. We will do this data import by typing in a spreadsheet command.
I have created a new Spreadsheet file and named it as ‘XYZ Commission’. First line I have written a message for my employee XYZ. Click on the second row first cell. Now type in the following command.
=IMPORTRANGE(spreadsheet_key, range_string)
Replace the spreadsheet key with sharable link of the main document you have copied earlier.
Replace range_string part with sheet name you want to import and the cell range.
The final command will look like;
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1ujOuAWJXvAe9O-Vw2Fxs0bjf0etk4hOFw/edit?usp=sharing”,”XYZ!A:E”)
Here the XYZ is the sheet name, put an exclamation mark after that ‘!’ and specify the columns you have to import. In my case it is A to E so, we will write this as A:E.
Hit enter key and within few seconds the data from the main spreadsheet (Sales Commission) sheet (XYZ) will get populated in the new spreadsheet document XYZ commission.
If you see #REF! value instead, then click on the cell and click on Allow Access option.
Setting up limited Access before sharing
The XYZ Commission document is all set to share with my employee XYZ. To share, click on the Share button on the top right corner. Click Advanced and type the person’s email id under the Invite People text box. To set up limited access; you can check the options under the Owner Settings heading.
The two access settings you can put in are;
- Prevent editors from changing access and adding new people
- Disable options to download, print, and copy for commenters and viewers
The best part about this workaround is the data is synchronized between the spreadsheets. That means, if you edit the data in your main sheet, the spreadsheet XYZ Commission that you have shared with your employee will also get updated. This is a great workaround you can do to share the individual sheets in a Google Spreadsheet. I hope the Google will release an update in near future as a solution to address this issue.