Why?
The IMPORTRANGE function in Google Sheets allows the live content of one spreadsheet to appear within another. E.g.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sheet1!A1:C10")This imports the range of cells A1:C10 from Sheet1 of the spreadsheet with the given URL.
This can be inserted into a sheet as part of a running script. E.g.
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const dataSheet = spreadsheet.getSheetByName('Data');
dataSheet.getRange(1,1).setValue(`=IMPORTRANGE("${dataSpreadsheetId}","A1:F250")`);The above is part of a script that creates sheets for extracurricular club registers, one that imports student data (names, tutor groups, etc) from a central spreadsheet.
The issue to overcome is that when entered into a cell on the sheet, the IMPORTRANGE function requires the user to give the current sheet permission to access the “donor” sheet. This is done via a pop-up dialog…which isn’t going to work in the middle of a flowing script.
How?
Insert the following function into your script:
function allowImportRange(receiverId, donorId) {
let url = `https://docs.google.com/spreadsheets/d/${receiverId}/externaldata/addimportrangepermissions?donorDocId=${donorId}`;
let token = ScriptApp.getOAuthToken();
let params = {
method: 'post',
headers: {
Authorization: 'Bearer ' + token,
},
muteHttpExceptions: true
};
UrlFetchApp.fetch(url, params);
}Then simply call it after writing the formula to the sheet:
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const dataSheet = spreadsheet.getSheetByName('Data');
dataSheet.getRange(1,1).setValue(`=IMPORTRANGE("${dataSpreadsheetId}","A1:F250")`);
allowImportRange(thisSpreadsheetId, dataSpreadsheetId);Notes
The above solution to granting permission doesn’t appear to be documented in the official Google Sheets API. The function above is based on the blog post referenced below.
