Set and get values and formulas for a range - Shared with Script Lab

Set and get values - 1.yaml

name: Set and get values - 1
description: Set and get values and formulas for a range
author: MIchaelMainer
host: EXCEL
api_set: {}
script:
    content: |
        $("#setup").click(setup);

        $("#set-value").click(setValue);
        $("#set-values").click(setValues);
        $("#set-formula").click(setFormula);
        $("#set-formulas").click(setFormulas);
        $("#set-formulas-r1c1").click(setFormulasR1C1);
        $("#get-values").click(getValues);
        $("#get-texts").click(getTexts);
        $("#get-formulas").click(getFormulas);

        async function setValue() {
            try {
                await Excel.run(async (context) => {
                    // const sheet = context.workbook.worksheets.getItem("Sample");

                    // const range = sheet.getRange("C3");
                    // range.values = [[ 5 ]];
                    // range.format.autofitColumns();

                    // await context.sync();

                    const selectedRange = context.workbook.getSelectedRange();
                    selectedRange.load("values");
                    await context.sync();

                    // [] [] 
                    var selectedRangeValue = selectedRange.values[0][0];
                    
                    
                    selectedRange.values = [[selectedRangeValue + "; MigrateReady"]];

                    await context.sync(); 
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function setValues() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");

                    const data = [
                        ["Potato Chips", 10, 1.80],
                    ];
                    
                    const range = sheet.getRange("B5:D5");
                    range.values = data;
                    range.format.autofitColumns();

                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function setFormula() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");

                    const range = sheet.getRange("E3");
                    range.formulas = [[ "=C3 * D3" ]];
                    range.format.autofitColumns();

                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function setFormulas() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");

                    const data = [
                        ["Total Price"],
                        ["=C3 * D3"],
                        ["=C4 * D4"],
                        ["=C5 * D5"],
                        ["=SUM(E3:E5)"]
                    ];
                    
                    const range = sheet.getRange("E2:E6");
                    range.formulas = data;
                    range.format.autofitColumns();

                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function setFormulasR1C1() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");

                    const data = [
                        ["Total Price"],
                        ["=R[0]C[-2] * R[0]C[-1]"],
                        ["=R[0]C[-2] * R[0]C[-1]"],
                        ["=R[0]C[-2] * R[0]C[-1]"],
                        ["=SUM(R[-3]C[0]:R[-1]C[0])"]
                    ];
                    
                    const range = sheet.getRange("E2:E6");
                    range.formulasR1C1 = data;
                    range.format.autofitColumns();

                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function getValues() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");
                    const range = sheet.getRange("B2:E6");
                    range.load("values");

                    await context.sync();

                    console.log(JSON.stringify(range.values, null, 4));
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function getTexts() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");
                    const range = sheet.getRange("B2:E6");
                    range.load("text");

                    await context.sync();

                    console.log(JSON.stringify(range.text, null, 4));
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function getFormulas() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");
                    const range = sheet.getRange("B2:E6");
                    range.load("formulas");

                    await context.sync();

                    console.log(JSON.stringify(range.formulas, null, 4));
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }

        async function setup() {
            try {
                await Excel.run(async (context) => {
                    const sheet = await OfficeHelpers.ExcelUtilities
                        .forceCreateSheet(context.workbook, "Sample");
                    
                    const data = [
                        ["Product", "Qty", "Unit Price"],
                        ["Almonds", 2, 7.50],
                        ["Coffee", 1, 34.50],
                        ["Chocolate", 5, 9.56]
                    ];

                    const range = sheet.getRange("B2:D5");
                    range.values = data;
                    range.format.autofitColumns();

                    const header = range.getRow(0);
                    header.format.fill.color = "#4472C4";
                    header.format.font.color = "white";

                    sheet.activate();
                    
                    await context.sync();
                });
            }
            catch (error) {
                OfficeHelpers.UI.notify(error);
                OfficeHelpers.Utilities.log(error);
            }
        }
    language: typescript
template:
    content: |
        <section class="ms-font-m">
            <p>This sample shows how to set and get values and formulas for a range using the Excel API.</p>
        </section>

        <section class="setup ms-font-m">
            <h3>Set up</h3>
            <button id="setup" class="ms-Button">
                <span class="ms-Button-label">Add sample data</span>
            </button>
        </section>

        <section class="samples ms-font-m">
            <h3>Try it out</h3>

            <button id="set-value" class="ms-Button">
                <span class="ms-Button-label">Set value</span>
            </button>
            <button id="set-values" class="ms-Button">
                <span class="ms-Button-label">Set values</span>
            </button>
            <button id="set-formula" class="ms-Button">
                <span class="ms-Button-label">Set formula using A1-style</span>
            </button>
            <button id="set-formulas" class="ms-Button">
                <span class="ms-Button-label">Set formulas using A1-style</span>
            </button>
            <button id="set-formulas-r1c1" class="ms-Button">
                <span class="ms-Button-label">Set formulas using R1C1-style</span>
            </button>

            <br />

            <button id="get-values" class="ms-Button">
                <span class="ms-Button-label">Get values</span>
            </button>
            <button id="get-texts" class="ms-Button">
                <span class="ms-Button-label">Get texts</span>
            </button>
            <button id="get-formulas" class="ms-Button">
                <span class="ms-Button-label">Get formulas</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
    [email protected]/dist/css/fabric.min.css
    [email protected]/dist/css/fabric.components.min.css

    # NPM libraries
    [email protected]/client/core.min.js
    @microsoft/[email protected]/dist/office.helpers.min.js
    [email protected]

    # IntelliSense: @types/library or node_modules paths or URL to d.ts files
    @types/office-js
    @types/core-js
    @microsoft/[email protected]/dist/office.helpers.d.ts
    @types/jquery