How to get filtered range from Table's Auto Filter in Excel's Office Script?

Question

Sorry, I am new to Office Scripts but have asked ChatGPT, Bing Chat and done lots of Google searches without much luck. I thought it should be something very simple for Microsoft to provide. I must have missed something very crucial or it is a bug in Office Script or Microsoft just tried to make it super hard to use this filtering feature.

Problem: Access the filtered range of a Table from Office Script (see image attachment)

table1.getRange() => is there a way to captured the filtered range of a Table?

Of course, I can solve it by simply looping through all the records and manually filtering the records. However, it is very inefficient to do it this way as I am dealing with a large number of records and multiple loops.

I was hoping there was a solution to allow me to filter out several columns in a table and retrieve the filtered rows easily from Office Scripts.

Answer 1

• getSpecialCells(...) is utilized to obtain the visible range within a filtered table.

Options for retrieving columns in a filtered table:

• Iterate through all areas within the visible range (as filtered tables may have discontinuous ranges) and loop through all cells within each area range.
• Acquire the visible range of the desired column and iterate through its cells.

function main(workbook: ExcelScript.Workbook) {
    let table1 = workbook.getTable("Table3");
    // Enable AutoFilter
    let isFilterON = table1.getAutoFilter().getEnabled();
    if(!isFilterON) {
        table1.getAutoFilter().apply(table1.getRange());
    }
    // Apply checked items filter on table table2 column C1
    let tabFilter = table1.getColumnByName("C1").getFilter();
    let tableRange = table1.getRange();
    // Get the visible range of table w/ header
    let tabVisibleRange = tableRange
        .getSpecialCells(ExcelScript.SpecialCellType.visible);
    for (let i = 0; i < tabVisibleRange.getAreaCount(); i++) {
        let areaRange = tabVisibleRange.getAreas()[i];
        console.log(`Area_${i} range: ${areaRange.getAddress()}`)
    }
    // Get all data in column C2 
    let c2List: string[] = [];
    for (let i = 0; i < tabVisibleRange.getAreaCount(); i++) {
        let areaRange = tabVisibleRange.getAreas()[i];
        let rowCount = areaRange.getRowCount();
        for (let r = 0; r < rowCount; r++) {
            c2List.push(areaRange.getCell(r, 1).getText());
        }
    }
    console.log(c2List.join(","))
    // Get the visible range of column C2 w/o header
    let colVisibleRange = table1.getColumnByName("C2")
        .getRangeBetweenHeaderAndTotal()
        .getSpecialCells(ExcelScript.SpecialCellType.visible);
    console.log(colVisibleRange.getAddress())
}

Output:

Area_0 range: Sheet2!A1:C3
Area_1 range: Sheet2!A5:C5
Area_2 range: Sheet2!A7:C7
C2,Cat,Dog,Pig,Mouse                << data in column C2
Sheet2!B2:B3,Sheet2!B5,Sheet2!B7    << visible range in column C2