locked
How to fill partially Excel table from Javascript task pane application RRS feed

  • 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. What I need is to fill only the first 7 columns and the table to grow or shrink automatically. I think, that it should be possible, because of the way how Excel (Power) Queries work. But how can I solve this?

    Thanks.

    Thursday, April 7, 2016 12:35 PM

Answers

  • Hi Mottor,

    >>By the way, is there a way to refresh a PowerQuery from JavaScript? 

    No. At present, there is no such way we can operate the PowerQuery using the JavaScript API for Office.

    >>Now if I use your proposal, I have to compare the size with old one. If the new data is smaller I have 
    to shrink the table (Is there a method for this?). If it is bigger I can use setDataAsync + addRowsAsync 
    and copy the formulas from the first row (I do not know them before runtime). Did I understand 
    correctly? Is it possible to start a flash fill from Javascript?

    Yes, you are correct. Based on my understanding, if the data source of the binding table is change outside the Excel(delete, update. add). The fastest way to refresh the table is that delete the table and create a new one because we need to query the data from data source and compare the data. The comparison is time costed. 

    And the Office add-in also doesn't support shrink the table. And if you have any feedback, you can submit from the link below:
    https://officespdev.uservoice.com/

    Regards & Fei


    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.

    • Marked as answer by Mottor Wednesday, May 4, 2016 6:50 AM
    Monday, April 11, 2016 8:10 AM

All replies

  • Hi Mottor,

    Current we are only able to set the data of column which less than the rows of the original table. It will not grow or shrink automatically. 

    As a workaround, we can delete all the data and add the new rows. And we can write the formula using reative reference for the calculate field. For example, there is the code that set the data of table, and the data in second column is same with the first column.

       Office.context.document.bindings.getByIdAsync("table1", function (asyncResult) {
                var data = new Office.TableData();
                data.headers = [["Name","Column2"]]
                data.rows = [["Jack11","=OFFSET(INDIRECT(CELL(\"address\")),,-1)"], ["Jack22","=OFFSET(INDIRECT(CELL(\"address\")),,-1)"]];
               
                asyncResult.value.setDataAsync(data, { coercionType: Office.CoercionType.Table, startColumn: 0 }, function (asyncResult) {
                    if (asyncResult.status=="failed")
                        showMessage(asyncResult.status + ":" + asyncResult.error.message);
                    else
                        showMessage(asyncResult.status + ":" + asyncResult.value);
                });
            })

    And if you have any feedback about Office add-in, you can try to submit the feedback from link below:

    https://officespdev.uservoice.com/

    Regards & Fei


    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.

    Friday, April 8, 2016 3:06 AM
  • Hi Fei,

    thank you very much for your response. What I am actually doing is to make a binding on start with 
    bindings.addFromNamedItemAsync and later use bindings.getByIdAsync to get the binding, deleting
     all rows (binding.deleteAllDataValuesAsync) and add the new ones (binding.addRowsAsync). And
     then use this table as source for PowerQuery. But this method is slow (I have pro query between 
    5000 and 8000 rows). And PowerQuery does not copy the formulas when grows the table. By the way, 
    is there a way to refresh a PowerQuery from JavaScript? 
      Now if I use your proposal, I have to compare the size with old one. If the new data is smaller I have 
    to shrink the table (Is there a method for this?). If it is bigger I can use setDataAsync + addRowsAsync 
    and copy the formulas from the first row (I do not know them before runtime). Did I understand 
    correctly? Is it possible to start a flash fill from Javascript?
    
    
    Best Regards,
    Mottor

    • Edited by Mottor Friday, April 8, 2016 7:46 AM
    Friday, April 8, 2016 7:45 AM
  • Hi Mottor,

    >>By the way, is there a way to refresh a PowerQuery from JavaScript? 

    No. At present, there is no such way we can operate the PowerQuery using the JavaScript API for Office.

    >>Now if I use your proposal, I have to compare the size with old one. If the new data is smaller I have 
    to shrink the table (Is there a method for this?). If it is bigger I can use setDataAsync + addRowsAsync 
    and copy the formulas from the first row (I do not know them before runtime). Did I understand 
    correctly? Is it possible to start a flash fill from Javascript?

    Yes, you are correct. Based on my understanding, if the data source of the binding table is change outside the Excel(delete, update. add). The fastest way to refresh the table is that delete the table and create a new one because we need to query the data from data source and compare the data. The comparison is time costed. 

    And the Office add-in also doesn't support shrink the table. And if you have any feedback, you can submit from the link below:
    https://officespdev.uservoice.com/

    Regards & Fei


    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.

    • Marked as answer by Mottor Wednesday, May 4, 2016 6:50 AM
    Monday, April 11, 2016 8:10 AM