When you share a Google Sheet with others, they will have access to all individual sheets in the Spreadsheet. At present, there is no direct option to share a single sheet in Google Sheets. However, using the following method, you can share data in a single sheet or selected few sheets without giving access to the entire Spreadsheet.
In a previous post, I explained, How to import data from one Google Sheet to another. We can use the same method to share a single sheet in Google Sheets without giving access to other sheets.
Table of Contents
Steps to share a single sheet in Google Sheets
At present, there is no direct option to share a single sheet. As such, we are going to create a new Google Sheet and link it to the primary Google Sheet. (I used the name “primary Google Sheet”, to identify the Google Sheet with all your data). And also, we restrict access to the primary Google Sheet.
Then we are going to share the new Google Sheet. That way, you can share only the required sheets without giving access to the entire Google Sheet.
I will explain this in a few steps.
Step 01: Restrict access to your Primary Google Sheet
In this method, we need to include the URL of the primary Google Sheet in the new Google Sheet. Therefore, we need to restrict access to the primary Google Sheets. Visitors shouldn’t be able to access the primary Google sheet without the owner’s permission.
- Click the Share button at the top right corner of the primary Google Sheet
- Click the ⚙️ Gear icon (Settings)
- Uncheck both the checkboxes.
- Click ← (Back arrow)
- Under the “Get link” section click Change
- Click “Anyone with the link ▼” and change it to Restricted.
- Click Done.
The following video demonstrates the above steps.
Step 02: Create a new Google Sheet and link it to the Primary Google Sheet using the IMPORTRANGE function
The IMPORTRANGE function is used to import data from one Google Sheet to another.
You can learn more about the IMPORTRANGE function from this post.
Create a new Google Sheet. This is the Google Sheet you are going to share.
Let’s say you have a sheet named “World Population”, and you have data in the range A1:BI218 of your primary Google Sheet. You can import this range to your new Google Sheet using any one of the following formulae.
Method 1 (Using Spreadsheet URL)
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aL8h-x10bhyKPtdPTkJpMTw3a56qRVih9t8VIu9d0aE","'World Population'!A1:BI218")
Method 2 (Using Spreadsheet ID)
=IMPORTRANGE("1aL8h-x10bhyKPtdPTkJpMTw3a56qRVih9t8VIu9d0aE","'World Population'!A1:BI218")In method 2 above, I have used Spreadsheet ID instead of the URL. I prefer method 2 because it is nice and clean.
Note: Once you add the function, the cell will show you a #REF! error. Hover over the mouse on that cell and it will pop up the “Allow access” button. Click it, and you can see the data.
The following video demonstrates how to use the IMPORTRANGE function. Watch the video on full screen to see the details clearly.
? This method does not copy the existing formatting to the new Google Sheet. So you need to apply to formatting again.
Step 03: Share the new Google Sheet, instead of the primary Google Sheet
Now you can share this new Google Sheet with others. When you are sharing the Google Sheet, it is not recommended to grant edit permission. If they grant edit access, someone can guess other sheet names and manage to access those sheets also.
Since this Google Sheet includes the URL to your primary Google Sheet, it is important to follow Step 01. This prevents others from accessing the primary Google Sheet by copying and pasting the URL included in the IMPORTRANGE formula in the browser.
Warning!
- Since you already have granted the new Google Sheet to access the primary Google Sheet, do not grant edit permission to others. If you do so, they can guess the names and range of the other sheets and edit the function to see the data.
Even though there is no direct option, by following the above three steps you can share a single sheet in Google Sheets with others. This method is not limited to a single sheet, you can also share multiple sheets using this method.
You can access the two Google Sheets used in the above example from the following links.
Primary Google Sheet / Shared Google Sheet
Note: I have given “View” access to both Google Sheets above; you will not be able to access them otherwise.
You can also use some of the other methods mentioned below to share a part of your Google Sheets without giving access to the entire Spreadsheet.
Other methods for sharing only selected sheets in Google Sheets without giving access to the entire spreadsheet
In the above, I discussed how to share a single sheet in Google Sheets without giving access to other sheets in the primary Google Sheet. In that method, you are sharing the data in another Google Sheet. Alternatively, you can also use Google Apps Script or Google Data Studio to share your data without giving access to Google Sheet.
Use Google Apps Script
Using Google Apps Script, you can build web apps for free.
For example, you can use Google Apps Script HTML service to build a web page. Then you can display your data on that web page using Google Apps Script. This is also entirely free, and you do not need to share your Google Sheets.
You may read the following blog posts on Google Apps Script.
- How to create a web form to get data from Google Sheets
- How to Create an Online Data Entry Form that can Perform CRUD Operations on Google Sheets
Use Google Data Studio
Data Studio is a free tool offered by Google, which lets you create a dashboard by connecting Google’s products and many other external sources.
You can link your Google Sheet to Google Data Studio and display your data using many types of visual elements. This is also a very secure and attractive method to share your Google Sheets data without giving access to the entire Google Sheet.
You may read the following blog posts on Google Data Studio
- Create Stunning Free Online Dashboard for your Data
- How to link Google Sheets to Google Maps and create interactive maps
Protect other Sheets and Share
In this method, others can see all your sheets. However, you can protect the other sheets that should not be altered by other users. Then users can only edit the sheet where they haven’t protected.
To protect the entire sheet,
- Go to Tools and select Protect sheet. It will open up Protect sheets & ranges sidebar.
- Make sure you have selected the Sheet tab in the Protect sheets & ranges sidebar.
- Select the sheet you want to protect from the dropdown list.
- Click Set permissions
Wrapping Up
At present, there is no direct option to share a single sheet in Google Sheets. However, there are some other methods that you can obtain the same result. In the first part of the post, I explained how to use the IMPORTRANGE function to import data to a new Google Sheet. And explained to you how to share the new Google Sheets without giving access to the primary Google Sheet, which contains all your data.
You can also use Google Apps Script and Google Data Studio to share only the required data of your Google Sheets without giving access to the entire Spreadsheet.
