How to hide certain columns for a user?

Question

On a shared Google spreadsheet:

I don't want a specific user in my domain to view columns with sensitive data, but this person should still be able to edit the rest of the spreadsheet.

I tried creating a 'master' spreadsheet and using importrange to bring the data into a 'shared' sheet, but once I edit any cell in the shared spreadsheet, importrange no longer works as the sheet is emptied out.

Is there a quick way to do this, maybe using scripting?

Answer 1

I came here looking for a way to share only certain columns of a spreadsheet with a customer, but not all.

As noted in the other answer, using =IMPORTRANGE works well, but a clever customer could simply edit the function and see the other columns.

My solution was to first create a 'proxy' spreadsheet that imported only the columns I want the customer to see. This proxy spreadsheet is not shared.

Then, I created another spreadsheet that imported the columns from the proxy, and shared that spreadsheet with the customer. This way, even with edit privileges, it's impossible for him to see anything that isn't on the proxy spreadsheet.

A bit clunky to be sure, but it worked perfectly for my situation.

Answer 2

After some research and trying various different options, I was finally able to achieve what I want using the importrange function.

At first, I tried using a combination of hide columns & protect range, but this wouldn't work because a simple copy & paste would reveal the contents of the hidden columns.

Solution: The 'master' spreadsheet does not have any sensitive column data and can be shared with everyone in the organization... I then ADD the sensitive data to a new spreadsheet and use importrange to grab contents from the 'master'. (Previously I had the roles reversed, but this didn't work)

---

You may want to add unique keys per row entry so that sorting etc won't mess things up when you zip-up the sensitive data and the 'master' data.