Excel Office JS filter data

Question

In Excel a user can select a range and hit Ctrl+Shift+L to show filters. I am trying to get equivalent behavior from an Office.js Add-in.

The closest I have come to that is adding a table over the range I want to filter and then adding a filter to the table. There seem to be a couple of significant problems with that however.

First, adding a table this way for 30000+ rows is very slow and I am frequently using tables much larger than that. If I do Ctrl+Shift+L over a range that size it is instantaneous.

Additionally, when I add the table, Office.js stylizes the range. I do not want any new styling for the range I just want a filter added.

My current code looks like this:

await Excel.run(async ctx => {
    const table = await getOrCreateDataTable(ctx, "CostData", new ExcelRange(this.stateService.headerRow)); //see below
    const validationColumn: Excel.TableColumn = table.columns.getItemOrNullObject("Validation");
    validationColumn.filter.applyCustomFilter(`*${searchString}*`)
    await ctx.sync();
});

export const getOrCreateDataTable = async(ctx: Excel.RequestContext, tableName: string, headerRow: ExcelRange): Promise < Excel.Table > => {

    let table: Excel.Table = ctx.workbook.tables.getItemOrNullObject(tableName)
    await ctx.sync();
    if (!table.isNullObject)
        console.log(`Table: ${tableName} found`)
    else {
        const sheet = await getSheet(ctx, headerRow.sheet)
        const headerRange = sheet.getRange(headerRow.getRange()).getEntireRow().getUsedRange()
        const usedRange: Excel.Range = sheet.getUsedRange()
        const tableRange = headerRange.getBoundingRect(usedRange.getLastCell())
        table = ctx.workbook.tables.add(tableRange, true)
        table.name = tableName
        await ctx.sync();

    }
    return table;
}

Answer 1

You can now add filters to ranges as of Excel Javascript API 1.9: https://docs.microsoft.com/en-us/office/dev/add-ins/reference/requirement-sets/excel-api-1-9-requirement-set

Here's an example to apply a custom filter which will find cells with the substring "test":

Excel.run((ctx) => {
    var sheet = ctx.workbook.worksheets.getActiveWorksheet();
    var range = sheet.getUsedRange();
    var columnIndex = 3; //zero based index
    var condition = { criterion1: "=*test*", filterOn: Excel.FilterOn.custom }        

    sheet.autoFilter.apply(range, columnIndex, condition);

    return ctx.sync();
}).catch(errorHandlerFunction);

Remove filters with:

sheet.autoFilter.remove();

Find more details here:

https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-worksheets#filter-data

Or in script lab: https://www.microsoft.com/en-us/garage/profiles/script-lab/

Answer 2

Currently ExcelApi only supports filtering on a Table object.

What you're looking for here would be support for filtering a Range. I would highly recommending visiting the UserVoice and this suggestion.

Answer 3

Mashing up @cs_pupil answer and MS Docs I came up w/ this function.

Note: The first thing I noticed after getting the MS version to work was that it didn't work correctly w/ Wildcards which is what I use mainly, so I switched to custom filtering vs values

https://docs.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-tables#apply-filters-to-a-table

function Do_FilterTable(TBLObj, ColHeader, criterion1Str) {
    TBLObj.columns.getItem(ColHeader).filter.apply({
        filterOn: Excel.FilterOn.custom,
        criterion1: criterion1Str
    }); 
}