In this tutorial, you will learn how to share only one sheet in Google Sheets.
Despite all the collaborative features in Google Sheets, Google has yet to add a specific feature for sharing a single sheet. There is, however, a solid workaround that allows you to share a single sheet, although it does come with limitations
IMPORTRANGE() is a Google Sheets function that allows you to import a range from another spreadsheet. As such, it can be used to pull data from a restricted master spreadsheet to one that is shared with users who should not have access to the master, essentially sharing only a single sheet (or even a small portion of a sheet) with that user
To use IMPORTRANGE(), we’ll need the URL of the spreadsheet to import from. To get the URL, open the spreadsheet and copy the URL from the address bar in your browser
Open a new spreadsheet to import the range into and type the following formula: “=IMPORTRANGE(“[URL]”, “Sheet1!A:C”)” where [URL] is the URL you copied in Step 1, and Sheet1!A:C is the A1 notation reference to the range within that spreadsheet that you want to import
When you finish the formula, you’ll get an #REF! error because you haven’t yet given this new spreadsheet permission to access data from the original spreadsheet. Hover over the error and a blue Allow Access button will appear. Click on it to allow the spreadsheet to perform the import, without granting spreadsheet users access to it
The data from the spreadsheet range will import
If you would like to pull the formatting from the original spreadsheet, you’ll have to copy and paste it from the original spreadsheet. Select the range you’re importing in the master spreadsheet, and copy it,
In the new spreadsheet, select the cell containing the IMPORTRANGE() formula, then open the Edit menu, hover over the Paste Special option and then choose Paste Format Only
The formatting from the master file will appear
While IMPORTRANGE() works in many cases when you would want to share a single sheet, there are a few issues/limitations to be aware of:
- As noted in the instructions above, IMPORTRANGE() only imports data, not formatting. You will have to copy and paste the formatting from the master any time you want to change it in the shared sheet
- IMPORTRANGE() creates a read-only copy. Changes made to the imported data will create an error and not appear in the original
- It is possible for a user to edit the IMPORTRANGE() formula to read other portions of the spreadsheet. They would have to guess in the dark what portion to read, but it could still create a potential security problem, particularly if tabs are named predictably, such as being a student’s name
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to share only one sheet in Google Sheets. Want more? Check out all the Google Sheets Tutorials.