Using Office Scripts to Split Worksheet By Column Value
LisaCarpenter-5571
0
Reputation points
I first asked this question on the microsoft community but was redirected here.
I'm trying to split a worksheet containing data by the values in the column of that data. I tried following this video and got the script below (the download didn't work, so this could be a simple copy error).
function main(workbook: ExcelScript.Workbook) {
const worksheet = workbook.getActiveWorksheet();
const usedRange = worksheet.getUsedRange();
const data:(string | number | boolean)[][] = usedRange.getValues();
const headerRow:(string |number | boolean)[] = data[0];
const dataRows:(string | number| boolean)[][] = data.slice(1);
const dataByGroup = dataRows.reduce((map: Map<string | number |boolean,(string|number|boolean)[][]>,row:(string | number|boolean)[])=>{
const group = row[3];
if (map.has(group)){
map.set(group,[headerRow,row]);
}else{
map.get(group).push(row)
}
return map;
}, new Map());
dataByGroup.forEach((rows,group)=>{
let newWorksheet = workbook.getWorksheet(group);
if(!newWorksheet){
newWorksheet = workbook.addWorksheet(group);
}
newWorksheet.getRangeByIndexes(0,0,rows.length,rows[0].length).setValues(rows);
})};
I had to update the data types to include boolean, but otherwise it should be the same as the final version in that video.
I'm getting the error message: "Line 13: Cannot read properties of undefined (reading 'push')" when I try to run it.
Am I doing something wrong? Is there a better approach?