Do you need to share a single worksheet tab? This post is going to show you how to share only one sheet with users in Google Sheets.

It has become an absolute necessity that people can collaborate and work irrespective of distance. Google Sheets makes it extremely easy for teams to collaborate on projects without any difficulty.

Google Sheets can allow multiple users access to a single spreadsheet simultaneously, while also providing live feedback regarding who is making what changes. Collaboration has never been any easier.

Sometimes you may need to share the entire workbook, but other times, you only want a user to have access to just one sheet. While sharing the spreadsheet is a fairly straightforward process, sharing only one sheet is not so easy.

In Google Sheets, there are no native solutions that allow a user to only share one sheet. However, there are workarounds with notable exceptions.

This post aims to explore these workarounds that will allow you to share one sheet and its limitations.

Share Only One Sheet with the GID

The gid is a code in a spreadsheet’s URL that links directly to a specific sheet.

Usually, when users open a shared spreadsheet they land on the first sheet. Then they have to navigate to the sheet containing the information they need.

But when you share a sheet alongside its gid, users are taken directly to that sheet.

In this example, the spreadsheet contains grade information about two students. To share the sheet so that the student named John has immediate access to his sheet without having to wander around, we’d just copy the sheet’s URL.

At the end of the URL, you will find the gid code which is gid=662745270 in this example.

You will also find that the gid for the first sheet differs, attesting to the fact that each sheet has unique gid identifiers.

The problem with sharing a sheet this way is that you can’t prevent other users from accessing the information on other sheets. This solution would be appropriate if you don’t mind other users seeing data on other sheets.

But you can tweak a few settings that can reduce the effect of this downside.

You can hide a sheet you don’t want other users to see, although this is not a foolproof solution. This is because when you unhide the sheet to access its information, other users also get access to the sheet.

Asides from that, you can only hide the sheet from every user, not from specific users.

For this to work as intended, users must have only Commenter or Viewer permissions. This means they can’t have editing permissions, or they will be able to unhide the hidden sheet and access its contents.

If you don’t mind that other users see information on other sheets, but you don’t want them to change or edit anything, not on their designated sheet then you can lock the sheet.

Check out this post to learn about protecting a sheet.

While this solution wouldn’t prevent other users from seeing specific sheets, you can be certain that users can only edit information on their assigned sheets.

Share Only One Sheet by Creating a New File

The first method discussed for sharing only one sheet has quite a few flaws. You can’t entirely keep other information in the other sheets secure.

Creating a new file can easily take care of all these issues. The process to do this is simple.

1. Click on the File menu.
2. Select the Make a copy option.

This will create a copy of the spreadsheet!

You can change the name of the new spreadsheet using the Name textbox.

Leave the options to Share it with the same people and Copy comments unchecked since you’re creating a new file for one specific user.

When you’re done click on Make a copy. This will create a copy of the spreadsheet in a new file.

Now that you have created a new spreadsheet, you can easily delete sheets with information you don’t want the user with whom you’d share this file to see.

After deleting the sheet, you can then share the entire Google Sheet file with the user.

Share Only One Sheet Using the IMPORTRANGE Function

Another method to get data from one spreadsheet to another is to use the IMPORTRANGE function.

With this approach, you can import exactly the sheet you need and wouldn’t have to delete any sheet afterward.

= IMPORTRANGE ( spreadsheet_url, range_string )

The IMPORTRANGE function allows you to connect to another sheet and get data from a specific range.

• spreadsheet_url: the URL of the spreadsheet you want to import.
• range_string: references the sheet name and range you want to import.

= IMPORTRANGE( "https://docs.google.com/spreadsheets/d/1yw-RS1ccPynnGcdRqZqwkbOW5HLqvC9xZi5U3Dl5gaY/edit#gid=0", "Michael!A1:D5" )

When you copy this formula and hit enter, it returns the #REF error.

You can fix this error. Place your mouse on the cell and you will get an Allow access pop-up.

Click on the Allow access button to grant access to the sheet.

The data is imported to the new spreadsheet.

Now that both sheets are linked, any changes made in the primary sheet will be reflected in the new secondary sheet.

If you don’t want to keep these sheets linked your can copy and paste the IMPORTRANGE data as values.

When you share a file this way, you eliminate most of the problems that are present with the first method. The user can’t see the information you don’t want them to see in other sheets and they can make edits and changes.

The one problem that comes with this approach is when you have multiple users with whom you need to share the file. You will have to create new files for multiple users.

Share Only One Sheet with an Apps Script

For this approach, we will create a script that will share the active sheet with a user.

Open the app script editor window by going to the Extensions menu and selecting the Apps Script option.

