create data validation (pulldown) from other spreadsheet

Question

I have current spreadsheet (named as INPUT). In this INPUT , i have a sheet named USER. I share USER sheet to other users so they can write data on it (as editor). On cell A1 , i will use data validation /pulldown from other spreadsheet (named as DATA).

The data validation can't use data directly from other spreadheet so in the INPUT spreadsheet , i create a new sheet (named as DATAIMPORT) and use importrange() to import from DATA.

It works fine but the DATAIMPORT sheet is visible to all my users. How if i want the hide the DATAIMPORT from my users?

Answer 1

Have you considered creating a form instead of sharing a spreadsheet for data input? Form responses automatically appear in a separate tab in the spreadsheet in a row-oriented fashion and are thus easy to process with spreadsheet functions such as query() and filter().

If the users need access to the spreadsheet for some reason, you can just protect and hide the DATA sheet to make it a bit more difficult to view.

To retain interactivity but take the data completely out of reach, you will need to create a web app. See the Web App Demo answer for more information and sample code.