Names the selected range using TypeScript.

Name a range.EXCEL.yaml

name: Name a range
description: |-
  Names the selected range
  using TypeScript.
host: EXCEL
api_set: {}
script:
  content: >
    //The event handler of the button to add the name

    $("#addName").click(() => tryCatch(addTheName));

    //Start the event handler of the refersto box, it terminates automatically
    when the app ends

    $(document).ready(function() {
      tryCatch(startSelectionEvent);
    });


    let eventResult;


    async function addTheName() {
      await Excel.run(async (context) => {
        let wb = context.workbook;

        let refersTo: string = $("#cellSelector")
          .val()
          .toString();
        let nameName: string = $("#rangeName")
          .val()
          .toString();

        const namedItem = wb.names.getItemOrNullObject(nameName);
        namedItem.load("name");

        await context.sync();

        if (namedItem.isNullObject) {
          if (nameName.length > 0 && refersTo.length > 0) {
            wb.names.add(nameName, refersTo);
            await context.sync();
          } else {
            console.log("Please provide both a name and a formula");
          }
        } else {
          console.log("Name '" + nameName + "' already exists!");
        }
      });
    }


    async function handleSelectionChange(event) {
      await Excel.run(async (context) => {
        const selAddress = event.address;
        const ws = context.workbook.worksheets.getActiveWorksheet();
        ws.load("name");
        await context.sync();
        let wsName = ws.name;
        if (wsName.indexOf(" ") != -1) {
          wsName = "'" + wsName + "'";
        }
        $("#cellSelector").val("=" + wsName + "!" + convertRangeAddress(selAddress, true, true));
      });
    }


    function convertRangeAddress(addr: String, colAbs: boolean, rowAbs: boolean)
    {
      /* Examples
      AA1234:BC4532
      Sheet1!AA1:DD12
      'Sheet 1'!A12:B20
      */
      var i: number;
      var retAddr: string;
      var workAddr: string;
      var cellCorners: string[];
      var sheetName: string;
      var temp;
      temp = addr.split("!");
      if (temp.length > 1) {
        sheetName = temp[0];
        workAddr = temp[1];
      } else {
        sheetName = "";
        workAddr = temp[0];
      }
      cellCorners = workAddr.split(":");
      for (i = 0; i < cellCorners.length; i++) {
        let pos: number = indexOfFirstDigit(cellCorners[i]);
        if (colAbs) {
          retAddr = "$" + cellCorners[i].substr(0, pos);
        } else {
          retAddr = cellCorners[i].substr(0, pos);
        }

        if (rowAbs) {
          retAddr = retAddr + "$";
        }
        retAddr = retAddr + cellCorners[i].substr(pos, cellCorners[i].length);
        cellCorners[i] = retAddr;
      }
      retAddr = cellCorners.join(":");

      if (sheetName.length > 0) {
        retAddr = sheetName + "!" + retAddr;
      }
      return retAddr;
    }


    function indexOfFirstDigit(input) {
      let i = 0;
      for (; input[i] < "0" || input[i] > "9"; i++);
      return i == input.length ? -1 : i;
    }


    async function startSelectionEvent() {
      await Excel.run(async (context) => {
        const ws = context.workbook.worksheets.getActiveWorksheet();
        const eventResult = ws.onSelectionChanged.add(handleSelectionChange);
        await context.sync();
        console.log("Event handler successfully registered for onSelectionChanged event in the worksheet.");
      });
    }


    async function removeSelectionEvent() {
      await Excel.run(eventResult.context, async (context) => {
        eventResult.remove();
        await context.sync();

        eventResult = null;
        console.log("Event handler successfully removed.");
      });
    }

    /** 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: "\n<section class=\"samples ms-font-m\">\n\t<h3>Name a range</h3>\n\t<p class=\"ms-font-m\">Enter a name, select some cells in the worksheet, then press <b>Insert this name</b>.</p>\n\t<p>Name</p>\n\t<p><input id=\"rangeName\"></input></p>\n\t<p>Refers To (select a range on the sheet)</p>\n\t<p><input id=\"cellSelector\"></input></p>\n\t<button id=\"addName\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Insert this name</span>\n    </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

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

  [email protected]/client/core.min.js
  @types/core-js

  [email protected]
  @types/[email protected]