Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to allow access for importrange function via apps script?

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.
...
like image 465
Stefan Muntwyler Avatar asked Jan 20 '15 06:01

Stefan Muntwyler


People also ask

Why is this Importrange formula not working?

There are many approaches to fixing this issue: Hard refresh of the sheet and/or browser. Re-adding the IMPORTRANGE formula to the same cell (use the Google Sheets shortcuts Ctrl+X and then Ctrl+V or clear the cell and use Ctrl+Z to restore it)


3 Answers

@Franzi suggested using undocumented approach which works and does not require making a donor/source spreadsheet public. Here's how you can do it from Google App Script:

function addImportrangePermission() {
  // id of the spreadsheet to add permission to import
  const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();

  // donor or source spreadsheet id, you should get it somewhere
  const donorId = '1GrELZHlEKu_QbBVqv...';

  // adding permission by fetching this url
  const url = `https://docs.google.com/spreadsheets/d/${ssId}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;

  const token = ScriptApp.getOAuthToken();

  const params = {
    method: 'post',
    headers: {
      Authorization: 'Bearer ' + token,
    },
    muteHttpExceptions: true
  };
  
  UrlFetchApp.fetch(url, params);
}

Usually there is no need, but in some rare cases you might want to add the required oauthScopes in appscript.json manifest:

  ...,
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/script.external_request"
  ],
  ...
like image 183
kishkin Avatar answered Oct 01 '22 21:10

kishkin


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)
like image 32
Damien Avatar answered Oct 01 '22 22:10

Damien


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

like image 35
Franzi Avatar answered Oct 01 '22 23:10

Franzi