Executes a basic call using the Operational API

Basic Operational Call - 2.EXCEL.yaml

name: Basic Operational Call - 2
description: Executes a basic call using the Operational API
host: EXCEL
api_set: {}
script:
  content: |
    // Get the Operational workbook
    // In a true NPM world, this will be:
    // import Excel from 'excel';
    // const { workbook } = new Excel();
    const { workbook } = new ExcelOp.Excel();

    // Bind button handler to run function
    $("#run").click(run);

    async function run() {
      // YOUR CODE HERE
      var userIds = await getDataSheetColumnA();
      var postsData = await getRawData();
      var row = 1;
      for (var i = 0; i < userIds.values.length; i++) {
        var userId = userIds.values[i][0];
        var data = postsData.filterByNumber(userId);
        row = await setDataToWorksheet(workbook.worksheets.getItem("data"), userId, data, 3, row);
        //console.log("New Row Index:" + row);
      }
    }

    async function getDataSheetColumnA() {
      var worksheet = workbook.worksheets.getItem("data");
      var aCol = worksheet.getRange("A:A");
      var aUsedRange = aCol.getUsedRange(true);
      var numbers = await aUsedRange.retrieve("values");
      return numbers;
    }

    async function getRawData() {
      const data = await fetch("https://jsonplaceholder.typicode.com/posts");
      const jsonArray = await data.json();
      return normalize(jsonArray);
    }

    function normalize(jsonArray: any) {
      return {
        filterByNumber: function(userId) {
          return jsonArray.filter((jsonStr) => {
            return jsonStr.userId == userId;
          });
        },
        print: function() {
          console.log(jsonArray);
        }
      };
    }

    async function setDataToWorksheet(
      worksheet: ExcelOp.Worksheet,
      userId: any,
      postDataCut: any[],
      shown: number,
      startRow: number
    ): Promise<number> {
      var rowIndex = 0;
      var visibleArray = [];
      var visibleArrayDirty = false;
      var invisibleArray = [];
      var invisibleArrayDirty = false;
      postDataCut.forEach((postData) => {
        if (visibleArray.length == 0) {
          visibleArrayDirty = true;
          visibleArray.push([userId, postData.id, postData.title, postData.body]);
        } else if (visibleArray.length < shown) {
          visibleArray.push(["", postData.id, postData.title, postData.body]);
        } else {
          invisibleArrayDirty = true;
          invisibleArray.push(["", postData.id, postData.title, postData.body]);
        }
      });
      if (visibleArrayDirty) {
        if (invisibleArrayDirty == false) {
          var visibleRangeIndex = rangeIndex(startRow, startRow + visibleArray.length - 1);
          //console.log(visibleArray);
          var visibleRange = worksheet.getRange(visibleRangeIndex);
          //console.log(visibleRange);
          await visibleRange.update({
            values: visibleArray,
            format: {
              wrapText: false
            }
          });
          return startRow + visibleArray.length;
        } else {
          var visibleRangeIndex = rangeIndex(startRow, startRow + shown - 1);
          //console.log(visibleRangeIndex);
          //console.log(visibleArray);
          var visibleRange = worksheet.getRange(visibleRangeIndex);
          await visibleRange.update({
            values: visibleArray,
            format: {
              wrapText: false
            }
          });
          
          var invisibleRangeIndex = rangeIndex(startRow + shown, startRow + shown + invisibleArray.length - 1);
          //console.log(invisibleRangeIndex);
          //console.log(invisibleArray);
          var invisibleRange = worksheet.getRange(invisibleRangeIndex);
          await invisibleRange.update({
            values: invisibleArray,
            rowHidden: true,
            format: {
              wrapText: false
            }
          });
          return startRow + shown + invisibleArray.length;
        }
      } else {
        var cell = worksheet.getCell(startRow - 1, 0);
        await cell.update({
          values: [[userId]]
        });
        return startRow + 1;
      }
    }

    function rangeIndex(startRow: number, endRow: number): string {
      return "A" + startRow + ":" + "D" + endRow;
    }
  language: typescript
template:
  content: |
    <p class="ms-font-m">Usability Study Test:</p>
    <button id="run" class="ms-Button">
        <span class="ms-Button-label">Run Task</span>
    </button>
  language: html
style:
  content: ''
  language: css
libraries: >
  https://unpkg.com/@microsoft/office-js@operational/dist/office.experimental.js

  https://unpkg.com/@microsoft/office-js@operational/dist/office.experimental.d.ts


  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


  whatwg-fetch

  @types/whatwg-fetch