Binding.setDataAsync method (JavaScript API for Office v1.1)

JavaScript API for Office v1.1

Writes data to the bound section of the document represented by the specified binding object.

Last modified: June 12, 2014

Introduced in Office.js version

1.0

Updated in Office.js version

1.1

Available in requirement sets

MatrixBindings, TableBindings, TextBindings

bindingObj.setDataAsync(data [, options] ,callback);

data

Type:

String

Excel and Word only

Array (array of arrays – "matrix")

Excel and Word only

TableData

Access, Excel, and Word only

HTML

Word only

Office Open XML

Word only

The data to set in the binding. Required.

options

Type: object

Specifies the following optional parameters.

coercionType

Type: CoercionType

Specifies how to coerce the data being set. Optional.

columns

Type: array of strings

Only for table bindings in content apps for Access, specifies the column names. Optional.

rows

Type: Office.TableRange.ThisRow

Only for table bindings in content apps for Access, specifies the pre-defined string "thisRow" to set data in the currently selected row. Optional.

startColumn

Type: number

For table or matrix bindings, specifies the zero-based starting column for a subset of the data. Optional. If omitted, data is set starting in the first column.

startRow

Type: number

For table or matrix bindings, specifies the zero-based starting row for a subset of the data in the binding. Optional. If omitted, data is set starting in the first row.

tableOptions

Type: object

For table bindings, a list of key-value pairs that specify table formatting options, such as header row, total row, and banded rows. Only supported for formatting tables in apps for Excel in this release. Optional.

cellFormat

Type: object

For table bindings, a list of key-value pairs that specify a range of columns, rows, or cells and the cell formatting to apply to that range. Only supported for formatting tables in apps for Excel in this release. Optional.

asyncContext

Type: array, boolean, null, number, object, string, or undefined

A user-defined item of any type that is returned in the AsyncResult object without being altered. Optional.

callback

Type: object

A function that is invoked when the callback returns, whose only parameter is of type AsyncResult. Optional.

When the function you passed to the callback parameter executes, it receives an AsyncResult object that you can access from the callback function's only parameter.

In the callback function passed to the setDataAsync method, you can use the properties of the AsyncResult object to return the following information.

Property

Use to...

AsyncResult.value

Always returns undefined because there is no object or data to retrieve.

AsyncResult.status

Determine the success or failure of the operation.

AsyncResult.error

Access an Error object that provides error information if the operation failed.

AsyncResult.asyncContext

Access your user-defined object or value, if you passed one as the asyncContext parameter.

The value passed for data contains the data to be written in the binding. The kind of value passed determines what will be written as described in the following table.

data value

Data written

A string

Plain text or anything that can be coerced to a string will be written.

An array of arrays ("matrix")

Tabular data without headers will be written.

For example, to write data to three rows in two columns, you can pass an array like this:

[["R1C1", "R1C2"], ["R2C1", "R2C2"], ["R3C1", "R3C2"]]

To write a single column of three rows, pass an array like this:

[["R1C1"], ["R2C1"], ["R3C1"]]

A TableData object

A table with headers will be written.

Additionally, these application-specific actions apply when writing data to a binding.

For Word, the specified data is written to the binding as follows:

data value

Data written

A string

The specified text is written.

An array of arrays ("matrix") or a TableData object

A Word table is written.

HTML

The specified HTML is written.

Important note Important

If any of the HTML you write is invalid, Word will not raise an error. Word will write as much of the HTML as it can and will omit any invalid data.

Office Open XML ("Open XML")

The specified the XML is written.

For Excel, the specified data is written to the binding as follows:

data value

Data written

A string

The specified text is inserted as the value of the first bound cell.

You can also specify a valid formula to add that formula to the bound cell. For example, setting data to "=SUM(A1:A5)" will total the values in the specified range. However, when you set a formula on the bound cell, after doing so, you can't read the added formula (or any pre-existing formula) from the bound cell. If you call the Binding.getDataAsync method on the bound cell to read its data, the method can return only the data displayed in the cell (the formula's result).

An array of arrays ("matrix"), and the shape exactly matches the shape of the binding specified

The set of rows and columns are written.

