exceloffice-scripts

I need to change also a cell with the same value as the sheet name excel


I wrote this code to create sheets taking the name from a range, i need to also change a specific cell in the new sheet with the same name of the created sheet

    function main(wb: ExcelScript.Workbook) {
        // Define constants.
        const LOOKUP_SHEET_NAME: string = "JP PPS";
        const LOOKUP_RANGE_ADDRESS: string = "P4:P12";
        const TEMPLATE_SHEET_NAME: string = "Template";
        // Return the values of the lookup range in an array.
        let lws = wb.getWorksheet(LOOKUP_SHEET_NAME);
        let lrg = lws.getRange(LOOKUP_RANGE_ADDRESS);
        let lData = lrg.getValues();
        // Referemce the template.
        let tws = wb.getWorksheet(TEMPLATE_SHEET_NAME);
        // Use each value in the array as a name for a copy
        // of the template created after the last worksheet.
        for (let i = 0; i < lData.length; i++) {
            let nws = tws.copy(ExcelScript.WorksheetPositionType //
                .after, wb.getLastWorksheet());
            let nwsName: string = lData[i].toString();
            nws.setName(nwsName);
        };
    }

Solution

  • function main(wb: ExcelScript.Workbook) {
        // Define constants.
        const LOOKUP_SHEET_NAME: string = "JP PPS";
        const LOOKUP_RANGE_ADDRESS: string = "P4:P12";
        const TEMPLATE_SHEET_NAME: string = "Template";
        // Return the values of the lookup range in an array.
        let lws = wb.getWorksheet(LOOKUP_SHEET_NAME);
        let lrg = lws.getRange(LOOKUP_RANGE_ADDRESS);
        let lData = lrg.getTexts();
        // Referemce the template.
        let tws = wb.getWorksheet(TEMPLATE_SHEET_NAME);
        // get the list of all sheets' name
        const sheetsList = wb.getWorksheets().map(sht => sht.getName());
        // Use each value in the array as a name for a copy
        // of the template created after the last worksheet.
        const TARGET_REF: string = "A1" // modify as needed
        for (let i = 0; i < lData.length; i++) {
            let nwsName: string = lData[i][0];
            if (nwsName.length) {  // skip blank cell
                if (sheetsList.includes(nwsName)) { // remove existing sheet
                    wb.getWorksheet(nwsName).delete();
                }
                let nws = tws.copy(ExcelScript.WorksheetPositionType.after
                    , wb.getLastWorksheet());
                nws.setName(nwsName);
                nws.getRange(TARGET_REF).setValue(nwsName); // write sheet name to A1
            }
        };
    }
    

    Microsoft documentation:

    ExcelScript.Range interface getText()