For a genealogy project, I've created a Google sheet where participants enter all details of one ancestor.
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).
(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
Is there a way to automate this?:
- Duplicate file named
- Rename it to
- Change cell
671in the renamed file
- Copy the sheet URL
- Paste the URL in column
671+1in another Google Sheet
And then repeat step 1-5 above until I have 8191 google sheets?