function shareActive() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();

  var newSheet = SpreadsheetApp.create("New Sheet");
  var id = newSheet.getId();
  var open = SpreadsheetApp.openById(id);
  
  var copy = sheet.copyTo(open);

  var ui = SpreadsheetApp.getUi();
  var user = ui.prompt('Enter user email address', ui.ButtonSet.OK_CANCEL);

  if (
    user.getSelectedButton() == ui.Button.OK
  ) {
  var email = user.getResponseText()
  }

  DriveApp.getFileById(id).addEditor(email);
}

function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu("Share Sheet")
      .addItem('Active Sheet', 'shareActive')
      .addToUi();
}

Copy and paste the above code into the editor. Hit Save and Run to grant the necessary permissions.

Refresh your spreadsheet and the script creates a new menu ribbon called Share Sheet. The custom menu has a submenu called Active Sheet.

When you hit this command, the script will create a copy of the active sheet as a new spreadsheet file. It will then trigger a prompt requesting the user email with which you want to share the newly created spreadsheet.

When you enter the user’s email and hit OK, the user gets an email with the link to the new spreadsheet.

Share Only One Sheet by Exporting as PDF

Exporting files is another approach to use for sharing a single sheet.

When you export, you can select exactly what part of the spreadsheet you want to include in the final document.

This means you can choose to export a whole sheet or a specific range.

Follow these steps to export a sheet.

1. Go to the File menu.
2. Select the Download option.
3. Choose the PDF file format for the final document.

On the right side of the next window, you will see there are a lot of settings you can apply to make your document come out as you want.

In this case, you will focus on the sheet settings. This is where you can choose what sheets or range you want to export. The preview shows how the file will appear in the final document.

4. Select Current sheet in the Export options.
5. Click on the Export button.

You can also change other settings like the Paper size, Page orientation, Scale, Margins, and Custom Page breaks.

A new pdf file will be created and automatically downloaded on your machine. You can share this file as an email attachment with anyone.

With this solution, you can be certain that you’re sharing only the information you want others to access. The obvious downside with this is that the user cannot make any edits to the document if it is exported as a PDF file.

Also, you must know that only the PDF file option allows you to select which sheets or ranges to export in the final document. The other options will download every content in the spreadsheet.

📝 Note: Only the PDF or CSV file option allows you to select which sheets or ranges to export in the final document. The other options will download all content in the spreadsheet.

Share Only One Sheet by Publishing to the Web

The Publish to web feature is another very useful approach to sharing a spreadsheet. It also allows you to share only part of the file.

Like the other approaches, it also has flaws in that the user may not be able to edit the information depending on what options you choose.

Follow these steps to publish your file on the web.

1. Go to the File menu.
2. Select the Share option.
3. Choose the Publish to web option in the submenu.

This will open the Publish to the web window, where you have two options for publishing the spreadsheet. You can choose the Link or Embed option.

The Publish to the web window opens with the Link tab in focus, and it is set to share the Entire Document by default.

4. Click on the Entire Document box and select which sheet you want to share.

You can change the format for sharing the document by clicking on the Web page box. You will get different file options for sharing the document from the dropdown options.

You can share as a CSV, TSV, and PDF file, all of which are automatically downloaded when anyone visits the link that will be generated once the document is published.

You can share the spreadsheet as a CSV or TSV file, both of which can be opened with MS Excel. This can allow them to make changes to the document if that’s what you want, and they can share with you the updated file.

When you share as a Web page, they can only view the sheet as a webpage and cannot make any changes. However, the web page data gets updated when you make any changes in the primary spreadsheet.

After selecting the sheet and file format, you can now publish the document.

5. Click on Publish.

A prompt will appear confirming if you want to share the sheet.

6. Click on the OK button.

As soon as you hit OK from the previous step, the Publish button gets greyed out and a link is generated for the sheet you want to share.

Now the sheet is live and anyone with the generated link can access the information.

If you want to remove access to the sheet through the link, click on the Publish content and setting dropdown and select Stop publishing.

When the prompt appears, click on OK to stop publishing the sheet.

To use the Embed option, select the Embed tab and select the sheet you want to share using the previous method.

After you click on Publish and select Ok from the prompt that appears, you will get an HTML code that will display the information on the spreadsheet when you add it to any webpage.

The information on the sheet will be updated as per changes made in the primary document.

You can stop publishing the sheet by following the steps described earlier.

Publishing to the web appears to be the most robust method for sharing a sheet that mitigates the problems that occur when using previous methods. You can be certain that you’re only sharing the data you want.

Depending on the editing requirements, you can choose to share it as a CSV or TSV file if you want the user to be able to alter the data.

But if you only want them to see the data without having the chance to change anything, then share it as a webpage.

This also avoids creating redundant spreadsheets with the purpose of sharing a single sheet.

Conclusions

Google Sheets doesn’t offer any native solutions for sharing only one sheet, but there are many workarounds that you can use.

These workarounds may not be perfect for every situation, but you will certainly find one that will suit your needs.

What do you think about these approaches to sharing a single sheet? Are there other approaches to sharing a sheet that you use? Let me know down in the comments section!