How to fill partially Excel table from Javascript task pane application

Question

I have an Excel table with 7 columns with data and 6 calculated columns. The table is connected with 3 charts.

A task pane add-in is used to get some parameters, implement some security (Bearer Token) and get the data. The query returns different number of rows for different parameters.

Because of this, I cannot use a range and have to use Office.TableData.

On samples like this the table is destroyed every time and created again, which I cannot use, because the Excel workbook is used as template, and I do not know, what for connection this table has.

With bindings and TableData is possible to use different number of rows, but not to update the data partially.

These are the methods I have tried without success: addFromNamedItemAsync, deleteAllDataValuesAsync, addRowsAsync, setSelectedDataAsync.

I think, that it should be possible, because of the way how Excel Workbook Queries work. But how can I solve this?

Answer 1

Take a look at the latest version of the Excel JavaScript APIs (available in Office 2016 and Office365), which have more methods to use with tables: https://dev.office.com/reference/add-ins/excel/table

On the table, you could get the range and then get cells using offsets, for example: var range = table.getRange().getCell(1,1); There is also the TableRowCollection that allows you to retrieve specific rows and again, you could get subsets of it using getCell, or a combination of getCell along with getIntersection if you need to get a specific subrange.

Answer 2

Though i dont have answer to your question but if you are not able to find answer to question related to office-add-in you should ask that question on social msdn forum. I got my answer from there. You can check my asked question related to office-add-in