BindingCollection Object (JavaScript API for Excel)

Represents the collection of all the binding objects that are part of the workbook.

Properties

Property Type Description Req. Set
count int Returns the number of bindings in the collection. Read-only. 1.1
items Binding[] A collection of binding objects. Read-only. 1.1

See property access examples.

Relationships

None

Methods

Method Return Type Description Req. Set
add(range: Range or string, bindingType: string, id: string) Binding Add a new binding to a particular Range. 1.3
addFromNamedItem(name: string, bindingType: string, id: string) Binding Add a new binding based on a named item in the workbook. 1.3
addFromSelection(bindingType: string, id: string) Binding Add a new binding based on the current selection. 1.3
getCount() int Gets the number of bindings in the collection. 1.4
getItem(id: string) Binding Gets a binding object by ID. 1.1
getItemAt(index: number) Binding Gets a binding object based on its position in the items array. 1.1
getItemOrNullObject(id: string) Binding Gets a binding object by ID. If the binding object does not exist, will return a null object. 1.4

Method Details

add(range: Range or string, bindingType: string, id: string)

Add a new binding to a particular Range.

Syntax

bindingCollectionObject.add(range, bindingType, id);

Parameters

Parameter Type Description  
range Range or string Range to bind the binding to. May be an Excel Range object, or a string. If string, must contain the full address, including the sheet name  
bindingType string Type of binding. Possible values are: Range, Table, Text  
id string Name of binding.  

Returns

Binding

addFromNamedItem(name: string, bindingType: string, id: string)

Add a new binding based on a named item in the workbook.

Syntax

bindingCollectionObject.addFromNamedItem(name, bindingType, id);

Parameters

Parameter Type Description  
name string Name from which to create binding.  
bindingType string Type of binding. Possible values are: Range, Table, Text  
id string Name of binding.  

Returns

Binding

addFromSelection(bindingType: string, id: string)

Add a new binding based on the current selection.

Syntax

bindingCollectionObject.addFromSelection(bindingType, id);

Parameters

Parameter Type Description  
bindingType string Type of binding. Possible values are: Range, Table, Text  
id string Name of binding.  

Returns

Binding

getCount()

Gets the number of bindings in the collection.

Syntax

bindingCollectionObject.getCount();

Parameters

None

Returns

int

getItem(id: string)

Gets a binding object by ID.

Syntax

bindingCollectionObject.getItem(id);

Parameters

Parameter Type Description  
id string Id of the binding object to be retrieved.  

Returns

Binding

Examples

Create a table binding to monitor data changes in the table. When data is changed, the background color of the table will be changed to orange.

function addEventHandler() {
    //Create Table1
Excel.run(function (ctx) { 
    ctx.workbook.tables.add("Sheet1!A1:C4", true);
    return ctx.sync().then(function() {
             console.log("My Diet Data Inserted!");
    })
    .catch(function (error) {
             console.log(JSON.stringify(error));
    });
});
    //Create a new table binding for Table1
Office.context.document.bindings.addFromNamedItemAsync("Table1", Office.CoercionType.Table, { id: "myBinding" }, function (asyncResult) {
    if (asyncResult.status == "failed") {
        console.log("Action failed with error: " + asyncResult.error.message);
    }
    else {
        // If succeeded, then add event handler to the table binding.
        Office.select("bindings#myBinding").addHandlerAsync(Office.EventType.BindingDataChanged, onBindingDataChanged);
    }
});
}
    
// when data in the table is changed, this event will be triggered.
function onBindingDataChanged(eventArgs) {
Excel.run(function (ctx) { 
    // highlight the table in orange to indicate data has been changed.
    ctx.workbook.bindings.getItem(eventArgs.binding.id).getTable().getDataBodyRange().format.fill.color = "Orange";
    return ctx.sync().then(function() {
            console.log("The value in this table got changed!");
    })
    .catch(function (error) {
            console.log(JSON.stringify(error));
    });
});
}

Examples

Excel.run(function (ctx) { 
    var lastPosition = ctx.workbook.bindings.count - 1;
    var binding = ctx.workbook.bindings.getItemAt(lastPosition);
    binding.load('type')
    return ctx.sync().then(function() {
            console.log(binding.type); 
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getItemAt(index: number)

Gets a binding object based on its position in the items array.

Syntax

bindingCollectionObject.getItemAt(index);

Parameters

Parameter Type Description  
index number Index value of the object to be retrieved. Zero-indexed.  

Returns

Binding

Examples

Excel.run(function (ctx) { 
    var lastPosition = ctx.workbook.bindings.count - 1;
    var binding = ctx.workbook.bindings.getItemAt(lastPosition);
    binding.load('type')
    return ctx.sync().then(function() {
            console.log(binding.type); 
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

getItemOrNullObject(id: string)

Gets a binding object by ID. If the binding object does not exist, will return a null object.

Syntax

bindingCollectionObject.getItemOrNullObject(id);

Parameters

Parameter Type Description  
id string Id of the binding object to be retrieved.  

Returns

Binding

Property access examples

Excel.run(function (ctx) { 
    var bindings = ctx.workbook.bindings;
    bindings.load('items');
    return ctx.sync().then(function() {
        for (var i = 0; i < bindings.items.length; i++)
        {
            console.log(bindings.items[i].id);
        }
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});

Get the number of bindings

Excel.run(function (ctx) { 
    var bindings = ctx.workbook.bindings;
    bindings.load('count');
    return ctx.sync().then(function() {
        console.log("Bindings: Count= " + bindings.count);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});
e number of bindings

Excel.run(function (ctx) { 
    var bindings = ctx.workbook.bindings;
    bindings.load('count');
    return ctx.sync().then(function() {
        console.log("Bindings: Count= " + bindings.count);
    });
}).catch(function(error) {
        console.log("Error: " + error);
        if (error instanceof OfficeExtension.Error) {
            console.log("Debug info: " + JSON.stringify(error.debugInfo));
        }
});