Forum Discussion

smithmrk's avatar
smithmrk
Copper Contributor
Jul 19, 2023
Solved

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.getActiveWorksheet();
  let colValues[] = selectedSheet.getTable(tableName).getColumn("CUSTOMER_STATE").getRange().getValues().filter(x => x[0] == "CA");​

However, once it filters out all the Califorina Rows...how do I get all those rows into an array so I can loop through that array of items?

 

Thanks,
Mark

 

Office Scripts
  • mathetes's avatar
    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's avatar
    SergeiBaklan
    MVP
    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.

  • mathetes's avatar
    mathetes
    Silver Contributor
    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.

Resources