In this article we will show you how to share individual spreadsheets from the same workbook in Google Sheets. We cover how to do this with an add-on, with IMPORTRANGE and by copying and pasting.
A simple and efficient way to share a single sheet in Google Sheets is to use the copy paste method. This method involves copying sheet data to a new sheet. This will allow the recipient to make changes to the data once shared.
It is important to remember that as this method involves creating a new sheet so any changes made in one sheet will not be reflected in the other.
For the purpose of this demonstration example worksheets were created. Access these and follow along by clicking the links below:
Worksheet Main:
https://docs.google.com/spreadsheets/d/1rV-EJJFJS69u2W6Ds5rkQadx5QCrnd4TG8qjEdlsXH8/edit?usp=sharing
IMPORTRANGE Worksheet 2:
https://docs.google.com/spreadsheets/d/1JLz8sCDrPeRswaLDNeP_UHBsjnMMrs3TS5DbUSaMB6A/edit?usp=sharing
Click any cell in the sheet to share and press Ctrl (Cmd on Mac) + A to select all cells. Alternatively click the grey box next to the labels for row 1 and column A.
In the example below we can see all the cells in sheet2 have been highlighted.
With the data highlighted press the keyboard shortcut Ctrl + C (Cmd + C on Mac). This will copy the entire data in the sheet.
Alternatively from the file menu click Edit and from the displayed menu select Copy.
From the file menu select File followed by New > Spreadsheet. A new spreadsheet will be created.
Click on cell A1 in the new spreadsheet and use the paste keyboard shortcut. Press the Ctrl + V keys (Cmd + v for Mac) to paste the data into the new spreadsheet.
As can be seen in the example below, our data from sheet2 has now been copied to Sheet1 of the new spreadsheet including the original formatting.
Located at the right-hand side of the display is the green Share button. Click this to display the share options menu.
From the section labelled General access expand the dropdown menu currently displaying Restricted and select Anyone with the link.
On the right hand side of the General access section a new dropdown will display which allows you to change the access permissions. Here are the following options:
Viewer: Selecting this will allow the recipient to view the data only, they will not be able to leave comments or make any changes to the data.
Commenter: This option will allow the recipient of the link to view and comment on the data. They won't be able to make any edits.
Editor: Anybody with the link will be granted full edit access. They can view, comment or edit the data.
In our example we will select the Editor option.
At the bottom of the share options select the Copy link button followed by the blue Done button. The link is copied and the sharing options menu will close.
A notification will display at the top of the screen stating that the Access permissions have been saved.
Send the link to your intended recipient/s and they now have access to the sheet.
By using the IMPORTRANGE function in a new spreadsheet we can share a sheet that will display changes made to the original sheet.
This method has a limitation that it cannot support editing to the new workbook as the IMPORTRANGE function will break. Therefore if using this method we need to set the permissions so the recipient can view or comment on the data but not edit it.
For the purpose of this demonstration example spreadsheets were created. Access these and follow along by clicking the links below:
Worksheet Main:
https://docs.google.com/spreadsheets/d/1rV-EJJFJS69u2W6Ds5rkQadx5QCrnd4TG8qjEdlsXH8/edit?usp=sharing
IMPORTRANGE Worksheet 2:
https://docs.google.com/spreadsheets/d/1JLz8sCDrPeRswaLDNeP_UHBsjnMMrs3TS5DbUSaMB6A/edit?usp=sharing
Click the tab at the bottom of the screen for the sheet you need to share.\
From the browser's address bar at the top of the display either triple click or drag to highlight the URL and press Ctrl (Cmd on Mac) + C to copy it.
In the below example, we click Sheet2 from Workbook1. The sheet is highlighted. From the top window, we copy the URL which starts with: docs.google.com/spreadsheets….
Create a new workbook and select cell A1 in the new workbook.
With cell A1 selected type the formula using the format:
=IMPORTRANGE(“URL”, “SheetName!A:Z”)
Formula breakdown:
IMPORTRANGE: The IMPORTRANGE function informs Google Sheets that we intend to import a range of data that is in another worksheet.
URL: The URL is commonly known as the website address. This will be the URL of the spreadsheet we copied earlier.
SheetName!: This is the name of the sheet you want to import the data from, this must match the sheet name exactly. In our example this will be: Sheet2!
A:Z: This specifies the range to import. A:Z will import the entire sheet range.
In our example we want to import all of the data from Sheet2, therefore we need to specify the URL copied from the original workbook along with Sheet2, as we are importing the entire range we need to enter A:Z.
In our demonstration below we have entered the formula:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1rV-EJJFJS69u2W6Ds5rkQadx5QCrnd4TG8qjEdlsXH8/edit?usp=sharing","Sheet2!A:Z")
With the formula inputted press the Enter key, and the cell will display #REF!
This is because for the first time connecting the sheets, you will need to allow access permissions. To do this click on the cell containing the formula again to see a pop up box.
At the bottom of the pop up box click Allow access.
The data from the main workbook will now appear in your new workbook.
As we can see below, the data from Sheet2 of the main workbook has been imported. Only the data is imported, the original formatting is not.
Now click the green Share button located at the top, right hand side of the sheet to display the sharing options.
In the General access section of the sharing options menu, expand the drop-down menu labeled “Restricted” and change this to “Anyone with the link”. On the right hand of this section, a new dropdown menu will appear. This sets the role of anybody with the link, the options are:
Viewer: This option will allow the recipient of the link view permissions, they will not be able to comment on or edit the data.
Commentor: The link recipient will be able to view and comment on the data but will be unable to edit. Commenting on data will not affect the IMPORTRANGE.
Editor: This option grants the recipient the ability to view and comment on the data. It will also allow them edit access. Because editing any data on this sheet will break the IMPORTRANGE this option is not recommended.
In our example we will set the access role to Viewer as we do not want to allow the recipient editing privileges due to the IMPORTRANGE.
With the sharing permissions changed, click the Copy link button to copy the link to the sheet, then click the blue Done button.
The permissions are then updated and you will see a notification confirming this at the top of the display.
You can also share only one sheet via the Sheet Share add-on in Google Sheets. In the demonstration below, we will show how to install and use this helpful add-on.
For the purpose of this demonstration example worksheets were created. Access these and follow along by clicking the links below:
Worksheet Main:
https://docs.google.com/spreadsheets/d/1rV-EJJFJS69u2W6Ds5rkQadx5QCrnd4TG8qjEdlsXH8/edit?usp=sharing
From the file menu at the top of the display select Extensions. From the dropdown menu click Add-Ons followed by Get add-ons from the side menu.
A popup window will now display the Google Workspace Marketplace, in the search bar type “Sheet share”.
Now press enter, and look for the Sheet Share - Share Single Sheet add-on provided by Sheet Automation.
Click the Sheet Share add-on from the displayed results and click the blue install button located on the top-right of the display.
At this point you may need to provide permission, select continue, choose your Google account, and select accept. You will then be presented with a success screen showing that Sheet Share add-on has been installed. From the success screen click Done.
From the file menu at the top of the display select Extension. From the dropdown menu click Sheet Auto Share followed by Open
The add-on will now open, and a “Sharing” menu will now open at the right-hand side of the screen.
From the Sharing menu, select Share. A new menu will display a number of parameters to create a new sheet share. The first thing to do is to name your share. Simply enter this in place of the placeholder text under the Name heading.
In our example below we have named our sheet share ‘LidoShare1’
Click the box below the sheet heading to display the sheet sharing options within the add-on. From this menu select the sheet you want to share from the dropdown menu underneath ‘Select the sheet to share’.
Now choose the level of protection required for your sheet. In the dropdown menu underneath “Protection” select either “No protection” or “The whole sheet”
For our example we will choose “No protection”
Click “Save” to return to the sheet share main menu.
Now access the User settings by select “Click to add user” under the User header of the main Sheet share menu.
From this menu click the box below the heading “Specify the user to share with” and enter an email address in the text box below. In our example we will enter a test email.
You can choose the sharing permissions that will be assigned to the person that the sheet will be shared with by expanding the dropdown menu underneath “Permission” select either “Editor” or “Viewer”
Editor: Allows the recipient edit access permissions, they can view and edit the data on the spreadsheet.
Viewer: Allows the recipient view-only permissions. They will not be able to edit the data but can view it.
For this example we will select Editor, therefore, giving the recipient edit access permissions.
With the user settings applied, click the green Save button to be taken back to the main Sheet Share menu. From this menu click Save once more to save and apply the overall share instance.
With the share saved click the three dot menu next to the name of your share from the Sheet Share menu to expand it. Within the three-dot menu, we can send a notification, Open the shared sheet or delete the instance. Select Send notification. This will send all users contained within the share instance an email to notify them. Your single sheet will now be shared.
As we can see below, our share named LidoShare1 has been saved and the users notified.
We hope this article has helped you and given you a better understanding of how to share only one sheet in Google Sheets. You might also like our articles on how to share only one tab in Google Sheets and how to find and replace in Google Sheets.
To optimize your workflow, we recommend reading our guide on how to do mail merge from Google Sheets.
