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);
};
}
lData
is a 2D array, where lData[i]
represents a sub-array, and lData[i][0]
is an individual element within the 2D array.
Retrieve the value as a String
using lrg.getTexts()
, eliminating the need for .toString()
in your script.
It is highly recommended to check if a sheet with the desired name already exists; otherwise, the line of code that renames the sheet after creation will cause an error.
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: