Google Sheets IMPORTRANGE: Prevent clever editors from accessing other ranges of your imported sheet

Note! This is part of a series on using IMPORTRANGE in Google Sheets. If you don’t know how to use IMPORTRANGE or what it is, I encourage you to head back to the first tutorial here:

https://yagisanatode.com/2022/01/26/importing-range-data-from-one-google-sheet-to-another/

And here is a link to the sample data that I am importing if you want to play along:

Template – Project Tasks (IMPORTRANGE)

You’ve grabbed data from a source Google Sheet and used the IMPORTRANGE function to insert it into your destination sheet carefully selecting the range and sheet tab of the data that you want to display.

So you might be thinking, Great! I’ve managed to only display the content from my original Google Sheet that I want my users to see and hidden the rest from them. 

While this may be the case if you are only providing ‘View’ or ‘Comment’ permission to a Google Sheet, I do have an ugly little surprise for you if you.

via GIPHY

Are you sitting down? Are you comfortable? Do you have a support network nearby?

Let’s have a look at the IMPORTRANGE docs:

So basically, if you have other editors on your new sheet editing your document, they can access anything in the imported Google Sheet by copying the access granted IMPORTRANGE. All they need to do is change the Sheet tab and the range location.

There is a solution

The good news is that we can significantly minimise the risk of this occurring.

There are two things that a ‘clever 🕵️‍♀️’ editor needs to know to use IMPORTRANGE to access other parts of your original imported Google Sheet:

1. The Sheet Tab names.
2. The URL to the original Google Sheet.

Sheet Tab names

While editors of your destination sheet won’t have any access to your originally imported sheet (Unless you have granted it previously) they will be able to access other ranges and in different sheet tabs of your source imported sheet from the current sheet. That is, of course, if they can guess the sheet tab name.

For example, let’s say that I have been lazy in my source Google Sheet and left all my sheet tabs as the default ‘Sheet1’, ‘Sheet2’, ‘Sheet3’ etc.

Using our example above, all they need to do now is change the second argument of the IMPORTRANGE function to something like this to see what is in that Google Sheet tab.

From this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Av1N4H1MKJIJXD88Ia7F-uEm3ZXN1HZEhmq8zpk_fRA/","'Task List'!A2:G")

To this:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Av1N4H1MKJIJXD88Ia7F-uEm3ZXN1HZEhmq8zpk_fRA/","'Sheet5'!A2:G")

One simple workaround here is to create more obscure sheet tab titles for each of your tabs in your workbook.

You can learn about renaming your Google Sheet tabs here:

Google Sheets Beginners; Creating Sheet Tabs (14)

Hiding the URL to your IMPORTRANGE

Another approach is to hide the URL to your source imported data. Well, actually, we will be hiding the entire IMPORTRANGE formula in this example.

First head to your destination Google Sheet and the sheet tab where you have imported the source data. Rename the sheet tab to, say, ‘Data’.

Next, create a new sheet tab and call it something like, ‘Project Data’.

In cell A1 of ‘Project Data’  we are going to create an array that references the imported data from our ‘Data’ sheet tab. We can do this by encasing the range in curly braces so our range will look like this:

={Data!A1:G}

Now, select the dropdown menu of the ‘Data’ sheet tab > Protect sheet.

A sidebar will appear. You don’t have to give the protected sheet a title. Just select ‘Set Permission’.

A pop-up window will appear. Select Restrict who can edit this range (1). Then select Only you (2). Finally hit Done (3).

You have now protected the ‘data’ sheet so that no one can edit it.

Users will still be able to copy and paste your IMPORTRANGE formula from your visible ‘Data’ sheet tab. However, one helpful side-effect of protecting a sheet is that when you hide the sheet other users cannot open it and see what is inside.

Click on the menu arrow of the sheet tab again and select Hide to hide the ‘Data’ Google Sheet tab.

If a user tries to unhide your hidden data sheet they will get the following error:

This is approach makes it difficult to guess where the data is coming from and is likely a solid approach for abstracting away the IMPORTRANGE data. It is probably satisfactory for most cases. The editor would have to make a guess as to what the formula is before they can think about extracting the URL to use it.

Nevertheless, a tip-top sneaky editor may still be able to divine the formula with a simple little known function: =FORMULATEXT(Data!A1). 

An intermediary sheet

A final more protective approach might be to create an intermediary Google Sheet. Here. You can import the ranges that you want to share publically on your other Google Sheets. Then, you can import this sheet into your shared Google Sheets.

How to make it

Let’s keep using the Template – Project Tasks (IMPORTRANGE) Google Sheet from the example. We only want to display the ‘Task List’ from A2:G.

First, we create a new Google Sheet. Let’s name it ‘Intermediary – Only data to share’. We won’t worry about renaming the sheet tab, so we will leave it as the default ‘Sheet1’. Next, we import the range from our Project Task sheet with our IMPORTRANGE formula:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Av1N4H1MKJIJXD88Ia7F-uEm3ZXN1HZEhmq8zpk_fRA/","'Task List'!A2:G")

We don’t share this sheet with anyone else. It is just an intermediary.

Next, we create another new Google Sheet. This will be the one that we share our editors on. Call it, ‘Imported data’.

Now we import the range from our intermediary sheet.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/19SpQsJMPiypkTuJ1c23Qi3GZrQOaqJYm2a3QmLekDoM/","Sheet1!A:G")

Now a curious editor can only view what you have shared in your intermediary and not your core sheet.

The downside

The downside to this approach is that you are increasing the processing time and burden by having to go through one extra Google Sheet before arriving at the sheet that you want to share with other editors. This can slow things down for you and may cause errors in your Google Sheets.

A foolproof approach

One final approach is that we can always use Google Apps Script to copy data from a core Google Sheet and import it into your shared Google Sheet.

You can run the script manually after each time you make changes or you can use a custom time or event trigger to automatically send updates to your connected sheets.

This way you will know with confidence that only the data you send over will be available to the editors of your destination Google Sheet.

You can find a template of the script that you can you for your own project here:

Copying the values from one Google Sheet to Another with Google Apps Script.

The video tutorial

The IMPORTRANGE series

• Introduction to IMPORTRANGE.
• Google Sheets IMPORTRANGE: Using VLOOKUP on imported Google Sheets data.
• Google Sheets IMPORTRANGE: Using FILTER and QUERY on imported Google Sheets data.