29

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?

Rubén
  • 34,714
  • 9
  • 70
  • 166
lee
  • 1,036
  • 2
  • 14
  • 24
  • I think you can use a protected sheet. Did you ask here - https://productforums.google.com/forum/#!categories/docs/spreadsheets ... Stackoverflow is code related, so your question is not a good fit. Good luck. – eddyparkinson May 28 '14 at 07:35
  • hey, thanks, I tried searching there but no luck. I'll look into protected sheets. I was thinking if there is a way to do this via the API, like testing for the user's email address like Session.getActiveUser().getEmail() – lee May 28 '14 at 09:05
  • 1
    The API just lets you automate, but you are not looking to automate, you want to control access. If what you want is possible, then there will be a way to do it with the spreadsheet directly. – eddyparkinson May 29 '14 at 00:48

2 Answers2

30

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.

Joel L
  • 3,031
  • 1
  • 20
  • 33
lee
  • 1,036
  • 2
  • 14
  • 24
  • 1
    What if the person you are protecting the sensitive data from simply alters the importrange function? Wouldn't they be able to import any column they choose? – jbright Dec 12 '16 at 19:04
  • @jbright yes, of course they could - but the 'new spreadsheet' with the importrange function is (in my case) only for my use :) – lee Dec 14 '16 at 09:50
  • @lee Hi, Lee. I've the very same need. It would be very kind from you if you could share some more detail about how you manage to "zip-up" the sensitive data with 'master' data by means of a unique row id. –  Feb 21 '19 at 11:43
  • 1
    Maybe this can help, same solution but more detailed https://infoinspired.com/google-docs/spreadsheet/how-to-hide-tabs-from-specific-people-in-google-sheets/ – aUXcoder Jan 27 '20 at 16:06
10

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.

Mark Chapel
  • 491
  • 6
  • 11