You can also specify an array of arrays that contain valid formulas to add them to the bound cells. For example, setting data to [["=SUM(A1:A5)","=AVERAGE(A1:A5)"]] will add those two formulas to a binding that contains two cells. Just as when setting a formula on a single bound cell, you can't read the added formulas (or any pre-existing formulas) from the binding with the Binding.getDataAsync method – it returns only the data displayed in the bound cells.

A TableData object, and the shape of the table matches the bound table.

The specified set of rows and/or headers are written, if no other data in surrounding cells will be overwritten.

Note: If you specify formulas in the TableData object you pass for the data parameter, you might not get the results you expect due to the "calculated columns" feature of Excel, which automatically duplicates formulas within a column. To work around this when you want to write data that contains formulas to a bound table, try specifying the data as an array of arrays (instead of a TableData object), and specify the coercionType as Microsoft.Office.Matrix or "matrix".

In all other cases, an error is returned.

The setDataAsync method will write data in a subset of a table or matrix binding if the optional startRow and startColumn parameters are specified, and they specify a valid range.

  • In content apps for Access, you can create table bindings.

  • In apps for Excel, you can set formatting when writing data to a table binding by using the tableOptions and cellFormat optional parameters in your call to the setDataAsync method. For more information, see How to: Format tables in apps for Excel.

function setBindingData() {
    Office.select("bindings#MyBinding").setDataAsync('Hello World!', function (asyncResult) { });
}

Specifying the optional coercionType parameter lets you specify the kind of data you want to write to a binding. For example, in Word if you want to write HTML to a text binding, you can specify the coercionType parameter as "html" as shown in the following example, which uses HTML <b> tags to make "Hello" bold.

function writeHtmlData() {
    Office.select("bindings#myBinding").setDataAsync("<b>Hello</b> World!", {coercionType: "html"}, function (asyncResult) {
        if (asyncResult.status == "failed") {
            write('Error: ' + asyncResult.error.message);
        }
    });
}

// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

In this example, the call to setDataAsync passes the data parameter as an array of arrays (to create a single column of three rows), and specifies the data structure with the coercionType parameter as a "matrix".

function writeBoundDataMatrix() {
    Office.select("bindings#myBinding").setDataAsync([['Berlin'],['Munich'],['Duisburg']],{ coercionType: "matrix" }, function (asyncResult) {
        if (asyncResult.status == "failed") {
            write('Error: ' + asyncResult.error.message);
        } else {
            write('Bound data: ' + asyncResult.value);
        }
    });
}
// Function that writes to a div with id='message' on the page.
function write(message){
    document.getElementById('message').innerText += message; 
}

In the writeBoundDataTable function in this example, the call to setDataAsync passes the data parameter as a TableData object (to write three columns and three rows), and specifies the data structure with the coercionType parameter as a "table".

In the updateTableData function, the call to setDataAsync again passes the data parameter as a TableData object, but as a single column with a new header and three rows, to update the values in the last column of the table created with the writeBoundDataTable function. The optional zero-based startColumn parameter is specified as 2 to replace the values in the third column of the table.

function writeBoundDataTable() {
    // Create a TableData object.
    var myTable = new Office.TableData();
    myTable.headers = ['First Name', 'Last Name', 'Grade'];
    myTable.rows = [['Kim', 'Abercrombie', 'A'], ['Junmin','Hao', 'C'],['Toni','Poe','B']];

    // Set myTable in the binding.
    Office.select("bindings#myBinding").setDataAsync(myTable, { coercionType: "table" }, 
        function (asyncResult) {
            if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                write('Error: '+ asyncResult.error.message);
        } else {
            write('Bound data: ' + asyncResult.value);
        }
    });
}

// Replace last column with different data.
function updateTableData() {
     var newTable = new Office.TableData();
     newTable.headers = ["Gender"];
     newTable.rows = [["M"],["M"],["F"]];
     Office.select("bindings#myBinding").setDataAsync(newTable, { coercionType: "table", startColumn:2 }, 
         function (asyncResult) {
             if (asyncResult.status == Office.AsyncResultStatus.Failed) {
                 write('Error: '+ asyncResult.error.message);
         } else {
            write('Bound data: ' + asyncResult.value);
         }     
     });   
}

App types

Content app, Task pane app

Supported clients

Access app for SharePoint, Excel 2013, Excel 2013 SP1, Excel 2013 RT, Excel Online, Word 2013, Word 2013 SP1, Word 2013 RT

Library

Office.js

Namespace

Office

Show:
© 2014 Microsoft