Custom Function Sample.EXCEL.yaml

name: Custom Function Sample
description: ''
host: EXCEL
api_set: {}
script:
  content: |-
    /**
     * Create Entity for raw data.
     * @customfunction
     * @param {string} display
     * @param {string} image
     * @param {string} productID
     * @param {string} typeOfProduct
     * @param {string} category
     * @param {number[][]} years
     * @param {number[][]} purchasePrices
     * @param {number[][]} sellprices
     * @param {number[][]} purchaseAmounts
     * @param {number[][]} inventorys
     * @returns The volume of the sphere.
     */
    function createEntity(
      display,
      image,
      productID,
      typeOfProduct,
      category,
      years,
      purchasePrices,
      sellprices,
      purchaseAmounts,
      inventorys
    ) {
      var entity = {
        type: "Entity",
        basicType: "Error",
        basicValue: "#VALUE!",
        text: display + "-" + typeOfProduct
      };

      var elements = [];
      var sumProfit = 0;
      years.forEach((element, index) => {
        var profit = (sellprices[index][0] - purchasePrices[index][0]) * purchaseAmounts[index][0];
        sumProfit += profit;
        var yearEntity = {
          type: "Entity",
          basicType: "Error",
          basicValue: "#VALUE!",
          text: element[0].toString()
        };

        yearEntity["properties"] = {
          "(year)": {
            type: "String",
            basicValue: element[0].toString()
          },
          "Purhcase Price": {
            type: "FormattedNumber",
            basicValue: purchasePrices[index][0],
            numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
          },
          "Sell Price": {
            type: "FormattedNumber",
            basicValue: sellprices[index][0],
            numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
          },
          "Purchase Amount": {
            type: "Double",
            basicValue: purchaseAmounts[index][0]
          },
          Inventory: {
            type: "Double",
            basicValue: inventorys[index][0]
          },
          Profit: {
            type: "FormattedNumber",
            basicValue: profit,
            numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
          }
        };
        elements.push(yearEntity);
      });

      entity["properties"] = {
        display: {
          type: "String",
          basicValue: display
        },
        productID: {
          type: "String",
          basicValue: productID
        },
        typeOfProduct: {
          type: "String",
          basicValue: typeOfProduct
        },
        category: {
          type: "String",
          basicValue: category
        },
        picture: {
          type: "WebImage",
          basicType: "Error",
          basicValue: "#VALUE!",
          address: image
        },
        years: {
          type: "Array",
          basicType: "Error",
          basicValue: "#VALUE!",
          elements: [elements]
        },
        "Amount of Profit": {
          type: "FormattedNumber",
          basicValue: sumProfit,
          numberFormat: '_($* #,##0.00_);_($* (#,##0.00);_($* " -"??_);_(@_)'
        }
      };

      return entity;
    }

    /**
     * get entities of top.
     * @customfunction
     * @param {any[][]} entities
     * @param {string} category
     * @param {number} year
     * @param {number} top
     * @returns {any[][]}The volume of the sphere.
     * 
     */
    function getTopProift(
      entities,
      category,
      year,
      top
    )
    {
      entities = entities.map(x => x[0]).filter(x => x.properties.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]);
    }
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js
  [email protected]/client/core.min.js