Export (0) Print
Expand All

Document.setSelectedDataAsync method (JavaScript API for Office v1.1)

JavaScript API for Office v1.1

Writes data to the current selection in the document.

Last modified: July 21, 2014

Introduced in Office.js version

1.0

Updated in Office.js version

1.1

Available in requirement sets

Selection

Office.context.document.setSelectedDataAsync(data [, options], callback);

data

Type:

String

Excel, PowerPoint, Project, 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 be set in the current selection. Required.

options

Type: object

Specifies the following optional parameters.

coercionType

Type: CoercionType

Specifies how to coerce the data being set. Optional.

tableOptions

Type: object

For the selected table, 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 the selected table, 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 setSelectedDataAsync 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 write to the current selection. If the value is:

  • A string: Plain text or anything that can be coerced to a string will be inserted.

    In Excel, you can also specify data as a valid formula to add that formula to the selected 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 Document.getSelectedDataAsync method on the selected 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"): Tabular data without headers will be inserted. 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"]]

    In Excel, you can also specify data as an array of arrays that contains valid formulas to add them to the selected cells. For example if no other data will be overwritten, setting data to [["=SUM(A1:A5)","=AVERAGE(A1:A5)"]] will add those two formulas to the selection. Just as when setting a formula on a single cell as "text", you can't read the added formulas (or any pre-existing formulas) after they have been set – you can only read the formulas' results.

  • A TableData object: A table with headers will be inserted.

    Note: In Excel, 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 selected 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".

Additionally, the following application-specific actions apply when writing data to a selection.

For Word

  • If there is no selection and the insertion point is at a valid location, the specified data is inserted at the insertion point as follows:

    • If data is a string, the specified text is inserted.

    • If data is an array of arrays ("matrix") or a TableData object, a new Word table is inserted.

    • If data is HTML, the specified HTML is inserted.

      Important note Important

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

    • If data is Office Open XML, the specified the XML is inserted.

  • If there is a selection, it will be replaced with the specified data following the same rules as above.

For Excel

  • If a single cell is selected:

    • If data is a string, the specified text is inserted as the value of the current cell.

    • If data is an array of arrays ("matrix"), the specified set of rows and columns are inserted, if no other data in surrounding cells will be overwritten.

    • If data is a TableData object, a new Excel table with the specified set of rows and headers is inserted, if no other data in surrounding cells will be overwritten.

  • If multiple cells are selected and the shape does not match the shape of data, an error is returned.

  • If multiple cells are selected and the shape of the selection exactly matches the shape of data, the values of the selected cells are updated based on the values in data.

In all other cases, an error is returned.

Additional remarks for Excel Online

  • The total number of cells you write to a worksheet with the data parameter can't exceed 20,000 in a single call to this method.

  • The number of formatting groups passed to the cellFormat parameter can't exceed 100. A single formatting group consists of a set of formatting applied to a specified range of cells. For example, the following call passes two formatting groups to cellFormat.

    Office.context.document.setSelectedDataAsync(
        {cellFormat:[{cells: {row: 1}, format: {fontColor: "yellow"}}, 
            {cells: {row: 3, column: 4}, format: {borderColor: "white", fontStyle: "bold"}}]}, 
        function (asyncResult){});
    

  • In apps for Excel, you can set formatting when inserting a table (passing a TableData object as the data parameter) by using the tableOptions and cellFormat optional parameters in your call to the setSelectedDataAsync method. For more information, see How to: Format tables in apps for Excel.

The following example sets the selected text or cell to "Hello World!", and if that fails, displays the value of the error.message property.

function writeText() {
    Office.context.document.setSelectedDataAsync("Hello World!",
        function (asyncResult) {
            var error = asyncResult.error;
            if (asyncResult.status === "failed"){
            write(error.name + ": " + error.message);
            }
        });
}

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

Specifying the optional coercionType parameter lets you specify the kind of data you want to write to a selection. The following example writes data as an array of three rows of two columns, specifying the coercionType as "matrix" for that data structure, and if that fails, displays the value of the error.message property.

function writeMatrix() {
    Office.context.document.setSelectedDataAsync([["Red", "Rojo"], ["Green", "Verde"], ["Blue", "Azul"]], {coercionType: "matrix"}
        function (asyncResult) {
            var error = asyncResult.error;
            if (asyncResult.status === "failed"){
            write(error.name + ": " + error.message);
            }
        });
}

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

The following example writes data as a one column table with a header and four rows, specifying the coercionType as "table" for that data structure, and if that fails, displays the value of the error.message property.

function writeTable() {
    // Build table.
    var myTable = new Office.TableData();
    myTable.headers = [["Cities"]];
    myTable.rows = [['Berlin'], ['Roma'], ['Tokyo'], ['Seattle']];

    // Write table.
    Office.context.document.setSelectedDataAsync(myTable, {coercionType: "table"},
        function (result) {
            var error = result.error
            if (result.status === "failed") {
                write(error.name + ": " + error.message);
            }
    });
}

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

In Word if you want to write HTML to the selection, 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.document.setSelectedDataAsync("<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; 
}

App types

Content apps, Task pane apps

Supported clients

Excel 2013, Excel 2013 SP1, Excel Online, Word 2013, Word 2013 SP1, Word 2013 RT, Project 2013 SP1, PowerPoint 2013 SP1, PowerPoint 2013 RT, and PowerPoint 2013.

Library

Office.js

Namespace

Office

Show:
© 2014 Microsoft