Create a new worksheet and add data to it (using a 2-dimensional array of data) - Shared with Script Lab

45680289-excel-office-js-api-insert-values-to-a-blank-sheet.yaml

name: 45680289-excel-office-js-api-insert-values-to-a-blank-sheet
description: Create a new worksheet and add data to it (using a 2-dimensional array of data)
author: kbrandl
host: EXCEL
api_set: {}
script:
    content: |
        $("#run_TS").click(run_TS);
        $("#run_JS").click(run_JS);

        async function run_TS() {
            try {
                await Excel.run(async (context) => {

                    // define values that will be inserted into new sheet
                    let values = [["A1", "B1", "C1"], ["A2", "B2", "C2"]]; 

                    // create and activate new sheet
                    let sheets = context.workbook.worksheets;
                    let newSheet = sheets.add("New sheet TS");
                    newSheet.activate();

                    // add data to the new sheet
                    let range = newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1);
                    range.values = values;

                    // sync
                    await context.sync();

                    console.log("Finished with run_TS function");
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        function run_JS() {
            Excel.run(function (context) {

                // define values that will be inserted into new sheet
                var values = [["A1", "B1", "C1"], ["A2", "B2", "C2"]];

                // create and activate new sheet
                var sheets = context.workbook.worksheets;
                var newSheet = sheets.add("New sheet JS");
                newSheet.activate();

                // add data to the new sheet
                var range = newSheet.getRange("A1").getResizedRange(values.length - 1, values[0].length - 1);
                range.values = values;

                // sync        
                return context.sync()
                    .then(function () {
                        console.log("Finished with run_JS function");
                    });
            })
                .catch(function (error) {
                    OfficeHelpers.UI.notify(error);
                    OfficeHelpers.Utilities.log(error);
                });
        }

            
    language: typescript
template:
    content: |
        <section class="ms-font-m">
            <p>This sample shows how to add a new worksheet and insert data using a 2-dimensional array.</p>
        </section>

        <section class="samples ms-font-m">
            <h3>Try it out</h3>
            <button id="run_TS" class="ms-Button">
                <span class="ms-Button-label">Run using TypeScript function</span>
            </button>
            <br/><br/>
            <button id="run_JS" class="ms-Button">
                <span class="ms-Button-label">Run using JavaScript function</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: |
    # 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