Creates, accesses, and removes named items in a worksheet.

Create, access, and remove (3).EXCEL.yaml

name: 'Create, access, and remove (3)'
description: 'Creates, accesses, and removes named items in a worksheet.'
host: EXCEL
api_set: {}
script:
  content: |
    $("#test").click(() => tryCatch(test));

    async function test() {
      await Excel.run(async (context) => {
        const sheet = context.workbook.worksheets.getActiveWorksheet();

        // setup cell values
        sheet.getRange("A2").values = [[3000]];
        await context.sync(); 

        // clean names:
        const name1 = sheet.names.getItemOrNullObject("name1");
        const name2 = sheet.names.getItemOrNullObject("name2");
        const name3 = sheet.names.getItemOrNullObject("name3");
        const name4 = sheet.names.getItemOrNullObject("name4");
        name1.load();
        name2.load();
        name3.load();
        name4.load();
        await context.sync();
        if (name1.value) name1.delete();
        if (name2.value) name2.delete();
        if (name3.value) name3.delete();
        if (name4.value) name4.delete();
        await context.sync();

        // add names:
        sheet.names.add("name1", "=100+200"); // "value" evaluates well to 300
        sheet.names.add("name2", "=A2"); // "value" evaluates to "Sample!$A$2"
        sheet.names.add("name3", "=name2+100"); // "value" evaluates well to 3100
        sheet.names.add("name4", "=Sample!A2+100"); // "value" evaluates to 100, which is wrong. A2 is considered here as 0.
        await context.sync();

        // show names:
        const namedItems = context.workbook.worksheets.getActiveWorksheet().names.load();
        await context.sync();
        console.log("This worksheet contains " + namedItems.items.length + " named items.");
        for (let i = 0; i < namedItems.items.length; i++) {
          console.log(JSON.stringify(namedItems.items[i])) + "\n";
        }
        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">
        <p>This sample shows how to create, access, and delete named items.</p>
    </section>

    <section class="samples ms-font-m">     
        <button id="test" class="ms-Button">
            <span class="ms-Button-label">test</span>
        </button>
    </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