Deletes a range (even rows that are hidden) - Shared with Script Lab

53070021-delete-range.yaml

name: 53070021-delete-range
description: Deletes a range (even rows that are hidden)
author: kbrandl
host: EXCEL
api_set: {}
script:
    content: |
        $("#setup").click(setup);

        $("#delete-range").click(deleteRange);

        async function deleteRange() {
            try {
                await Excel.run(async (context) => {
                    const sheet = context.workbook.worksheets.getItem("Sample");
                    const range = sheet.getRange('A1:KK20000');
                    range.rowHidden = false;
                    range.delete(Excel.DeleteShiftDirection.up);
                    context.sync();
                });
            }
            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", "Total Price"],
                        ["Almonds", 2, 7.50, "=C3 * D3"],
                        ["Coffee", 1, 34.50, "=C4 * D4"],
                        ["Chocolate", 5, 9.56, "=C5 * D5"]
                    ];

                    const range = sheet.getRange("B2:E5");
                    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\">\n    <p>This sample shows how to delete a range (including any hidden rows in the range).</p>\n</section>\n\n<section class=\"setup ms-font-m\">\n    <h3>Set up</h3>\n    <button id=\"setup\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Add sample data</span>\n    </button>\n</section>\n\n<section class=\"samples ms-font-m\">\n    <h3>Try it out</h3>\n\t<button id=\"delete-range\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Delete range</span>\n    </button>\n</section>\n"
    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

    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.min.js
    @microsoft/office-js-helpers@0.7.4/dist/office.helpers.d.ts

    jquery@3.1.1
    @types/jquery@3.3.1