Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
setSelectedDataAsync method

Document.setSelectedDataAsync method (JavaScript API for Office)

Office Add-ins

Writes data to the current selection in the document.

Last modified: August 04, 2015

Note Note

The name "apps for Office" is changing to "Office Add-ins". During the transition, the documentation and the UI of some Office host applications and Visual Studio tools might still use the term "apps for Office". For details, see New name for apps for Office and SharePoint.

Hosts: Access, Excel, PowerPoint, Project, Word

App types: Content, Task pane

Available in Requirement set

Selection

Last changed in

1.1

See all support details

Try it out

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

Name

Type

Description

Support notes

data

Host support

string (Office.CoercionType.Text)

Excel, Excel Online, PowerPoint, PowerPoint Online, Word, and Word Online only

array of arrays (Office.CoercionType.Matrix)

Excel, Word, and Word Online only

TableData (Office.CoercionType.Table)

Access, Excel, Word, Word Online only

HTML

Word, and Word Online only

Office Open XML

Word only

The data to be set in the current selection. Required.

Changed in: 1.1.

Support for content apps for Access requires Selection requirement set 1.1 or later.

options

object

Specifies any of the following optional parameters

coercionType

CoercionType

Specifies how to coerce the data being set.

tableOptions

object

For the inserted table, a list of key-value pairs that specify table formatting options, such as header row, total row, and banded rows.

Added in: v1.1.

Supported in: Excel 2013, Excel Online.

cellFormat

object

For the inserted 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.

Added in v1.1.

Supported in: Excel 2013, Excel Online.

asyncContext

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.

callback

object

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

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, the AsyncResult.value property always returns undefined because there is no object or data to retrieve.

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".

Application-specific behaviors

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 won't raise an error. Word will insert as much of the HTML as it can and omits 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.

For Excel Online

In addition to the behaviors described for Excel above, the following limits apply when writing data in Excel Online.

  • The total number of cells you can 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){});
    

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.context.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; 
}

A checkmark (√) in the following matrix indicates that this method is supported in the corresponding Office host application. An empty cell indicates that the Office host application doesn't support this method.

For more information about Office host application and server requirements, see Requirements for running apps for Office.

Supported hosts, by platform

Office for Windows desktop

Office Online
(in browser)

Office for iPad

Access

Check symbol

Excel

Check symbol

Check symbol

Check symbol

PowerPoint

Check symbol

Check symbol

Word

Check symbol

Check symbol

Check symbol

Available in requirement sets

Selection

Minimum permission level

WriteDocument

App types

Content, task pane

Library

Office.js

Namespace

Office

Version

Changes

1.1

In Word Online, added support for writing data as an array of arrays (matrix) and TableData (table).

1.1

In Excel and Word in Office for iPad, added the same level of support as Excel and Word on Windows desktop.

1.1

In Word Online, added support for writing data as string (text).

1.1

Added support for setting formatting when inserting tables with apps for Excel by using the tableOptions and cellFormat optional parameters.

1.1

Added support for writing table data in apps for Access.

1.0

Introduced

Show:
© 2015 Microsoft