Split Data into Separate Worksheets Using ChatGPT to Write Office Script

In this Microsoft Excel video tutorial I use ChatGPT to write an Office Script that splits data (currently held in a single worksheet) into multiple worksheets based on a column value.

Download the featured file here.

Here’s the code that ChatGPT came up with:

function main(workbook: ExcelScript.Workbook) {
    const worksheet = workbook.getActiveWorksheet();
    const usedRange = worksheet.getUsedRange();
    const data: (string | number)[][] = usedRange.getValues();
    const headerRow: (string | number)[] = data[0];
    const dataRows: (string | number)[][] = data.slice(1);

    const dataByGroup = dataRows.reduce((map: Map<string | number, (string | number)[][]>, row: (string | number)[]) => {
        const group = row[2]; // Change this to match the column you are grouping by (zero-indexed)
        if (!map.has(group)) {
            map.set(group, [headerRow, row]);
        } else {
            map.get(group).push(row);
        }
        return map;
    }, new Map());

    dataByGroup.forEach((rows: (string | number)[][], group: string | number) => {
        let newWorksheet = workbook.getWorksheet(String(group));
        if (!newWorksheet) {
            newWorksheet = workbook.addWorksheet(String(group));
        }
        newWorksheet.getRangeByIndexes(0, 0, rows.length, rows[0].length).setValues(rows);
    });
}