I use this loop to create or duplicate to new sheets from existing sheet Main
var ss = SpreadsheetApp.getActiveSpreadsheet()
for (var sheet = 1; sheet <= 5; sheet++) {
ss.getSheetByName("Main").copyTo(ss).setName(sheet)
}
and I also fill formula sheetname() in cell A1, this formula to those sheets which it shows "Copy of Main" it's not correct sheet name as number "1 to 5" as above (actually the numbers change to be my wish)
function sheetname() {
return SpreadsheetApp.getActiveSpreadsheet().getSheetName()
}
When I manually refill sheetname(), it works just fine. But when I use script loop, copy to and then use custom formula. It's not work properly or it's kind of not auto update.
Is there any ways to get this work, when I don't need to refill its manually? Or can I do to set value of sheet name in the loop "for" is it possible?
You can meet with the best article about this Automatic Recalculation of Custom Function on Spreadsheet Part 1, Automatic Recalculation of Custom Function on Spreadsheet Part 2
For your situation the next code will be fine
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var sheet = 1; sheet <= 5; sheet++)
ss.getSheetByName("Main").copyTo(ss).setName(sheet);
var temp = Utilities.getUuid();
ss.createTextFinder("=").matchFormulaText(true).replaceAllWith(temp);
ss.createTextFinder(temp).matchFormulaText(true).replaceAllWith("=");