03-14-2022 06:24 AM - last edited 03-14-2022 06:33 AM
The flow allows the user to send bulk emails with a dynamic html template. With the addition of a 2 email test run to validate before approval to send all emails
Setup
The Flow completes the followin steps
function main(workbook: ExcelScript.Workbook) {
let wb1 = workbook.getWorksheet("Sheet1");
let wb2 = workbook.addWorksheet();
let i = 1;
let r=1;
let c=3;
let rowCount = wb1.getUsedRange().getRowCount();
let colCount = wb1.getUsedRange().getColumnCount();
console.log(rowCount+" "+colCount);
let headersMain =[["Email","Subject"]]
wb1.getRange("A1:B1").setValue(headersMain);
let headers=[["ID","Email","Change","With"]]
wb2.getRange("A1:D1").setValue(headers);
for (r=1;r <rowCount;r++){
for(c=2;c<colCount;c++){
wb2.getCell(i, 0).setValue(r);
wb2.getCell(i, 1).setValue(wb1.getCell(r, 0).getValue());
wb2.getCell(i, 2).setValue(wb1.getCell(0, c).getValue());
wb2.getCell(i, 3).setValue(wb1.getCell(r, c).getValue());
i++;
}
}
wb1.getRange("A:A").insert(ExcelScript.InsertShiftDirection.right);
wb1.getRange("A1").setValue("mainID");
wb1.getRange("A2").setFormulaLocal("=row(A1)");
wb1.getRange("A2").autoFill();
let table1 = workbook.addTable(wb1.getRange("A1:F"+rowCount), true);
table1.setName("mainTable");
let table2 =workbook.addTable(wb2.getRange("A1:D" + i), true);
table2.setName("PivotTable");
wb2.setName("Pivot");
return(rowCount-1);
}
replace(variables('sEmailbody'), items('Apply_to_each_3')?['Change'], items('Apply_to_each_3')?['with'])
Thanks for sharing! This is a very useful solution 👍!
Here is a minor performance suggestion to the code around the for/loop block - instead of calling getCell, setValue, getValue frequently inside the loops, you can also try using getValues first to get all the cell values into a two-dimensional string array, then iterate through it and build up another two-dimensional string array based on your current pivoting logic, and finally call setValues to set the second array into the target range in a single shot. This sometimes can greatly improve the script performance especially for large worksheet that contains many rows and columns.
Hi Yutao,
Great feedback, didn't think of looping of over an array instead of a range, would definitely I prove performance and an all round nicer solution