Mats Oftedal Mats Oftedal - 8 months ago 53
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();
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.


One way to get only filtered data is through the Binding.getDataAsync method, which takes a filterType parameter."bindings#myTableBinding1").getDataAsync({
    coercionType: "table",
    filterType: "onlyVisible"
    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:

    id: "myTableBinding1"
    // 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