This sample shows how it is difficult to check for and delete invalid bindings.

Handling invalid bindings.EXCEL.yaml

name: Handling invalid bindings
description: >-
  This sample shows how it is difficult to check for and delete invalid
  bindings.
host: EXCEL
api_set: {}
script:
  content: |
    $("#add-binding").click(() => tryCatch(addBinding));
    $("#remove-cell").click(() => tryCatch(removeCell));
    $("#add-many-bindings").click(() => tryCatch(addManyBindings));

    $("#list-bindings").click(() => tryCatch(listBindings));
    $("#get-binding-value").click(() => tryCatch(getBindingValue));
    $("#is-binding-valid").click(() => tryCatch(isBindingValid));
    $("#delete-binding-2016").click(() => tryCatch(deleteBinding2016));
    $("#delete-binding-2013").click(() => tryCatch(deleteBinding2013));
    $("#all-bindings-valid").click(() => tryCatch(allBindingsValid));
    $("#delete-all-bindings").click(() => tryCatch(deleteAllBindings));

    async function addBinding() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        const sheet = context.workbook.worksheets.add("Sample");
        sheet.activate();

        let binding = context.workbook.bindings.add("Sample!A1", Excel.BindingType.range, "binding1");
        binding.getRange().values = [["foo"]];

        await context.sync();
      });
    }

    async function addManyBindings() {
      await Excel.run(async (context) => {
        context.workbook.worksheets.getItemOrNullObject("Sample").delete();
        const sheet = context.workbook.worksheets.add("Sample");
        sheet.activate();

        for (let i = 1; i <= 50; i++) {
          let binding = context.workbook.bindings.add("Sample!A" + i, Excel.BindingType.range, "binding" + i);
          binding.getRange().values = [["foo" + i]];
        }

        await context.sync();
      });
    }

    async function allBindingsValid() {
      await Excel.run(async (context) => {
        let startTime = new Date();
        let numInvalid = 0;
        let numValid = 0;

        // Load list of all bindings
        let bindings = context.workbook.bindings;
        bindings.load("items");
        await context.sync();

        // Check each binding for validity and add to count
        let validBindingIds = [];
        for (let i = 0; i < bindings.items.length; i++) {
          try {
            let binding = bindings.getItemAt(i);
            await context.sync();

            validBindingIds.push(bindings.items[i].id);
            numValid++;
          } catch (ex) {
            if (ex.code !== Excel.ErrorCodes.invalidBinding) throw ex;
            numInvalid++;
          }
        }

        let endTime = new Date();

        console.log("Valid bindings:", validBindingIds);
        console.log("# valid: " + numValid);
        console.log("# INVALID: " + numInvalid);
        console.log("Elapsed time: " + (endTime.getTime() - startTime.getTime()) + " ms");
      });
    }

    async function deleteAllBindings() {
      await Excel.run(async (context) => {
        // Delete bindings using 2013 syntax since that will actually work
        for (let i = 1; i <= 50; i++) {
          Office.context.document.bindings.releaseByIdAsync("binding" + (i+1));
        }

        console.log("All bindings have been deleted.");
      });
    }

    async function deleteBinding2013() {
      await Excel.run(async (context) => {
        Office.context.document.bindings.releaseByIdAsync("binding1");

        console.log("Binding1 has been deleted.");
      });
    }

    async function deleteBinding2016() {
      await Excel.run(async (context) => {
        context.workbook.bindings.getItem("binding1").delete();
        await context.sync();

        console.log("Binding1 has been deleted.");
      });
    }

    async function getBindingValue() {
      await Excel.run(async (context) => {
        let bindingRange = context.workbook.bindings
          .getItem("binding1")
          .getRange()
          .load("values");
        await context.sync();

        console.log("Binding 'binding1' has value: " + bindingRange.values[0][0]);
      });
    }

    async function isBindingValid() {
      await Excel.run(async (context) => {
        let isValid = true;
        let binding = context.workbook.bindings.getItem("binding1");

        try {
          await context.sync();
        } catch (ex) {
          if (ex.code !== Excel.ErrorCodes.invalidBinding) throw ex;
          isValid = false;
        }

        console.log("Validity of 'binding1': " + (isValid ? "valid" : "INVALID"));
      });
    }

    async function listBindings() {
      await Excel.run(async (context) => {
        let bindings = context.workbook.bindings.load();
        await context.sync();

        console.log("This workbook contains " + bindings.items.length + " binding(s).");

        for (let i = 0; i < bindings.items.length; i++) {
          console.log(JSON.stringify(bindings.items[i])) + "\n";
        }

        await context.sync();
      });
    }

    async function removeCell() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getItem("Sample");
        let cell = sheet.getRange("A1");
        cell.delete(Excel.DeleteShiftDirection.left);
        await context.sync();
      });
    }

    /** Default helper for invoking an action and handling errors. */
    async function tryCatch(callback) {
      try {
        await callback();
      } catch (error) {
        // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
        console.error(error);
      }
    }
  language: typescript
