exceloffice-scripts

Duplicate and rename sheets by script


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;
}

Solution

  • 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);
                }
            }
            )
        }
    
    }