ExcelScript Get Rows in Array from Filter

I have an ExcelScript that I want to filter a Column by the State and get all the rows in an array that match that State. This is what I have so far: let selectedSheet = workbook.getActiveWorkshee...

Office Scripts

mathetes
Jul 19, 2023
smithmrk

I can't speak to your VBA/macro question. However, what you've described sounds as if the FILTER function itself might work. If you're not aware of that function--which has only been around for a few years--here's a YouTube video created by Microsoft to introduce it. Worth watching.

SergeiBaklan

Diamond Contributor

Jul 19, 2023

smithmrk

As variant

function main(workbook: ExcelScript.Workbook) {

    let filteredRows: (number | string | boolean)[][] = [];
    const tableName = "Table1"
    const filterValue = "CA"
    const filterBy = "CUSTOMER_STATE";

    const table = workbook.getTable(tableName)
    const index = table
        .getHeaderRowRange()
        .getValues()[0]
        .indexOf(filterBy)

    const dataRows = table
        .getRangeBetweenHeaderAndTotal()

    const nRows = dataRows.getRowCount()

    for (let i = 0; i < nRows; i++) {
        const values = dataRows.getRow(i).getValues()[0];
        if (values[index] === filterValue) {
            filteredRows.push(values)
        }
    }

    console.log(filteredRows)

}

depend on what you are going to do with that array of arrays (filtered rows x columns) - perform some aggregation, copy to another table, etc.

smithmrk

Copper Contributor

Jul 19, 2023

Thanks!
I appreciate it.

SergeiBaklan
Diamond Contributor
Jul 19, 2023
smithmrk , you are welcome

Forum Discussion

ExcelScript Get Rows in Array from Filter

Resources