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

Solution 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.

Solution 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.

Solution 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.