Is there a way to automatically duplicate a large number of Google Sheets, name them, then copy their URLs to another sheet?

Question

Background:

For a genealogy project, I've created a Google sheet where participants enter all details of one ancestor. (FILEXXXX)

In another Google sheet (not just another tab/sheet, another FILE) I've built a ancestor tree in the sheet (ANCESTOR TREE) with room for 12 generations (8191 ancestors) which grabs info from another sheet in the same file (LIST OF INDIVIDUALS) and colours each cell based on conditional formatting rules. (Yes, it took me awhile to manually copy the formulas and change row numbers in 8191 cells).

The sheet (LIST OF INDIVIDUALS) uses the importrange-function to fetch selected info from each (FILEXXXX) based on the file URL that is manually placed in the L column on each row.

Everything above is working great BUT I manually have to create 8191 duplicates of the first (FILEXXXX), rename each file, change the value in cell A6 to the same number as the filename (I've tried a script to fetch filename but it's unstable since it only updates filename every 60 minutes or so). Finally, I have to copy the URL of each duplicated file and paste it into the L-column in the corresponding row in (LIST OF INDIVIDUALS).

I've manually duplicated and edited 670 google sheets so far, averaging 50 duplicated files in 10 minutes but it still feels like a never ending project.

I know there are better ways to build this, but at this stage I'm not interested/don't have the time to start over again

Question:

Is there a way to automate this?:

1. Duplicate file named 670
2. Rename it to 671
3. Change cell A6 to 671 in the renamed file
4. Copy the sheet URL
5. Paste the URL in column L row 671+1 in another Google Sheet

And then repeat step 1-5 above until I have 8191 google sheets?

Answer 1

Here is a (stand-alone) script that attempts to do this. An important caveat: scripts are subject to quotas, one of which is: no more than 250 spreadsheets created per day. So the script will be halted before finishing the job if you run it in the present form. You may want to change the upper bound to 671+250, and rerun the script with different starting values on different days.

The logic is simple: the file number 670 is used as a prototype for all of them, via makeCopy method. Each newly created file is then modified via SpreadsheetApp, and its URL is recorded in master sheet.

function copy() {
  var masterId = ' ID of your master spreadsheet';
  var fileId = ' ID of your 670 file ';

  var master = SpreadsheetApp.openById(masterId);
  var masterSheet = master.getSheetByName('list of URLs');
  var file = DriveApp.getFileById(fileId);
  for (var i = 671; i < 8192; i++) { 
    var newFile = file.makeCopy('file'+i);
    var ss = SpreadsheetApp.open(newFile);
    var sheet = ss.getSheets()[0];    // assuming you want the first sheet
    sheet.getRange('A6').setValue(i);
    masterSheet.getRange('L' + (i+1)).setValue(ss.getUrl());
  }
}