Mats Oftedal Mats Oftedal - 1 month ago 14
Javascript Question

Office-JS API: Fetching filtered data from table

I am trying to figure out a way to fetch only the filtered values from a table if a filter is active in Office-JS API.

Right now the only way I have figured to fetch all the table data is from the table range values property:

var table = tables.getItemAt(0);
var tableRange = table.getRange();
tableRange.load("values");
ctx.sync().then(function () {
// This returns all the values from the table, and not only the visible data
var values = tableRange.values;
});


Any ideas on how I can proceed to fetch only the visible values from the table if a filter is active?

From previous experience with Office Interop I have achieved the same by looping through the different Areas of the table range, but I am unable to find the equivalent to Areas in Office-JS.

Answer

One way to get only filtered data is through the Binding.getDataAsync method, which takes a filterType parameter.

Office.select("bindings#myTableBinding1").getDataAsync({
    coercionType: "table",
    filterType: "onlyVisible"
},function(asyncResult){
    var values = (asyncResult.value.rows);
});

This code assumes you have already created a binding to the table. If not, you can run the following code first, which uses the table name to call Bindings.addFromNamedItemAsync:

Office.context.document.bindings.addFromNamedItemAsync("Table1","table",{
    id: "myTableBinding1"
},function(asyncResult){
    // handle errors and call code sample #1
});

Note that the solution above is supported as far back as Excel 2013 because it uses the shared APIs. The Excel-specific API set doesn't yet have the capability to return only unfiltered data.

-Michael Saunders, PM for Office add-ins

Comments