Custom Function Sample.EXCEL.yaml

name: Custom Function Sample
description: ''
host: EXCEL
api_set: {}
script:
  content: |
    /**
     * Create Entity from sales data.
     * @customfunction
     * @param {any[][]} data
     * @returns{any[][]} The volume of the sphere.
     */
    function createEntity(data) {
      var propertiesList = [
        "display",
        "image",
        "years",
        "productID",
        "category",
        "purchasePrices",
        "sellprices",
        "purchaseAmounts",
        "inventorys",
        "typeOfProduct"
      ];

      data = data.map((element) => {
        var entity = {};
        propertiesList.forEach((item, index) => {
          entity[item] = element[index];
        });
        return entity;
      });

      data = data.reduce(function(prev, x) {
        (prev[x["productID"]] = prev[x["productID"]] || []).push(x);
        return prev;
      }, {});

      var entites = Object.keys(data).map((key) => {
        var element = data[key][0];
        var entity = {
          type: "Entity",
          basicType: "Error",
          basicValue: "#VALUE!",
          text: element.display
        };
        entity["properties"] = {
          "Product ID": {
            type: "String",
            basicValue: element.productID
          },
          Type: {
            type: "String",
            basicValue: element.typeOfProduct
          },
          "Product Category": {
            type: "String",
            basicValue: element.category
          },
          Image: {
            type: "WebImage",
            basicType: "Error",
            basicValue: "#VALUE!",
            address: element.image
          }
        };
        var yearEntities = [];
        var prevProfit = 0;
        data[key].forEach((item) => {
          var profit = (item.sellprices - item.purchasePrices) * (item.purchaseAmounts - item.inventorys);
          var yearEntity = {
            type: "Entity",
            basicType: "Error",
            basicValue: "#VALUE!",
            text: item.years.toString()
          };

          yearEntity["properties"] = {
            Date: {
              type: "String",
              basicValue: item.years.toString()
            },
            "Purhcase price": {
              type: "FormattedNumber",
              basicValue: item.purchasePrices,
              numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
            },
            "Sell price": {
              type: "FormattedNumber",
              basicValue: item.sellprices,
              numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
            },
            "Purchase amount": {
              type: "Double",
              basicValue: item.purchaseAmounts
            },
            Inventory: {
              type: "FormattedNumber",
              basicValue: item.inventorys,
              numberFormat: "[Red][>=100];[Blue]"
            },
            Profit: {
              type: "FormattedNumber",
              basicValue: profit,
              numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
            }
          };
          yearEntities.push(yearEntity);
          prevProfit = profit;
        });
        entity["properties"]["Years"] = {
          type: "Array",
          basicType: "Error",
          basicValue: "#VALUE!",
          elements: [yearEntities]
        };
        return [entity];
      });

      return entites;
    }

    /**
     * get top profit of different category and year.
     * @customfunction
     * @param {any[][]} entities
     * @param {string} category
     * @param {number} year
     * @param {number} top
     * @returns {any[][]}The volume of the sphere.
     *
     */
    function getTopProfit(entities, category, year, top) {
      entities = entities.map((x) => x[0]).filter((x) => x.properties["Product Category"].basicValue == category);

      entities.sort((a, b) => {
        return (
          b.properties["Years"].elements[0][year - 2017].properties.Profit.basicValue -
          a.properties["Years"].elements[0][year - 2017].properties.Profit.basicValue
        );
      });
      return entities.slice(0, top).map((x) => [x]);
    }

    /**
     * get total profit of each category.
     * @customfunction
     * @param {any[][]} entities
     * @param {string} category
     * @param {number} year
     * @returns {any}The volume of the sphere.
     *
     */
    function getTotalProfit(entities, category, year) {
      entities = entities.map((x) => x[0]).filter((x) => x.properties["Product Category"].basicValue == category);

      var totalProfit = entities.reduce((prev, cur) => {
        return prev + cur.properties["Years"].elements[0][year - 2017].properties.Profit.basicValue;
      }, 0);

      return {
        type: "FormattedNumber",
        basicValue: totalProfit,
        numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
      };
    }
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js
  core-js@2.4.1/client/core.min.js