Basic custom function (1).EXCEL.yaml

name: Basic custom function (1)
description: Calculates the volume of a sphere.
host: EXCEL
api_set: {}
script:
  content: |-
    /**
     * Create custom data.
     * @customfunction
     * @param {string} dataType
     * @returns custom data by different type.
     */
    function createCustomData(dataType) {
      switch (dataType) {
        case "Entity": {
          return {
            type: "Entity",
            basicType: "Error",
            basicValue: "#VALUE!",
            text: "My Entity",
            properties: {
              TestDouble: { type: "Double", basicType: "Double", basicValue: 1},
              TestString: { type: "String", basicType: "String", basicValue: "Hello World!" }
            }
          };
        }
        case "FormattedNumber": {
          return {
            type: "FormattedNumber",
            basicValue: 1.23,
            numberFormat: "0.00%"
          }
        }
        case "WebImage": {
          return {
            type: "WebImage",
            basicType: "Error",
            basicValue: "#VALUE!",
            address: "https://upload.wikimedia.org/wikipedia/en/c/cd/Marioparty1.jpg"
          };
        }
        case "Error":{
          return {
            type: "Error",
            basicValue: "#DIV/0!",
            errorType: "Div0"
          }
        }

        default: {
          return "Invalid data type";
        }
      }
    }

    /**
     * get custom data.
     * @customfunction
     * @param {any} value
     * @param {string} attribute
     * @returns {any} The volume of the sphere.
     */
    function getCustomData(value, attribute) {
      if (value.type == "Entity") {
        if (attribute == "text") return value.text;
        else {
          return value.properties[attribute].basicValue;
        }
      } else if (value.type == "FormattedNumber") {
        return value[attribute];
      } else if (value.type == "Error") {
        return value[attribute];
      } else if (value.type == "WebImage") {
        return value[attribute];
      } else {
        return JSON.stringify(value);
      }
    }

    /**
     * Create formatted number.
     * @customfunction
     * @param {number} value
     * @param {string} format(e.g. "0.00%")
     * @returns custom data by different type.
     */
    function createFormattedNumber(value, format) {
      return {
        type: "FormattedNumber",
        basicValue: value,
        numberFormat: format
      }
    }

    /**
     * get entity attribute.
     * @customfunction
     * @param {any} value
     * @param {string} attribute
     * @returns {any} The volume of the sphere.
     */
    function getEntityAttribute(value, attribute) {
      if (value.type == "Entity") {
        if (attribute == "text") return value.text;
        else {
          return value.properties[attribute].basicValue;
        }
      } else {
        return JSON.stringify(value);
      }
    }
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js
  core-js@2.4.1/client/core.min.js

custom function custom data sample.EXCEL.yaml

name: custom function custom data sample
description: ''
host: EXCEL
api_set: {}
script:
  content: |-
    /**
     * Create custom data.
     * @customfunction
     * @param {string} dataType
     * @returns custom data by different type.
     */
    function createCustomData(dataType) {
      switch (dataType) {
        case "Entity": {
          return {
            type: "Entity",
            basicType: "Error",
            basicValue: "#VALUE!",
            text: "My Entity",
            properties: {
              TestDouble: { type: "Double", basicType: "Double", basicValue: 1},
              TestString: { type: "String", basicType: "String", basicValue: "Hello World!" }
            }
          };
        }
        case "FormattedNumber": {
          return {
            type: "FormattedNumber",
            basicValue: 1.23,
            numberFormat: "0.00%"
          }
        }
        case "WebImage": {
          return {
            type: "WebImage",
            basicType: "Error",
            basicValue: "#VALUE!",
            address: "https://upload.wikimedia.org/wikipedia/en/c/cd/Marioparty1.jpg"
          };
        }
        case "Error":{
          return {
            type: "Error",
            basicValue: "#DIV/0!",
            errorType: "Div0"
          }
        }

        default: {
          return "Invalid data type";
        }
      }
    }

    /**
     * get custom data.
     * @customfunction
     * @param {any} value
     * @param {string} attribute
     * @returns {any} The volume of the sphere.
     */
    function getCustomData(value, attribute) {
      if (value.type == "Entity") {
        if (attribute == "text") return value.text;
        else {
          return value.properties[attribute].basicValue;
        }
      } else if (value.type == "FormattedNumber") {
        return value[attribute];
      } else if (value.type == "Error") {
        return value[attribute];
      } else if (value.type == "WebImage") {
        return value[attribute];
      } else {
        return JSON.stringify(value);
      }
    }

    /**
     * Create formatted number.
     * @customfunction
     * @param {number} value
     * @param {string} format(e.g. "0.00%")
     * @returns custom data by different type.
     */
    function createFormattedNumber(value, format) {
      return {
        type: "FormattedNumber",
        basicValue: value,
        numberFormat: format
      }
    }

    /**
     * get entity attribute.
     * @customfunction
     * @param {any} value
     * @param {string} attribute
     * @returns {any} The volume of the sphere.
     */
    function getEntityAttribute(value, attribute) {
      if (value.type == "Entity") {
        if (attribute == "text") return value.text;
        else {
          return value.properties[attribute].basicValue;
        }
      } else {
        return JSON.stringify(value);
      }
    }
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js
  core-js@2.4.1/client/core.min.js