How can I make an apps script to instantly allow access to all imported elements in a Google Spreadsheet?

Question

I have to work with google spreadsheets. I am using some template spreadsheets which all contain a lot of links to other spreadsheets. The problem is that whenever I make a copy of the template spreadsheet to use most of the links(created with the importrange function) require me to give them access again

What I want to do is make a script that would allow me to import all the links in one press. I tried to make an app script but I can't found any way to access the textbox with the "allow access" button so that i can make my script. My questions are: is there any way to access the "allow access" button programatically? or is there any other way to solve my problem and allow access to all links in one press?

Answer 1

I had to face to the same issue. The only way to avoid this Allow Access for importrange is to put the linked document (destination of the importrange) public, or shared by the link, which is public too, but if readers have the link. Then there is no requirement of authorizing importrange. Google should avoid this authorization access if the 2 documents are from the same owner, I don't understand where is the security breach here.

Answer 2

Well, what I've done doesn't really solve the problem, but helps a lot. I imported the range of all Spreadsheets I wanted to a third Spreadsheet. Then I imported that range to my model. So, when a new file is created, you only have to allow access to one Spreadsheet instead of many.

As I said, it doesn't solve the problem but it is a nice workaround.

Answer 3

No, you can't access "Allow Access" button programatically, because it would be a security breach in a system.

I think that the best way to grant access will be iterating through list of spreadsheets' ids and opening them like this:

var idList = [...]; // here are all the ids

for (var i = 0; i < idList.length; i++) {
    SpreadsheetApp.openById(idList[i]); // trying to open the spreadsheet by id
}

That way user should be asked to grant access to script to access every spreadsheet automatically.

Then you should be able to import ranges from script rather than from the spreadsheet itself. Use Spreadsheet and Sheet classes and getRange() method in particular.

Answer 4

Basically, you just need to give access to anyone by the link before you input importrange function. It should look like this:

DriveApp.getFileById(id).setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);

And then make it private again. In that case, importrange will work instantly without clicking give access button.

DriveApp.getFileById(id).setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.EDIT);

You may also need flush().

Answer 5

Access to the 'imported' document appears to be determined by the sharing settings on the imported document at the time the 'host' document (not the template) is created.

For example:

1. Template A uses importrange() to include data from document B
2. Sharing on document B is set to anything other than "Anyone on the Internet with this link can view"
3. Template A is used to create document C

Document C will not be able to import the content from document B. You will have to manually allow access.

If, before you manually allow access, you close document C, change the sharing on B to 'Anyone on the Internet with this link can view' and then reopen document C it will still fail to import the data from B. When document C was created it did not have access to document B for import, therefore it will never have access unless you manually grant access.

On the other hand:

1. Template A uses importrange() to include data from document B
2. Sharing on document B is set to "Anyone on the Internet with this link can view"
3. Template A is used to create document C

Now document C can import the data from document B with no problem.

If you now change the sharing on document B to 'Restricted' document C will continue to import data. Similar to the first case, when document C was created it had access to document B, therefore it will always have access.

The importrange() uses the sharing as it is for the imported document at the time the host document is created.

Based on this and the code provided by Adrian Mole I set up a workflow for creating my documents from templates as follows:

1. In a Google Apps script run through list of included document ids and set sharing to public:

   var idList = { "doc-id-1", "doc-id-2", "doc-id-3", [...] };
   for( id=0; id < idList.length; id++ ) {
     DriveApp.getFileById(id).setSharing(DriveApp.Access.ANYONE, DriveApp.Permission.VIEW);
   }
   

2. Create multiple documents from the template

3. Back in Google Apps script use similar code to close access to all the imported documents:

    var idList = { "doc-id-1", "doc-id-2", "doc-id-3", [...] };
    for( id=0; id < idList.length; id++ ) {
      DriveApp.getFileById(id).setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.EDIT);
    }
   

The documents I create can all import data correctly (each contains multiple importrange() functions) but when I am done all the imported documents are fully secured again.

Note that between steps 1 and 3 while the documents are being created from templates the imported documents are all publicly searchable and available on the Internet. It is theoretically possible for a malicious person to create an importrange() in a spreadsheet of their own to import your documents and they would continue to have that access after performed step 3 and 'locked' your documents again.