MS Office


The Excel Adapter allows apps to use Excel as a local data editor. The app uploads tabular data into Excel, so that the user may view it and edit it in a familiar environment. Any changes can then be transmitted back to the app for validation, processing, auditing and/or long-term storage.


As shown in the Setup section, you need to initialize the @glue42/office library and set the excel property of the configuration object to true:

const config = {
    // ...,
    excel: true // enable Excel integration

After that, get a reference to the Excel Adapter API:

    .then(office => {
        const excel = office.excel
        // interact with Excel

Tracking Excel Adapter Status Changes

When io.Connect Desktop is initialized, you can check whether Excel is running and the Excel Adapter is loaded:

console.log(`Excel Adapter is ${excel.addinStatus ? "available" : "unavailable"}`);

You can use the onAddinStatusChanged() method to track the availability of the Excel Adapter. You may find this useful if you need to track when to enable or disable certain elements of your app user interface.

const unsubscribe = excel.onAddinStatusChanged(available => {
    console.log(`Excel Adapter is ${available ? "available" : "not available"}`)

The available argument passed to your callback will be true if and only if:

  • Excel is running.
  • The Excel Adapter is installed and enabled in Excel.
  • The Excel Adapter and your app are using the same connectivity configuration and are connected to the same io.Connect Gateway.

In any other case the available flag will be false.

To stop listening for connection status changes, simply call the returned function:


Sending Data to Excel

To send a table to Excel, you need to call the openSheet() method.

You must specify the shape of your app data, optionally pass the row data and certain customization options. All of these need to be packaged in a single OpenSheetConfig object, passed to the openSheet() call.

The example below assumes your app is displaying a financial portfolio (e.g., a list of stocks a person owns). Here is how to push the data in Excel:

const config = {
    columnConfig: [
        { header: "Symbol", fieldName: "symbol" },
        { header: "Quantity", fieldName: "quantity" }
    data: [
        { quantity: 100, symbol: "AAPL" },
        { quantity: 200, symbol: "GOOG" }

    .then(sheet => console.log("Sent data to Excel"))

The example above will open a new Excel workbook, create an empty sheet and will create the following table and place it at position A1 (row 1, column A):

Symbol Quantity
AAPL 100
GOOG 200

The header in the ColumnConfig is optional and specifies the caption of the column in Excel. If omitted, the field name will be used.

If you don't need to send any data but just set up a table in Excel for the user to populate, then you can omit the data property. You can still populate the table later using the update() method on the Sheet object.

Note that the Promise returned by the openSheet() method resolves with a reference to a Sheet object.

Customization Options

By default, when your app sends data to Excel, the Application Adapter will create a new workbook, a new spreadsheet in the workbook, and place the unformatted data in the 1st row and column (A1).

There are certain customizations you can apply by specifying the options property in the OpenSheetConfig object.

Column Customization

If you are planning to take updates from the user into your app, you shouldn't rely on the ordering of the rows when accepting the data because the user might have filtered or sorted the data before returning it to your app. This means that:

  • all your rows must be keyed somehow, so you can track what has changed
  • you must not accept changes to columns containing keys

You can also specify a background and/or a foreground color and a column width:

const columns = [
        header: "Symbol",
        fieldName: "symbol",
        width: 80
    // ...

Preventing Saving Temporary Workbooks

If your users need to message data to Excel, but aren't allowed to save it locally, and should instead return the data to be saved in your app, you can set the inhibitLocalSave flag to true to prevent the users from saving temporary workbooks.

Custom Workbook, Sheet and Range

If your app needs to create (or re-open and re-use) a specific workbook, or place data in a specific spreadsheet and location in the spreadsheet, you can use the workbook, worksheet and topLeft options:

const config = {
    options: {
        workbook: "ClientData.xls",
        worksheet: "John Doe",
        topLeft: "B2",
        dataRangeName: "ClientData",
        clearGrid: true

Note that all settings except the columnConfig property are optional. If the workbook doesn't exist, it is going to be created, otherwise re-opened. If there is data in the specified spreadsheet, it's going to be wiped, unless you've set clearGrid to false. In the example above the data will be placed in the John Doe spreadsheet, starting on the 2nd row and column (B2). You can use also use Row/Column references, e.g., R2C2 (row 2 column 2). Specifying dataRangeName names the range of cells which starts at B2 and spans your data to ClientData.

Using Templates

Excel Templates are workbooks that can be used to create a framework that the Excel Adapter should use when displaying a new set of data to the user. When the app invokes an io.Connect Excel API method, it may request that the data be added to a copy of an existing workbook (the template) that has been formatted to present the data correctly for the user. The Excel Adapter will then make a copy of the workbook and paste the data into it, instead of using a new blank workbook.

If you want to send formatted data to Excel or you want to include more than just the data (e.g. headers, footers, charts and maps), you can use templateWorkbook and templateSheet:

const config = {
    options: {
        templateWorkbook: "ReportTemplate.xls",
        templateWorksheet: "Data with Chart"

Receiving Updates from Excel

Once you have obtained a reference to the opened spreadsheet, you can subscribe for and start tracking updates made by the user, receive and validate them in your app, using the onChanged() method on the Sheet object:

const config = {
    columnConfig: [
        { header: "Symbol", fieldName: "symbol" },
        { header: "Quantity", fieldName: "quantity" }
    data: [
        { quantity: 100, symbol: "AAPL" },
        { quantity: 200, symbol: "GOOG" },
    options: {
        // Configure Excel to trigger sheet change events when the user changes
        // one or more cells in a given row and then selects a cell in a different row.
        // By default, sheet change events will be triggered when the user clicks a predefined button in Excel.
        updateTrigger: ["row"]

    .then(sheet => {
        console.log("Sent data to Excel", sheet);

        sheet.onChanged((data, errorCallback, doneCallback, delta) => {
            console.log("data: ", data);
            console.log("delta: ", delta);


The function passed to onChanged() will be called with the data sent from Excel. The data parameter holds an array of objects where each object corresponds to a row in Excel. Each object will have a number of properties populated with data, where each property will correspond to the fieldName property you have passed in the respective column definition when calling openSheet().

You can optionally validate the data by calling the errorCallback function or accept the changes by calling the doneCallback function. Declarative and imperative validation is explained in details in the Data Validation section below.

The delta argument is a DeltaItem object which holds the changes made to the row(s).

The onChanged() method call creates a subscription for changes done by the user. To unsubscribe, simply call the returned unsubscribe() function:

const unsubscribe = sheet.onChanged(...)
unsubscribe()   // sheet no longer tracked for changes

Data Validation

There are 2 types of validation you can perform before you accept data from Excel:

  • preventing users from typing incompatible data (e.g., column is numeric but the user types in some text), which you can do using Declarative Validation;
  • preventing users from breaking the integrity of your data (which can span all data), which you can do using Programmatic Validation;

Declarative Validation

You can specify what kind of data the user is allowed to type in a given column using the validation property from the ColumnConfig:

const quantityColumn = {
    header: "Quantity",
    fieldName: "quantity",
    // whole numbers only
    validation: {
        alert: "Stop",
        type: "WholeNumber"

The possible alert types are:

Alert Description
Information Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it.
Stop Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel.
Warning Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.

The possible values for the type property are:

Type Description
Date Only dates are allowed
Time Only times are allowed
Decimal Only decimal numbers are allowed. Once the decimal number option is selected, other options become available to further limit the input.
TextLength Validates the input based on a number of characters or digits
WholeNumber Only whole numbers are allowed. Once the whole number option is selected, other options become available to further limit input.
List Only values from a predefined list are allowed. The values are presented to the user as a dropdown menu control.

The list validation type requires you to pass one more property holding the allowed set of values:

const stateColumn = {
    header: "State",
    fieldName: "state",
    validation: {
        alert: "Stop",
        type: "List",
        list: ["Arizona", "California", ...]

When your app has set the validation property and the alert is Stop, Excel won't even attempt to return data back to your app if the user input is invalid.

Programmatic Validation

When your app needs a more sophisticated validation than what the validation property can offer, you can write code to completely control the validation of the data sent from Excel to your app.

Here is again how subscribing for user updates looks like:

sheet.onChanged((data, errorCallback, doneCallback, delta) => {
    // add your custom validation here

When your app receives an update from Excel, you can signal Excel back by calling the errorCallback and let Excel know that there were errors in the user's input which need to be corrected in Excel before the data is accepted by your web app.

The errorCallback accepts a list of validation errors, where a validation error specifies which cell (row and column) is in error and what the problem is. Here is an example of handling multiple validation errors:

sheet.onChanged((data, errorCallback, doneCallback, delta) => {
    // ...
    const errors = data.reduce(
        (errors, rowData, rowIndex) => {
            if (!rowData["firstName"]) {
                    row: rowIndex + 1,
                    column: 0,
                    description: "First name is mandatory"
            if (Number(rowData["subscriptionMonths"]) < 6) {
                    row: rowIndex + 1,
                    column: 2,
                    description: "Subscription period must be at least 6 months",
                    text: "6"   // replacing what the user typed
        }, [])

    // if during the validation there are any errors accumulated
    // you need to call the errorCallback, otherwise the doneCallback
    if (errors.length > 0) {
    else {

The column property in the validation error can either be a Number (the 0-based column index) or a String (the fieldName specified in the column config).

You can also customize the way Excel displays the errors by setting the foregroundColor, backgroundColor and some other properties of the validationError object.

Controlling Update Frequency

If you are expecting updates from Excel, you can specify how often your app should get updates by setting a value for the updateTrigger property in the OpenSheetOptions object.

The possible values are:

Trigger Type Description
button When the user clicks the Return Data button on the io.Connect ribbon
row When the user changes one or more cells in a given row and then selects a cell in a different row
save When the user tries to save the worksheet

The row options is the most interactive, since your app gets updated as the user moves through the spreadsheet.

When using the button option, you can also customize the caption of the button and where it is placed by using the buttonText and buttonRange properties:

const config = {
    {   // options follow
        updateTrigger: "Button",
        buttonText: "Send back data",
        buttonRange: "A1:C1"    // button spans 3 cells

excel.openSheet(config).then(sheet => ...)

Excel Scripting

Upon launching, the Excel Adapter registers the following Interop methods and streams:

Method Description
"T42.ExcelPad.ShowGrid" Displays a grid of data in Excel for the user to edit.
"T42.ExcelPad.ShowTable" Displays a table of data in Excel for the user to edit.
"T42.ExcelScript.GetState" Returns the state of Excel - workbooks and worksheets.
"T42.ExcelScript.Grid.AddRow" Adds a new row with data to the end of a grid.
"T42.ExcelScript.Grid.FindRow" Searches a grid for a row that contains a given column value.
"T42.ExcelScript.Grid.GetInformation" Gets Excel grid information.
"T42.ExcelScript.Grid.OnRowAdded" Subscribes for the event which fires when one or more new rows have been added to a grid.
"T42.ExcelScript.Grid.OnRowUpdated" Subscribes for the event which fires when one or more rows have been changed in a grid.
"T42.ExcelScript.Grid.ReadRow" Reads a row of data from a grid.
"T42.ExcelScript.Table.AddRow" Adds a new row with data to the end of a table.
"T42.ExcelScript.Table.FindRow" Searches a table for a row that contains a given column value.
"T42.ExcelScript.Table.GetInformation" Gets Excel table information.
"T42.ExcelScript.Table.OnRowAdded" Subscribes for the event which fires when one or more rows have been added to a table.
"T42.ExcelScript.Table.OnRowUpdated" Subscribes for the event which fires when one or more rows have been changed in a table.
"T42.ExcelScript.Table.ReadRow" Reads a row of data from a table.
"T42.ExcelScript.Workbook.Create" Creates a new blank workbook and returns the list of worksheets in it.
"T42.ExcelScript.Workbook.GetFiles" Gets the workbooks in a whitelisted folder.
"T42.ExcelScript.Workbook.GetFolders" Gets a list of folders that may be searched according to the configuration.
"T42.ExcelScript.Workbook.GetTemplates" Gets the templates in a whitelisted folder.
"T42.ExcelScript.Workbook.IsOpen" Indicates whether a workbook is open.
"T42.ExcelScript.Workbook.OnAdded" Subscribes for the event which fires when a new workbook has been created.
"T42.ExcelScript.Workbook.OnOpened" Subscribes for the event which fires when a workbook has been opened.
"T42.ExcelScript.Workbook.Open" Opens a workbook and returns the list of worksheets in it.
"T42.ExcelScript.Workbook.SetWindowState" Sets the Excel window state - normal, maximized, minimized, focused, etc.
"T42.ExcelScript.Worksheet.Create" Creates a new worksheet in the current workbook.
"T42.ExcelScript.Worksheet.OnAdded" Subscribes for the event which fires when a new worksheets has been added in a workbook.
"T42.ExcelScript.Worksheet.Write" Updates a worksheet with new data.


For a complete list of the available Excel Adapter API methods and properties, see the Excel Adapter Reference Documentation.