Duplicate and rename sheets by script

Question

I work with excel 365 online. I am trying to duplicate a certain number of times a template sheet that I then have to rename by the value of a cell.

I have tried and tested several codes but currently, it returns an error on the loop and I have a problem renaming the created copy (which I think is linked to the fact that I do not activate the right tab). I am having trouble finding documentation on excel scripts so I am a little lost.

A little help would be appreciated!

function main(workbook: ExcelScript.Workbook) {
    let modele = workbook.getWorksheet("Semaine 36");

    for (i = 1 to 5 );
    // Duplicate worksheet
    let modele_copie = modele.copy(ExcelScript.WorksheetPositionType.after, modele);
    let sheet = workbook.getActiveWorksheet();
    const cellule = sheet.getRange('A3');
    const valeur = cellule.getValue();
    sheet.setName(valeur) 
    next i;
}

Answer 1

• Consider incorporating exception handling in the code, for example: if a workbook contains a sheet with the same name (below script will delete existing sheets with the same name), and if the sheet name list contains empty cells (skip those cells) etc.

• The script dynamically locate the work sheet name list on Sheet1.

function main(workbook: ExcelScript.Workbook) {
    const startRow = 3; // row# of the first sht name
    const semSht = workbook.getWorksheet("Semaine 36"); // get worksheet object
    const wsSht1 = workbook.getWorksheet("Sheet1")
    // get row# of the last name on Sheet1, index is zero-base
    const lastRow = wsSht1.getRange("A:A").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
    if (lastRow >= startRow) {
        // get all sht names
        const shtNames = wsSht1.getRange(`A${startRow}:A${lastRow}`).getTexts();
        // loop through each name
        shtNames.forEach(sName => {
            let shtName = sName[0]; // get the sht name
            if (shtName) { 
                // try to get the sht object
                let newSht = workbook.getWorksheet(shtName);
                if (newSht) { // remove sht if exists
                    newSht.delete();
                }
                // copy and rename template sht
                semSht.copy(ExcelScript.WorksheetPositionType.after, workbook.getLastWorksheet()).setName(shtName);
            }
        }
        )
    }

}