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