By default, Google allows sharing Google Docs or Google Sheets to other users with a valid Gmail ID. You can limit their access to the document on a different level like view/comment/edit etc. Google SpreadSheet may have multiple sheets, and there is no easy solution to share these individual sheets with your team members. We are going to discuss a solution to share only a particular sheet from a multiple sheets Google Spread Sheet.
Step 1: Protect Google SpreadSheet (Primary) to Limit Access to Specific People
This step is very important since we are going to use a link to share the primary document with another user. Google documents can view in public or viewable to anybody with a share link. You can change this access and limit only to certain members that you allow. Open the required Google spreadsheet (we say primary sheet) and click on the Share button on the right top corner of the document. Now click on Advanced from the “Share with others” window. Now you will be on the ShareSettings window, click on “Change” link. Chose “Off – Specific people” from the next Link sharing window and click on Save button. This step is critical since you are going to expose the share link with your team and you have to make sure they won’t be able to access your entire spreadsheet with this shared link.
Step 2: Get A Google Sheet Share Link
Once you complete the above step, now you will be back on the “Sharing settings” window and you can copy the link from the share window. Keep this link saved in notepad or somewhere for future use. If you miss this link, you can always come back to this screen and get the share link. To get the Google Sheet share link from the main screen, click on Share > Advanced > Link to share. Alternatively, you can get the same link from File Menu > Share > Advanced > Link to share.
Step 3: Create a New Google Spreadsheet (Secondary) to Share with Your Team Members
Now you need to create a new spreadsheet to share with the user. This is the sheet you are going to share with your team. Just go to Google Sheet application window and create a new spreadsheet with a valid name. There is no need to type any data inside of this sheet at this point.
Step 4: Import Google Individual Spreadsheet to A New Google Sheet
Well, in this step we are going to import the required individual Google sheet from the primary document to a new Google SpreadSheet (secondary) that you created in Step 3. We are going to use a function called “IMPORTRANGE” The syntax is:- IMPORTRANGE (spreadsheet key, range_string)
We chose row A1 to type Spread Sheet heading and used row A2 to insert the function. The import function is like this; =IMPORTRANGE(“sheet (primary) share link”,”share sheet name!share range start column: share range end column
In our example, we inserted the following URL and press Enter once you complete it. In this function, the URL is the same that we got in Step 2, Summary is the tab or sheet name from Primary Google SpreadSheet, and A & F are the cell ranges that you want to share. Don’t forget to insert the ‘!’ after sheet name and ‘:’ between row letters in the function. =IMPORTRANGE(“https://docs.google.com/spreadsheets/d/xxxxx”,”Summary!A:F”) Now you will see on A2 “#REF! and hover your cursor on top of it and click Allow Access. The secondary sheet will import the values now from the primary Google SpreadSheet specified tab/sheet. This trick will import only the values from the primary sheet. You have to take care of the styling and renaming the secondary sheet as per your requirement.
Step 5: Provide Your Members’ Limited Access to A New Google Sheet
You have to define the limited access to your team members. Click on Share button > Share with others window > Enter Gmail address of your team member > Change the access from “Can edit” to “Can comment”. This step protects new Google spreadsheet users from altering the share link that may expose more information than you intend to share. Now you are done with the Google Spreadsheet tab sharing. Whenever you make any changes in the Primary Google Spreadsheet tab, the secondary sheet values will automatically update with primary table values.
You can use this workaround to share individual tabs of a Google Spreadsheet with separate members from your team. Google lets you create, work and share these office documents with your teammates or office mates with a valid Gmail account. The beauty of this web application is more than one member can work on this document at a time; an ideal solution to draft your school project with your team. This workaround is very useful for schools, a small-scale business where they want to share individual sheets with particular people. The solution is a bit tricky, but works well and easy to implement. Let’s take a two-sheet Google SpreadSheet as an example and will show you how to share only the sheet named “Summary” with one of the team members. The downside of this solution is, you have to create separate secondary Google spreadsheets for each member on your team, but this will give you high security and your primary spreadsheet will be isolated and secure from other team members. Also, you can’t reflect on the cell format on the Secondary Google Sheet. Whatever the color code or format of the cell you did on the Primary sheet is limited to that sheet. It won’t copy to the Secondary with this solution. However, this workaround is a quick and free solution to manage multiple sheets separately with a master sheet. With this solution, you can share each tab of your primary sheet with the secondary Google Sheet. How can I lock a sheet like you have did in the first screenshot. The second sheet is “Transaction” it has locked symbol. How to do that? Can you explain please. One follow-up question, and apologize if this may be a more general question… a few of the secondary sheets, i would like to have it so the user has access to sort it. (my document is useful sorted by day then hour OR by hour then days). Is there a way to give each user sort of one-way access so that they may do whatever they please with the data on their end only? (maybe a user has a way to sort the data that i have not thought of; and would like to allow them to manipulate/sort the data in their own way without their changes affecting my sheet {or other users}) Thanks again! If that is the case, you can do in a reverse way. Keep master file as a synced version, and keep excel files for the chapter as a source file. If there is not a direct way to do this, are there any workarounds that you’re away of to create this function? Thanks in advance for the help. for ease of use for some of my non techy co-workers, i made the main file fully edit-able and sent the link for a very specific people. then the secondary file (public viewing) for all others to see.
Δ