template:
  content: "<section class=\"ms-font-m\">\n\t<p>Add a binding then remove the cell it's in. This causes the binding\n\t\tto be invalid, as expected.\n\t</p>\n\t<p>Validity is only determined by an exception thrown from <code>.sync()</code>;\n\t\tthus it's a costly operation. This is magnified when checking many bindings,\n\t\tas there is a <code>.sync()</code> call for each one.\n\t</p>\n\t<p>Further, you cannot delete an invalid binding using the 2016 paradigm.\n\t</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Setup</h3>\n\t<button id=\"add-binding\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Add 'binding1' to Cell A1</span>\n    </button>\n\t<button id=\"remove-cell\" class=\"ms-Button\">\n\t        <span class=\"ms-Button-label\">Remove Cell A1</span>\n\t    </button>\n\t<br />\n\n\t<button id=\"add-many-bindings\" class=\"ms-Button\">\n\t        <span class=\"ms-Button-label\">Add 50 bindings to Cells A1:A50</span>\n\t    </button>\n\n\t<h3>Try it out</h3>\n\t<button id=\"list-bindings\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">List bindings</span>\n    </button>\n\t<button id=\"get-binding-value\" class=\"ms-Button\">\n\t      <span class=\"ms-Button-label\">Get 'binding1' value</span>\n\t  </button>\n\t<button id=\"is-binding-valid\" class=\"ms-Button\">\n\t\t\t\t<span class=\"ms-Button-label\">Check 'binding1' validity</span>\n\t\t</button>\n\t<button id=\"delete-binding-2016\" class=\"ms-Button\">\n\t\t\t\t<span class=\"ms-Button-label\">Delete 'binding1' (2016 way)</span>\n\t\t</button>\n\t<button id=\"delete-binding-2013\" class=\"ms-Button\">\n\t\t\t\t\t<span class=\"ms-Button-label\">Delete 'binding1' (2013 way)</span>\n\t\t\t</button>\n\t<br />\n\n\t<button id=\"all-bindings-valid\" class=\"ms-Button\">\n\t\t\t\t\t<span class=\"ms-Button-label\">Check all bindings validity</span>\n\t\t\t</button>\n\t<button id=\"delete-all-bindings\" class=\"ms-Button\">\n\t\t\t\t\t\t<span class=\"ms-Button-label\">Delete all bindings (2013 way)</span>\n\t\t\t\t</button>\n</section>"
  language: html
style:
  content: |-
    section.samples {
        margin-top: 20px;
    }

    section.samples .ms-Button, section.setup .ms-Button {
        display: block;
        margin-bottom: 5px;
        margin-left: 20px;
        min-width: 80px;
    }
  language: css
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

  core-js@2.4.1/client/core.min.js
  @types/core-js

  jquery@3.1.1
  @types/jquery@3.3.1