Google-sheets – Restricted view of a sheet, with each person seeing only their rows

google sheets

I created a Google Spreadsheet. Column B contains the name of my employees. In Column C there is a task for every employee.

Different employees need to have access to this document, but I prefer my employees to see only "their" rows and "their" tasks. Meaning the rows in which their name appear in column B.

Is this possible?

Best Answer

If you did this just for the convenience of your employees, then filter views would do the job nicely. But filter views don't offer any data protection: if someone can see the filter view, they can see the entire sheet by turning off the filter view.

To actually protect the other rows from being viewed, you can create a separate spreadsheet for each employee and put the following in cell A1 there: 

=query(importrange(spreadsheet_key, "A:C"), "select * where Col2 = 'Joe Doe'")

Here spreadsheet_key is the URL of your master spreadsheet, and Jon Doe is the employee for whom the spreadsheet is intended. Then you can share the Jon Doe spreadsheet with that person so they can only view it. They will not be able to change the query string, and therefore will not be able to access other rows of the master spreadsheet. And if they make a copy of the spreadsheet, the importrange will stop working, because access via importrange has to be authorized in the new spreadsheet again.

Technical remark: when entering the above command, put simply =importrange(spreadsheet_key, "A:C") first, so that you'll be prompted to allow access to master sheet. Inconveniently, having importrange inside another command does not allow the request for authorization to bubble up to user interface.

Related Topic