Op Model Study - Task 6 Ricky.EXCEL.yaml

name: Op Model Study - Task 6 Ricky
description: ''
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() {

      const posts = await getPosts();
      const allUserIDs = workbook.worksheets.getItem("data").getUsedRange();

      // Group data into arrays by user ID.
      const allPostsData = await getPostsDataByUser(allUserIDs, posts);

      populateSheet(allUserIDs, allPostsData);

      // Hide all but first 3 posts for a given user.
      await hideExcessPosts();
    }


    async function getPosts(): Promise<Array<Post>> {
      const data = await fetch("https://jsonplaceholder.typicode.com/posts");
      const jsonArray = await data.json(); // Returns an array of posts
      return jsonArray;
    }


    async function getPostsDataByUser(allUserIDs: ExcelOp.Range, posts: Post[]):
    Promise<Array<any>> {

      const { values } = await allUserIDs.retrieve("values");
      const allPostsData = [];

      values.forEach(function (value) {
        let dataByUserId = postsByUserId(posts, value[0]);
        if (dataByUserId.length) {
          dataByUserId.forEach(function (item) {
            allPostsData.push(item);
          })
        } else {
          // No posts were found for the user, but we want 
          // to preserve the user's row in the worksheet.
          allPostsData.push([value[0], null, null, null]);
        }
      })
      return allPostsData;
    }


    function postsByUserId(posts: Post[], id: number): any[] {
      let data = [];
      posts.forEach(function (post, index) {
        if (post.userId === id) {
          // If data has 0 length, this is the 1st row
          // for this user ID, so include it in the data.
          if (!data.length) {
            data.push([
              posts[index].userId,
              posts[index].id,
              posts[index].title,
              posts[index].body
            ]);
          } else {
            // The previous row has the same userId, so pass
            // empty string for the first column.
            data.push([
              "",
              posts[index].id,
              posts[index].title,
              posts[index].body
            ]);
          }
        }
      })
      return data;
    }


    function populateSheet(allUserIDs: ExcelOp.Range, allPostsData: any[]): void
    {
      const finalRange = allUserIDs.getAbsoluteResizedRange(allPostsData.length, 4);
      finalRange.update({
        values: allPostsData,
        format: {
          verticalAlignment: "Top",
        }
      });
      finalRange.format.autofitColumns();
      finalRange.format.autofitRows();
    }


    async function hideExcessPosts(): Promise<void> {

      const userIDRange = workbook.worksheets.getItem("data")
        .getUsedRange().getIntersection("A:A");
      const userIDValues = await userIDRange.retrieve("values");

      let consecutiveBlankRows = 0;
      userIDValues.values.forEach(function (value, index) {

        if (value[0]) {
          // There's an ID in this row, so reset consecutiveBlankRows
          consecutiveBlankRows = 0;
        } else {
          consecutiveBlankRows++;
          if (consecutiveBlankRows > 2) {
            let rowToHide = userIDRange.getCell(index, 1).getAbsoluteResizedRange(1, 4);
            rowToHide.update({
              rowHidden: true
            });
          }
        }
      });
    }


    interface Post {
      userId: number;
      id: number;
      title: string;
      body: string;
    }
  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