Pro Tip: Restrict Access to Worksheets in Google Sheets

 

Let's say you're tracking data on students and you want to look at all the data for your students in one spreadsheet, but you'd like to share certain data on certain students with various people. Seems like it should be easy to give different people access to different tabs, right?

Several times this year I have wanted to have a spreadsheet where I accumulate data of one type or another and I share one or more worksheets (or tabs or pages) of the spreadsheet with people to view. What I have found is that while you can give people editing rights to certain worksheets, there is not a native way to restrict viewing access to one worksheet. I did find a workaround and I am sharing it here in case you also need this functionality. I admit that the workaround is not as easy as clicking the share button and making some changes, but it's not as hard as it might initially look either.

1.  Create a spreadsheet that will hold all the data that you want to share. This will be your master copy of the data that only you can access. Organize the data in a way that will make it easy to export and share.
2. Create another spreadsheet that will pull data from the master copy. This new spreadsheet is the one you will share with viewers.
3. In the spreadsheet you will share, click in cell A1 and type this formula:

            =IMPORTRANGE("spreadsheetURL","worksheetname!cellrange") 

           Replace the items in red above with information from your master copy of the data.

spreadsheet URL: Copy the web address of the spreadsheet that is your master copy of the data and paste it between the quotation marks.

worksheetname: Between the quotation mark and the exclamation point, type the name of the worksheet (or tab or page) from the master copy of the data where the data you will export can be found.

cell range: list the first and last cell from the worksheet you selected that you want to import. The first and last cell will be separated by a colon. The cell range A1:J30 will import data in cells A1 through J30.

The first time you import the data, you may have to grant permission for the spreadsheets to talk to each other, but after you grant permission, if you update your master copy of the data, the second spreadsheet will automagically update as well.