Office script to filter column

[jsmith2094]

HI,

not sure if this is the correct place to post.

I need an office script to use with power automate to filter a column called "Ref" and to only include anything that starts with IM or AF.

when I use the recorder it does not do it dynamically

can anyone help ?

here is the code:

function main(workbook: ExcelScript.Workbook) {
let tABLE1 = workbook.getTable("Table1");
// Apply checked items filter on table tABLE1 column Reference Num
tABLE1.getColumnByName("Reference Num").getFilter().applyValuesFilter(["AF-123456", "IM-123456"]);
}

 

[b_widg]

I haven't found a way to do this with a filter method, but this might do in a pinch. It loops through the column and hides rows that meet the criteria. This will hide instances of lower case "AF" and "IM" as well. If you want that just delete the two instances of ".toUpperCaSE()" IN LINE 10.

function main(workbook: ExcelScript.Workbook) {
    let table1 = workbook.getTable("Table1");

    let refColumn = table1.getColumnByName("Reference Num").getRangeBetweenHeaderAndTotal();

    let rowCount = refColumn.getRowCount();

    for (let i = rowCount -1; i >= 0; i--) {
        let cellVal = refColumn.getTexts()[i][0];
        if (cellVal.substring(0, 2).toUpperCase() === "AF" || cellVal.substring(0, 2).toUpperCase() === "IM"){
            refColumn.getCell(i,0).setRowHidden(true);
        }
    }
}

 

[b_widg]

I haven't found a way to do this with a filter method, but this might do in a pinch. It loops through the column and hides rows that meet the criteria. This will hide instances of lower case "AF" and "IM" as well. If you want that just delete the two instances of ".toUpperCaSE()" IN LINE 10.

function main(workbook: ExcelScript.Workbook) {
    let table1 = workbook.getTable("Table1");

    let refColumn = table1.getColumnByName("Reference Num").getRangeBetweenHeaderAndTotal();

    let rowCount = refColumn.getRowCount();

    for (let i = rowCount -1; i >= 0; i--) {
        let cellVal = refColumn.getTexts()[i][0];
        if (cellVal.substring(0, 2).toUpperCase() === "AF" || cellVal.substring(0, 2).toUpperCase() === "IM"){
            refColumn.getCell(i,0).setRowHidden(true);
        }
    }
}

...if you DON'T want that just delete the two instances of ".toUpperCaSE()" in line 10. Sorry about that!

 

[b_widg]

...if you DON'T want that just delete the two instances of ".toUpperCaSE()" in line 10. Sorry about that!

I'm sorry, you wanted to filter for those, right? This will get you closer.

function main(workbook: ExcelScript.Workbook) {
    let table1 = workbook.getTable("Table1");

    let refColumn = table1.getColumnByName("Reference Num").getRangeBetweenHeaderAndTotal();

    let rowCount = refColumn.getRowCount();

    for (let i = rowCount -1; i >= 0; i--) {
        let cellVal = refColumn.getTexts()[i][0];
        if (cellVal.substring(0, 2).toUpperCase() !== "AF" && cellVal.substring(0, 2).toUpperCase() !== "IM"){
            refColumn.getCell(i,0).setRowHidden(true);
        }
    }
}