locked
Excel API - is there a way to make table.getRange() return just the filtered (in) rows of a table? RRS feed

  • Question

  • If "myTable" below is in a sheet and it contains 100 recs but the user has applied filters so that only 25 rows are visible in the table, can you use table.getRange() to return only the filtered range of 25 visible rows?

    I know about Office.context.document.getSelectedDataAsync (filterType: Office.FilterType.OnlyVisible ...) but I need to use the Excel API to return an Excel.Range object.

    Excel.run(function (ctx)
    {
       var table = ctx.workbook.tables.getItem("myTable");
       var tableRange = table.getRange();
       tableRange.load('...');
     
       return ctx.sync().then(function ()
       {
          console.log(tableRange.address);
       });
    }).catch(function (error)
    {
       console.log("Error: " + error);
       if (error instanceof OfficeExtension.Error)
       {
          console.log("Debug info: " + JSON.stringify(error.debugInfo));
       }
    });

    Thanks,

    Jim

    Tuesday, May 17, 2016 6:26 PM

All replies

  • Hi Jim,

    I have checked the related object like table and range, but I failed to find such a method or property to achieve your requirement. I think it is impossible at present.

    I suggest you submit a feedback in the link below:

    http://officespdev.uservoice.com

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, May 18, 2016 2:21 AM
  • Thanks Edward,

    That's too bad, seems like it would be standard in the Excel API.

    I have opened user voice below but I'd like to keep this post open for a while in case someone has a solution or workaround (other than getSelectedDataAsync (filterType: Office.FilterType.OnlyVisible ...)).

    https://officespdev.uservoice.com/forums/224641-general/suggestions/13887417-excel-api-enhance-table-getrange-to-return-fil

    Thanks,

    Jim

    Wednesday, May 18, 2016 12:36 PM
  • Hi Jim,

    Thanks for your voice, and I agree with you to keep this post open for a while. If you find any solution or workaround, it would be appreciated if you could share us.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, May 19, 2016 2:34 AM