Add and get a named range item - Shared with Script Lab

47220047-why-created-name-range-not-able-to-get-named-item-range.yaml

name: 47220047-why-created-name-range-not-able-to-get-named-item-range
description: Add and get a named range item
author: kbrandl
host: EXCEL
api_set: {}
script:
    content: |-
        $("#add-get-named-range").click(() => tryCatch(addGetNamedRange));
        $("#setup").click(() => tryCatch(setup));

        function addGetNamedRange() {
            return Excel.run(function (ctx) {

                // Create named item "MyRange" for the specified range.
                var sheet = ctx.workbook.worksheets.getItem("Sample");
                var myRange = sheet.getRange("A1:E1");
                sheet.names.add("MyRange", myRange);

                return ctx.sync()
                    .then(function () {

                        // Get the range for the named item "MyRange" and load its address property.
                        var myNamedItem = sheet.names.getItem("MyRange");
                        var range = myNamedItem.getRange();
                        range.load("address");

                        return ctx.sync()
                            .then(function () {
                                console.log("Address of range: " + range.address);
                            });
                    });
            });
        }

        async function setup() {
            await Excel.run(async (context) => {
                const sheet = await OfficeHelpers.ExcelUtilities.forceCreateSheet(context.workbook, "Sample");
                const expensesTable = sheet.tables.add("A1:D1", true);
                expensesTable.name = "ExpensesTable";
                expensesTable.getHeaderRowRange().values = [["Date", "Merchant", "Category", "Amount"]];
                const newData = transactions.map(item =>
                    [item.date, item.merchant, item.category, item.amount]);
                expensesTable.rows.add(null, // Row position (null = at the bottom)
                    newData);
                sheet.getUsedRange().format.autofitColumns();
                sheet.getUsedRange().format.autofitRows();
                sheet.activate();

                await context.sync();
            });
        }

        const transactions = [
            {
                "date": "1/1/2017",
                "merchant": "The Phone Company",
                "category": "Communications",
                "amount": "$120"
            },
            {
                "date": "1/1/2017",
                "merchant": "SouthRidge Video",
                "category": "Entertainment",
                "amount": "$40"
            },
            {
                "date": "1/1/2017",
                "merchant": "Coho Winery",
                "category": "Restaurant",
                "amount": "$47"
            },
            {
                "date": "1/2/2017",
                "merchant": "Contoso, Ltd",
                "category": "Shopping",
                "amount": "$56"
            },
            {
                "date": "1/2/2017",
                "merchant": "Contoso, Ltd",
                "category": "Shopping",
                "amount": "$110"
            },
            {
                "date": "1/2/2017",
                "merchant": "Liberty Bakery & Cafe",
                "category": "Groceries",
                "amount": "$27"
            },
            {
                "date": "1/2/2017",
                "merchant": "Liberty Bakery & Cafe",
                "category": "Groceries",
                "amount": "$38"
            },
            {
                "date": "1/2/2017",
                "merchant": "Northwind Electric Cars",
                "category": "Transportation",
                "amount": "$42"
            },
            {
                "date": "1/2/2017",
                "merchant": "Best For You Organics Company",
                "category": "Groceries",
                "amount": "$27"
            }
        ];

        /** Default helper for invoking an action and handling errors. */
        async function tryCatch(callback) {
            try {
                await callback();
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }
    language: typescript
template:
    content: "<section class=\"ms-font-m\">\n    <p>This sample shows how to create a named range item and then get that named range item.</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n    <h3>Setup</h3>\n    <button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Add sample data</span>\n    </button>\n     <h3>Try it out</h3>\n\t<button id=\"add-get-named-range\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Run</span>\n    </button>\n</section>\n\n"
    language: html
style:
    content: "section.samples {\r\n    margin-top: 20px;\r\n}\r\n\r\nsection.samples .ms-Button, section.setup .ms-Button {\r\n    display: block;\r\n    margin-bottom: 5px;\r\n    margin-left: 20px;\r\n    min-width: 80px;\r\n}\r\n"
    language: css
libraries: |
    // Office.js
    https://appsforoffice.microsoft.com/lib/1/hosted/office.js

    // CSS Libraries
    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

    // NPM libraries
    core-js@2.4.1/client/core.min.js
    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js
    jquery@3.1.1

    // IntelliSense: @types/library or node_modules paths or URL to d.ts files
    @types/office-js
    @types/core-js
    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts
    @types/jquery