Using Office Scripts to Split Worksheet By Column Value

LisaCarpenter-5571 0 Reputation points
2024-05-20T13:34:18.0066667+00:00

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?

Microsoft 365 and Office Development Other
Microsoft 365 and Office Excel For business Windows
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.