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)

Problem

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.

You will have to anyway loop through the array to retrieve the values of col c1 and c2. No ? — Raky
– Raky, Commented Oct 3, 2023 at 4:05

taller · Accepted Answer · 2023-10-03 23:38:54Z

2

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

edited Oct 3, 2023 at 23:38

answered Oct 3, 2023 at 3:46

taller

19.7k2 gold badges9 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EdX Over a year ago

This is I got when I ran the codes: Line 4: Cannot read properties of undefined (reading 'getFilter')

taller Over a year ago

I have updated the code to handle the exception.

EdX Over a year ago

It works now. Thank you so much. So the answer is using .getSpecialCells()

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related