Skip this paragraph, if you do not care about the background and immediately want to see the coding question. I am pretty new to coding and this is my first question I post here, but I hope I can make some sense. I searched the web thoroughly, but I cannot find an answer to my probably not so special problem. Some background: I work as a coach and have google spreadsheets with training plans that I give to clients. Since I love coding and google spreadsheets, the statistics and functionality I programmed quickly developed, so much, I had to split the spreadsheet and its up to 7 training day tabs/sheets in different spreadsheets, so I save my clients or rather their smartphones some working memory. So far so good.
I programmed an apps script that duplicates the master/setup sheet as well as the training days spreadsheets (#1 to #7) and customizes the folder they are saved in as well as the copies names with the clients nickname/first name.
Today I also managed to successfully replace the importranges in the calender sheet (background: so I can see when my clients trained which day) of my master/setup spreadsheet, so that the copy of the master spreadsheet, which I just duplicated, does not link to the training day #1, #2, ... template, but the copy of the training day #1,#2 ... template. I do not know how comprehensible this is (also english is not my first language) so I try to rephrase it: Of course the IMPORTRANGE formulas that interconnect all the template spreadsheets with each other do not update when duplicating, but still refer to the template spreadsheets.
However, I asked myself why hardcode everything (the actual cell ranges and stuff) and if there is not an easy possibility to always substitute the id part of all importrange functions (of template #1 for example) with the id of the sheet (which I already stored in an array). Do you understand what I mean and could you help me with this? Here is what I already coded so far.
Thanks to anyone who read this question and trys to help! PS: Also I am happy if someone wants to correct or simplify my current code, but I am happy that it works so far and I would be overwhelmed if someone could push me in the right direction of how to achieve this. PPS: The reason why I am leaning in the direction of a GAS version of find and replace all importrange functions would be, that I would keep some flexibility in my spreadsheet template architecture and I would be able to edit the template in the future without thinking about hardcoded ranges in the GAS code, that I might destroy.
Tl;Dr: Is there an easy way to use find and replace via GAS to just change the id part of the importrange formulas after duplicating spreadsheets?
//function that creates a new menu in the spreadsheet just right of the "help" tab
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Scripts')
.addItem('Create Duplicates', 'createDuplicates')
.addItem('Create Links in Master Spreadsheet', 'createLinksInMasterSheet')
.addToUi();
}
const parentFolder = DriveApp.getFolderById("ID OF PARENT FOLDER");
const nameOfMasterSheet = "TrnngPln";
const currentVersion = "4.0";
//function that creates duplicates of master sheet, all training days in a new folder that is named after the clients nickname
function createDuplicates() {
const clientNickname = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SETUP").getRange('AB2').getValue();
const currentmonth = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InfoForAppsScript").getRange('F2').getValue();
const currentyear = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InfoForAppsScript").getRange('G2').getValue();
const folderNewClient = parentFolder.createFolder('TP.'+clientNickname+'.['+currentmonth+'/'+currentyear+']');
const newMasterSpreadSheetName = ''+clientNickname+'.'+nameOfMasterSheet+'['+currentmonth+'/'+currentyear+'.]'+currentVersion+'';
const setup = DriveApp.getFileById("ID OF SETUP TEMPLATE");
var newMasterID = setup.makeCopy(newMasterSpreadSheetName,folderNewClient).getId();
const nameOfCopyOfDay1 = '#1.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day1 = DriveApp.getFileById("ID OF TEMPLATE DAY 1");
var newDay1id = day1.makeCopy(nameOfCopyOfDay1,folderNewClient).getId();
const nameOfCopyOfDay2 = '#2.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day2 = DriveApp.getFileById("ID OF TEMPLATE DAY 2");
var newDay2id = day2.makeCopy(nameOfCopyOfDay2,folderNewClient).getId();
const nameOfCopyOfDay3 = '#3.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day3 = DriveApp.getFileById("ID OF TEMPLATE DAY 3");
var newDay3id = day3.makeCopy(nameOfCopyOfDay3,folderNewClient).getId();
const nameOfCopyOfDay4 = '#4.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day4 = DriveApp.getFileById("ID OF TEMPLATE DAY 4");
var newDay4id = day4.makeCopy(nameOfCopyOfDay4,folderNewClient).getId();
const nameOfCopyOfDay5 = '#5.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day5 = DriveApp.getFileById("ID OF TEMPLATE DAY 5");
var newDay5id = day5.makeCopy(nameOfCopyOfDay5,folderNewClient).getId();
const nameOfCopyOfDay6 = '#6.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day6 = DriveApp.getFileById("ID OF TEMPLATE DAY 6");
var newDay6id = day6.makeCopy(nameOfCopyOfDay6,folderNewClient).getId();
const nameOfCopyOfDay7 = '#7.'+clientNickname+'['+currentmonth+'/'+currentyear+']'+currentVersion+'';
const day7 = DriveApp.getFileById("ID OF TEMPLATE DAY 7");
var newDay7id = day7.makeCopy(nameOfCopyOfDay7,folderNewClient).getId();
}
//function that sets Links in Calendar tab of master trainingplan spreadsheet
function createLinksInMasterSheet() {
var searchFor = ['title contains ".TrnngPln"','title contains "#1."','title contains "#2."','title contains "#3."','title contains "#4."','title contains "#5."','title contains "#6."','title contains "#7."'];
var ss = SpreadsheetApp.getActive(); //current spreadsheet
var directParents = DriveApp.getFileById(ss.getId()).getParents(); // folderIterator "targets" parent folder
var folder = directParents.next(); //accesses parent folder
var names =[]; //array that stores names of the spreadsheets found in folder in the order of the array variable searchFor
var fileIds=[]; //array that stores the IDs of the spreadsheets found in folder in the order of the array variable searchFor
for (var i=0; i<searchFor.length; i++) { //forLoop that goes through the searchFor array
var files = folder.searchFiles(searchFor[i]); //creates file iterator t
while (files.hasNext()) { //while loop goes through all the files that match searchFor variable
var file = files.next(); //
var fileId = file.getId(); // To get FileId of the file
fileIds.push(fileId); //
var name = file.getName(); //To get name of the file
names.push(name); //
}
}
for (var i=1; i<fileIds.length;i++) {
const formula = 'IMPORTRANGE("'+fileIds[i]+'";"#'+i+'!JJ188:JX188")'; //tak
var rownumber = 265+i;
Logger.log(rownumber);
ss.getSheetByName('Cal').getRange('C'+rownumber+'').setFormula(formula);
Logger.log(formula);
}
}
PS: Most of this is not my code but edited code I found somewhere on this board, youtube or wherever and edited it to fit my purpose.
From the question
Tl;Dr: Is there an easy way to use find and replace via GAS to just change the id part of the importrange formulas after duplicating spreadsheets?
There are several ways to replace the first argument (id / key / url) on IMPORTRANGE functions by using Google Apps Script. Perhaps the easier is to use Class TextFinder as it has several methods that might be helpful like: