How to allow access for importrange function via apps script?

14
votes

When you enter an importrange function manually into a spreadsheet you receive a pop up and must 'allow access'.Pop up to allow access for importrange

However, I'm trying to find a way to do this via a script because I'm creating many spreadsheets, each with a query-importrange function (I 'own' the spreadsheet which has data to import). There's too many for me to manually 'allow access' via the pop up and update the function to include the query function.

Hence, I'm looking for a function call in apps script that can perform the same action that the pop up did. Code segment example below.

Does anyone know of a function that can 'allow access'? Stefan

// create new spreadsheet file
...
var ss = createSpreadsheet(fileName);
var spreadsheet = SpreadsheetApp.open(ss);
var sheet = spreadsheet.getSheetByName("Sheet1");

// Add student as Viewer
spreadsheet.addViewer(studentEmail);

// Add ImportRange function 
var sheet = spreadsheet.getSheets()[0];
var cell = sheet.getRange("A1");
var filter = "select * where Col3='" + studentEmail + "'";
var qry = '=QUERY(importRange("' + fileKey + '","14-15S2!A1:AE");"' + filter + '";1)';
cell.setValue(qry);
// I need a function to 'allow access' here, so the function can be allowed access. Otherwise, it throws an error.
...
3
javascriptgoogle-apps-scriptgoogle-sheetsimportrange
I don't think it is possible to call a function to allow access. Instead you can access the sheet by name or ID and get the range you wanted in App script. Hope that helps! - KRR
Have you tried running some main function directly from the GAS IDE, supposedly google looks through your code and lets you auth in one step Do you own the sheet? - JZL003
Possible duplicate of How can I make an apps script to instantly allow access to all imported elements in a Google Spreadsheet? - Rubén

3 Answers

11
votes

I had a problem similar to this and found the answer was to alter the permissions of the spreadhseet file from which you are importing data (the "filekey" in your example").

This is the google app script that made "Allow Access" go away for me:

file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW)
1
votes

All I did was share the 'source' sheets - the sheets from which ImportRange grabs its data - with the people in my organsation who are using the gsheet template. I edited the template by clicking the authorise access button and all sheets that I have made from the template since have worked.

The ImportRange function works without me having to re-authorise access within each new sheet created from the template. I hope it works for others in my organisation.

1
votes

I'm doing it automatically using the gspread python library by calling the addimportrangepermissions endpoint.

    sheet.client.request(
        'post',
        f'https://docs.google.com/spreadsheets/d/{sheet.spreadsheet.id}/externaldata/addimportrangepermissions',
        params={'donorDocId': external_sheet.spreadsheet.id}
    )

I know it's not via the apps script, but it could provide you some hints on how to do it there.

Note: I didn't find any doc about this, so I though this could help anyone trying to do the same in any platform (app script, python, etc.).