Search code examples
google-apps-scriptgoogle-sheetsgoogle-workspaceimportrange

programatically connect two Spreadsheets so you can IMPORTRANGE without having to manually authorize

First, this is for Enterprise G-Suite. Sharing/publishing outside of work domain is disabled.

This is my setup:

  1. template Spreadsheet in a folder on team drive 1
  2. destination Spreadsheet in a folder on team drive 1
  3. destination folder on a team drive 2

I have a script that:

  1. Copies #1 to #3
  2. Adds an IMPORTRANGE function to #2 that pulls data from the new file created in #3

Now, when I go into #2, I see the import range function there is an error and I have to authorize the sheets: "You need to connect these sheets.". Below is a screenshot:

enter image description here

If I click "Allow access" it works and does pull data from the newly copied file.

What I am wondering is if there is a way to authorize the access programatically in my code so I don't have to open #2 and manually authorize?

var sFileID = "ID of template Spreadsheet in a folder on team drive 1";
var dFileID = "ID destination Spreadsheet in a folder on team drive 1";
var dFolderID = "ID of destination folder on a team drive 2";

// get the 2 files and the folder
var sFile = DriveApp.getFileById(sFileID);
var dFile = DriveApp.getFileById(dFileID);
var dFolder = DriveApp.getFolderById(dFolderID);

// copy the template file to the destination folder
var nFile = sFile.makeCopy("test", dFolder);

// open the destination spreadsheet
var dss = SpreadsheetApp.openById(dFileID);

// get the sheet
var ds = dss.getSheetByName("Sheet1");

// append a new row with the IMPORTRANGE function pulling from the new file
ds.appendRow(['=IMPORTRANGE("' + nFile.getId() + '", "Sheet1!A2:D2")']);

SpreadsheetApp.flush();
Solution

  • There is no way to automatically authorize IMPORTRANGE. Google Apps Script either Google Sheets API have a method that does this.

    According to this answer one alternative is to share the source spreadsheet with anyone with the link.

    Related