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?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,555 questions
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,599 questions
{count} votes