title: programatically connect two Spreadsheets so you can IMPORTRANGE without having to manually authorize
body: <p>First, this is for Enterprise G-Suite. Sharing/publishing outside of work domain is disabled. </p>
  
  <p>This is my setup:</p>
  
  <ol>
  <li>template Spreadsheet in a folder on team drive 1</li>
  <li>destination Spreadsheet in a folder on team drive 1</li>
  <li>destination folder on a team drive 2</li>
  </ol>
  
  <p>I have a script that:</p>
  
  <ol>
  <li>Copies #1 to #3</li>
  <li>Adds an <code>IMPORTRANGE</code> function to #2 that pulls data from the new file created in #3</li>
  </ol>
  
  <p>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:</p>
  
  <p><a href="https://i.stack.imgur.com/Pj1MK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Pj1MK.png" alt="enter image description here"></a></p>
  
  <p>If I click "Allow access" it works and does pull data from the newly copied file.</p>
  
  <p>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?</p>
  
  <pre><code>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();
  </code></pre>