none
Shared API - SetDataAsync - Update Row Data, One Column Only RRS feed

  • Question

  • Is it possible to only update values in one column of the table? My table has two columns, and the first column is essentially a "header", as it contains identifiers for the values that appear in the second column. However, the syntax interprets headers as the first row over X columns. When I update data, I don't want to have to pass/hard-code the field names each time. But I don't see how you can tell the function to only update values in the 2nd column. 

    My code below updates the values, but wipes out my first column of descriptions.

    Thanks,

    function setTableData() {
    
        var newTable = new Office.TableData();
         //set appropriate values:
        newTable.rows = [["1"], ["0"], ["0"], ["0"], ["0"], ["0"], ["0"], ["0"], ["0"], ["0"], ["0"]];
    
        Office.select("bindings#test").setDataAsync(newTable, { coercionType: "table" },
    
            function (asyncResult) {
                if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                    write('settings-message', 'Error: ' + asyncResult.error.message);
                } else {
                    write('settings-message', 'Bound data: ' + asyncResult.value);
                }
            });
    }

    Wednesday, May 10, 2017 9:03 PM

All replies

  • Hello,

    To update one column data, we could specify its startColumn. For more information, please visit Binding.setDataAsync method

    E.g.

    var newTable = new Office.TableData();
        newTable.rows = [["1"], ["2"], ["3"]];
    
        binding.setDataAsync(
          newTable,
          {
              coercionType: Office.CoercionType.Table,
              startColumn: 1,
              startRow: 0
    
          }

    The code sample below shows how to set binding and refresh the 2nd column.

    var tableName = "Sheet1!test",
         bindingName = "test",
         binding;
    
    function setBinding() {
        Office.context.document.bindings.addFromNamedItemAsync(
            tableName,
            Office.BindingType.Table,
            { id: bindingName },
            function (results) {
                // Capture a reference to the binding and then add
                // an event handler to the binding.
    
                //binding = results.value;
                //addBindingsHandler(function () { refreshData(); });
            });
    }
    
    
    function setTableData() {
        Office.context.document.bindings.getByIdAsync("test", function (asyncResult) {
            binding = asyncResult.value;
            addBindingsHandler(function () { refreshData(); });
        });
    }
    
    function addBindingsHandler(callback) {
        Office.select("bindings#" + bindingName).addHandlerAsync(
          Office.EventType.BindingDataChanged,
          onBindingDataChanged,
          function () {
              if (callback) { callback(); }
          });
    }
    
    var onBindingDataChanged = function (result) {
        refreshData();
    }
    
    function refreshData() {
        binding.getDataAsync(
            {
                startRow: 0,
                startColumn: 0,
                columnCount: 1
            },
            function (results) {
                removeHandler(function () {
                    updateTable();
                });
            });
    }
    
    function removeHandler(callback) {
    
        binding.removeHandlerAsync(
          Office.EventType.BindingDataChanged,
          { handler: onBindingDataChanged },
          function (results) {
              if (results.status == Office.AsyncResultStatus.Succeeded) {
                  if (callback) { callback(); }
              }
          });
    }
    function updateTable() {
        var newTable = new Office.TableData();
        newTable.rows = [["1"], ["2"], ["3"]];
    
        binding.setDataAsync(
          newTable,
          {
              coercionType: Office.CoercionType.Table,
              startColumn: 1,
              startRow: 0
    
          },
          function (results) {
              if (results.status == Office.AsyncResultStatus.Succeeded) {
                  addBindingsHandler();
              }
          });
    }

    Reference: Excel-Add-in-Bind-To-Table

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, May 11, 2017 3:05 AM
    Moderator