2

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();
Rubén
  • 34,714
  • 9
  • 70
  • 166
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89

1 Answers1

1

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

Rubén
  • 34,714
  • 9
  • 70
  • 166