A balanced view (with best of both Collapsed rows and Expand Rows views)

Balanced Rows.EXCEL.yaml

name: Balanced Rows
description: A balanced view (with best of both Collapsed rows and Expand Rows views)
host: EXCEL
api_set: {}
script:
  content: |
    $("#balanced-rows").click(() => tryCatch(balancedRows));

    async function balancedRows() {
      await Excel.run(async (context) => {
        // Initialize lastCheckedRow as the first row of Excel.
        var lastCheckedRow = 1;
        console.log("lastCheckedRow:" + lastCheckedRow);

        // Get last used row in the sheet
        const sheet = context.workbook.worksheets.getActiveWorksheet();
        var usedRange = sheet.getUsedRange(false);
        usedRange.load("rowCount, format");
        await context.sync();
        var lastRow = usedRange.rowCount;
        console.log("lastRow:" + lastRow);

        // Get first row from where you want the view to compress and row height
        var firstRow = $("#startRow").val();
        console.log("Starting row:" + firstRow);

        var height = $("#rowSize").val();
        console.log("desired row height:" + height);

        // As long as you are in the balanced view range, keep creating the view
        do {
          // Get current active cell row value (i.e. if user moved to another cell or not)
          var activeRange = context.workbook.getActiveCell();
          activeRange.load(["address", "values", "rowIndex"]);
          await context.sync().then(function() {
            // console.log("active cell rowNumber:" + Number(activeRange.rowIndex + 1) );
          });

          var currentRow = activeRange.rowIndex + 1;
          // console.log("currentRow:" + currentRow);

          // If user didnt move out of the row, wait for some more time for him to make a move.
          if (currentRow == lastCheckedRow) {
            // Allow user to navigate in same row before checking again
            var waitOver = false;
            timeOut(waitOver, 2);
          } else if (currentRow > lastRow) {
            // If user moved out of last checked row but the row is outside reading range (exit).
            console.log(
              "current row is out of reading range. Go to used rows and trigger this add in again for a balanced reading view!"
            );

            // Set the lastCheckedRow as currentRow
            lastCheckedRow = currentRow;
          } else if (currentRow != lastCheckedRow) {
            // If user moved out of last checked row and the row is within reading range.
            console.log("current row is different than last checked row. Change View.");
            console.log("currentRow:" + currentRow);
            console.log("lastCheckedRow:" + lastCheckedRow);

            // Collapse all rows (for now copy code here until I find out how to use functions in java script)
            var selectRange = sheet.getRange(firstRow + ":" + lastRow);
            selectRange.format.rowHeight = Number(height);

            // Expand current Row
            var selectedRange = sheet.getRange(currentRow + ":" + currentRow);
            selectedRange.format.autofitRows();

            // Set the lastCheckedRow as currentRow
            lastCheckedRow = currentRow;
          } else {
            console.log("This is interesting! I am curious to know how this happened!");
          }
        } while (lastCheckedRow <= lastRow);
        console.log("Exiting balanced view function!");
      });
    }

    function timeOut(waitOver, waitInSeconds) {
      if (waitOver) {
        // console.log("wait over");
      } else {
        window.setTimeout(timeOut, waitInSeconds);
        waitOver = true;
      }
    }

    /** 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: "<section class=\"ms-font-m\">\n\t<p class=\"ms-font-m\">This is a addin to give you different reading options for excel sheets</p>\n</section>\n\n<section class=\"samples ms-font-m\">\n\t<h3>Reading views</h3>\n\t<p class=\"ms-font-m\">Go to the worksheet where you want to have a reading view and, then press one of the buttons\n\t\tbelow:</p>\n\t<button id=\"balanced-rows\" class=\"ms-Button\">\n        <span class=\"ms-Button-label\">Balanced Rows</span>\n  </button>\n\t<input type=\"number\" id=\"rowSize\" value=12>Enter Row height (default=12)</input><br/><br/>\n\t<input type=\"number\" id=\"startRow\" value=3>Specify Start Row (default=3)</input><br/><br/>\n\t<img src='https://laughtard.com/wp-content/uploads/2017/06/NYCGifathon6.gif'>\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]