App For Office Updating multiple Rows by Dragging and Get those value. RRS feed

  • Question

  • Please help me out i stuck in this problem.. Please Check for Image attached to this for reference to my issues.

    I have selected 1 row and updated other 20 rows by dragging that row, but while trying to get updated rows i m not able to those rows, it gives me old values, Please help me out.....?

    Thanks in Advance for time n help....:)

    • Edited by Amit Dhemar Monday, April 29, 2013 12:20 PM
    Monday, April 29, 2013 5:37 AM

All replies

  • I have used App for Office JS API to do the work but when I am trying to Update 20 rows from App Excel by dragging single row and make change to next 20 rows, these 20 rows are updated in Front end but when I am trying to retrieve these updated rows using JS API it gives me the Old values only though in front end it shows updated one....... I am trying to get new values on SelectionChange and OnDataChange but both of these methods returns me old values

    Office.select("bindings#amitamit").getDataAsync({ coercionType: 'table', startRow: varCurrentRow, startCol: varCurrentColumn, rowCount: 1, colCount: 1 }, function (result) { if (result.status == 'succeeded') { // write("Image to find: " + result.value.rows[0][varCurrentColumn]); //varCurrentValue = result.value.rows[0][varCurrentColumn]; document.getElementById('divNewValue').innerText = result.value.rows[0][varCurrentColumn]; if (result.value.rows[0][0].length <= 0) varUpdatedMasterIDs += "¶-1"; else varUpdatedMasterIDs += "¶" + result.value.rows[0][0]; document.getElementById('divMasterIDs').innerText = varUpdatedMasterIDs; } else write(result.error.message); });

    • Edited by Amit Dhemar Monday, April 29, 2013 12:19 PM
    Monday, April 29, 2013 7:16 AM
  • Hi Amit,

    You referenced an image in your post but I am not seeing one so bear with me if some of these questions are not clear.

    As I understand it, you have written an App for Office using JavaScript.
    You have attached code to the SelectionChange and OnDataChange events to retrieve updated data.
    In Excel you are updating rows by dragging one row down.
    Unfortunately your code is returning the old information not the updated information.

    If you just type new data into a cell in Excel to you get the old or updated information?

    If you call the same code in your SelectionChange and OnDataChange events directly (rather having it triggered by the event) do you see the same information displayed on the screen?

    If you follow the same process directly in Excel using VBA to you get the same results?

    Best Regards,

    Donald M.
    Microsoft Online Community Support
    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, May 1, 2013 2:59 PM
  • Hi Donald M.

    Heartily millions thanks for your reply for my problem, Sorry for image as I am not able to post a image or a link to my image as my account say it has to be verified, so that the problem I am not able to post any image.

    With reference to your posts:-

    1. In a case If I am working with single Cell of Excel with JS API everything works fine, it gives me the desired output, its perfect in single cell process. 

    2.  If I call same code in my SelectionChange and OnDataChange events directly it gives me only that particular row only not other 20 rows which are effected with this single row by dragging this row to down. 

    3. Thanks for idea of process Excel using VBA, but as I am not sound of VAB and I have advised to this task with with App for Office JS API with Web Application, So can't go with this.

    Once I drag and update 20 rows I tried to Get again all data back to my Code via another click event and for that I used this Offce JS API Script to get all data in my excel

    Office.select("bindings#amitamit").getDataAsync(function (asyncResult) {
                                  varUpdationData = asyncResult.value;


    This returns me all data in Excel but updated data only in one single cell which I have taken reference to update next 20 rows but in front end it shows 20 updated rows with that single cell value after dragging but when I am trying to retrieve it with above script it gives me only one updated rows & other 20 are old values.

    I'll give more details view to my problem, I had make a ajax call with Database to get data and tried to Fill Excel with that data, then after I have created some event handlers, till here it works fine posting my Code for reference:-

    This is JS Method to get data and fill excel...

      var parameters = "{UserID:'" + document.getElementById("hdnUserID").value
                     + "', strProject: '" + document.getElementById("dllModuleName").value
                     + "', strFilterationID: '" + document.getElementById("dllFilteration").value
                     + "'}";

            type: "POST",
            url: "../../dccontactappending.asmx/subProcessInExcel",
            data: parameters,
            contentType: "application/json; charset=utf-8",
            dataType: "json",
            success: function (msg) {
                var Result = msg.d;
                var data = msg.d;

                var reportTable = new Office.TableData();
                var varColumnsSplit = new Array();

                var mycars = new Array();
                mycars[0] = ["Saab", "AAAAA"];
                var varColumnsSplit = data[0];
                mycars[1] = new Array(varColumnsSplit.ExcelItemRows.length);
                for (var i = 0; i < varColumnsSplit.ExcelItemRows.length  ; i++) {

                    mycars[0][i] = varColumnsSplit.ExcelItemRows[i];
                reportTable.headers = [mycars[0]];

                var DataValues = new Array();
                for (var i = 1; i < data.length; i++) {

                    DataValues[i - 1] = new Array(data[i].ExcelItemRows.length);

                    for (var j = 0; j < data[i].ExcelItemRows.length; j++) {
                        DataValues[i - 1][j] = data[i].ExcelItemRows[j];

                reportTable.rows = DataValues;

                Office.context.document.setSelectedDataAsync(reportTable, { coercionType: "table", id: 'amitamit' },
                    function (result) {
                        var error = result.error
                        if (result.status === "failed") {
                            write(error.name + ": " + error.message);

                Office.context.document.bindings.addFromSelectionAsync("table", { id: 'amitamit' },
                     function (asyncResult) {
                         if (asyncResult.status === "failed") {
                             write('Error: ' + asyncResult.error.message);
                         } else {
                             write('Added binding with type: ' + asyncResult.value.type + ' and id: ' +

                Office.select("bindings#amitamit").addHandlerAsync(Office.EventType.BindingDataChanged, onDataChanged);
                Office.context.document.bindings.getByIdAsync("amitamit", function (result) {
                    result.value.addHandlerAsync("bindingSelectionChanged", myHandlerSelectionChanged);

            error: function (req, errormsg) {
                var ssss = errormsg;

    With this script I am able to get what I want to and also the handlers I have are working fine with single cell but the dragging is the only issues I am having.

    Is am I doing some wrong with it OR the issues I am having is there or not yet being implemented with JS API.

    If I can have any mail id to where I can send problem in pic with better understanding to my issues.

    Again thanks a lot for your response. Will like to hear you again.


    Amit :)

    Thursday, May 2, 2013 4:07 AM
  • Amit,

    Because all of the events seem to correctly return the information for the updated "source" field, I suspect they are working as expected but as a test please try the following:

    • Update the first cell
    • Drag this cell down over the next 20 rows
    • Edit a second cell somewhere else on the worksheet (but out side of the 20 you are updating)
    • Then run your code from another click event.

    What I am hoping to see is that this returns the correct data. This would suggest that the screen has been update to show what will be changed but the change has not been "committed" (for lack of a better word) yet.

    Best Regards,

    Donald M.
    Microsoft Online Community Support
    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Thursday, May 2, 2013 4:29 PM