Google sheets - searching the protected data

Question

Besides Hiding the sheets, is it possible to let my employee to search some specific data by query select the data from my main spreadsheet without viewing other data from my main spreadsheet?

Let say I have a main spreadsheet called MAIN My employee has a spreadsheet called SEARCH

He can use the SEARCH to do like this

but I don't want to let him to view the MAIN in order to protect the other data of MAIN.

Is this even possible? or do I need some extension like Appscript or something else?

Answer 1

There is not much flexibility in the way you can handle permissions in Sheets. If you let the spreadsheets connect via ImportRange you won't be able to limit the access of your spreadsheet by the user modifying the range into that formula.

Obviously you can import data with AppScripts, but here you have a possible workaround:

Spreadsheet 1: MasterSheet, employee does not have any kind of permissions

Spreadsheet 2: ImportRange from desired range in Master. Employee could have only viewer permissions or none

Spreadsheet 3: ImportRange with all permissions for employee, importing data from Spreadsheet 2. This way he'll only be able to catch your already selected data but nothing more