I have created several spreadsheets on google drive. So i'm the owner of all of them.
I have a spreadsheet (MASTER) which works as a master file to export data into other spreadsheets (ACTIVITY REPORT) that I have shared with a team of consultants. The data exported enables them to use lists as data validations and so on, which helps the master file to analyse their activity (no need to clean the data).
Since I have approved the importrange of the master on the spreadsheets that are shared with the team of the consultants, I know that there is a connection created. Hence, they could change the formulas on their ACTIVITY REPORT to get access to data that I don't want them to see on my MASTER file.
Using Range Names or a specific sheet for all the things to be exported/imported prevents them from browsing what they want (they basically need the ID of the spreadsheet and a range which includes the name of a sheet or a named range).
But I wanted to know if Apps Script coud allow them to do that (browse my MASTER File to know how I named my sheets, ranges, and so on), because if it is the case, then authorizing an importrange on a spreadsheet used by editors could allow them to basically import whatever they want on their file, including sensitive or confidential information, using the named ranges or through the names of the sheets that are not dedicated to the export.
But I wanted to know if Apps Script could allow them to do that (browse my MASTER File to know how I named my sheets, ranges, and so on),
During my brief testing, apps script authorization/permissions seem to be separate from importrange authorization(although it is possible to gain importrange permissions from apps script - more on that below). So, A Editor on the Slave Spreadsheet(The spreadsheet on which data is imported) is not able to access data on the Master Spreadsheet through Apps script, if He/She already doesn't have access to the Master. Editor on the slave was however able to access the master through importrange formula.
From the support,
Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet. The access remains in effect until the user who granted access is removed from the source.
Using Range Names or a specific sheet for all the things to be exported/imported prevents them from browsing what they want (they basically need the ID of the spreadsheet and a range which includes the name of a sheet or a named range).
Essentially, You want to use Sheet name/Range name as password. During my brief testing, the following issues are encountered:
Brute forcing is a possibility. SheetName/RangeName should have >30bits of entropy,i.e., Random sheet names/special characters/uppercase/lowercase letters with >8 characters.
Nothing much is revealed about how importrange operates. Which means, Google could easily allow apps script access in the future and you might not know it. But, even today, there is an undocumented api to gain access through apps script, if the script has sufficient permissions
Finally, and most importantly, Sheet-name/Range-name is completely optional.
The sheet_name component of range_string is optional; by default IMPORTRANGE will import from the given range of the first sheet.
In other words, Any editor has unrestricted access to the first sheet. For example the following import is completely valid:
=IMPORTRANGE("SPREADSHEETURL", "A1:Z")
Furthermore, If you accidentally move a sensitive sheet in the master spreadsheet to the left, making it the first sheet, all editors can easily access that sheet after the move. To minimize this risk, you could create a system of spreadsheets: Master>slave1(has only one sheet imported. No editors)>slave of slave1(1 editor; import from slave1). In this case, even if slave1 sheet names are guessed, Master sheet is safe.
It's a bad idea to use importrange() for security of any sensitive data. Furthermore, Using importrange for more than a few sheets is unreliable at best. It's best to avoid importrange completely for all purposes. Although a bit tedious than formula, For spreadsheet syncing, it's better to use "Standalone" scripts both from performance and security perspective.