This sample shows how to write custom functions that return entity value data types.

Data types: Custom functions.EXCEL.yaml

name: 'Data types: Custom functions'
description: >-
  This sample shows how to write custom functions that return entity value data
  types.
host: EXCEL
api_set: {}
script:
  content: >
    /**
     * Search for products that match a given substring. Try =SCRIPTLAB.DATATYPESCUSTOMFUNCTIONS.PRODUCTSEARCH("chef", false).
     * @customfunction
     * @param {string} query The string to search for in the sample JSON data.
     * @param {boolean} [completeMatch] Define whether the search should be a match of the whole product name or part of the product name. If omitted, completeMatch = false.
     * @return {Promise<any[][]>} Search results as one or more data type entity values.
     */
    async function productSearch(query: string, completeMatch?: boolean):
    Promise<any[][]> {
      // This function searches a set of sample JSON data for the string entered in the
      // custom function, and then returns the search result as one or more entity values.

      // Set up an error to use if a matching product doesn't exist in the JSON data.
      const notAvailableError = [[{ "type": "Entity", "text": "Chai", "properties": { "Product ID": { "type": "String", "basicValue": "1" }, "Product Name": { "type": "String", "basicValue": "Chai" }, "Quantity Per Unit": { "type": "String", "basicValue": "10 boxes x 20 bags" }, "Unit Price": { "type": "FormattedNumber", "basicValue": 18, "numberFormat": "$* #,##0.00" }, "Discontinued": { "type": "Boolean", "basicValue": false }, "Image": { "type": "WebImage", "address": "https://upload.wikimedia.org/wikipedia/commons/thumb/0/04/Masala_Chai.JPG/320px-Masala_Chai.JPG" } }, "layouts": { "card": { "title": { "property": "Product Name" }, "sections": [{ "layout": "List", "properties": ["Product ID"] }, { "layout": "List", "title": "Quantity and price", "collapsible": true, "collapsed": false, "properties": ["Quantity Per Unit", "Unit Price"] }, { "layout": "List", "title": "Additional information", "collapsed": true, "properties": ["Discontinued"] }], "mainImage": { "property": "Image" } } } }]];

      // Search the sample JSON data for matching product names.
      try {
        if (completeMatch === null) {
          completeMatch = false;
        }

        console.log(`Searching for ${query}...`);
        const searchResult = await searchProduct(query, completeMatch);
        //console.log(searchResult);

        // If the search result is empty, return the error.
        if (searchResult.length == 0) {
          console.log(searchResult.length);
          console.log(notAvailableError);
          console.log(JSON.stringify(notAvailableError));
          return notAvailableError;
        }

        // Create product entities for each of the products returned in the search result.
        const entities = searchResult.map((product) => [makeProductEntity(product)]);
        return entities;
      } catch (error) {
        console.error(error);
      }
    }


    // Helper function to create entities from product properties.

    function makeProductEntity(product?: any) {
      const entity: Excel.EntityCellValue = {
        type: "Entity",
        text: product.productName,
        properties: {
          "Product ID": {
            type: "String",
            basicValue: product.productID.toString() || ""
          },
          "Product Name": {
            type: "String",
            basicValue: product.productName || ""
          },
          "Quantity Per Unit": {
            type: "String",
            basicValue: product.quantityPerUnit || ""
          },
          // Add Unit Price as a formatted number.
          "Unit Price": {
            type: "FormattedNumber",
            basicValue: product.unitPrice,
            numberFormat: "$* #,##0.00"
          },
          Discontinued: {
            type: "Boolean",
            basicValue: product.discontinued || false
          }
        },
        layouts: {
          card: {
            title: { property: "Product Name" },
            sections: [
              {
                layout: "List",
                properties: ["Product ID"]
              },
              {
                layout: "List",
                title: "Quantity and price",
                collapsible: true,
                collapsed: false,
                properties: ["Quantity Per Unit", "Unit Price"]
              },
              {
                layout: "List",
                title: "Additional information",
                collapsed: true,
                properties: ["Discontinued"]
              }
            ]
          }
        }
      };

      // Add image property to the entity and then add it to the card layout.
      if (product.productImage) {
        entity.properties["Image"] = {
          type: "WebImage",
          address: product.productImage || ""
        };
        entity.layouts.card.mainImage = { property: "Image" };
      }

      return entity;
    }


    // Helper function to search the sample JSON product data.

    function searchProduct(query: string, completeMatch: boolean): any {
      const queryUpperCase = query.toUpperCase();
      if (completeMatch === true) {
        return products.filter((p) => p.productName.toUpperCase() === queryUpperCase);
      } else {
        return products.filter((p) => p.productName.toUpperCase().indexOf(queryUpperCase) >= 0);
      }
    }


    /** Sample JSON product data. */

    const products = [
      {
        productID: 1,
        productName: "Chai",
        supplierID: 1,
        categoryID: 1,
        quantityPerUnit: "10 boxes x 20 bags",
        unitPrice: 18,
        discontinued: false,
        productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/0/04/Masala_Chai.JPG/320px-Masala_Chai.JPG"
      },
      {
        productID: 2,
        productName: "Chang",
        supplierID: 1,
        categoryID: 1,
        quantityPerUnit: "24 - 12 oz bottles",
        unitPrice: 19,
        discontinued: false,
        productImage: ""
      },
      {
        productID: 3,
        productName: "Aniseed Syrup",
        supplierID: 1,
        categoryID: 2,
        quantityPerUnit: "12 - 550 ml bottles",
        unitPrice: 10,
        discontinued: false,
        productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/8/81/Maltose_syrup.jpg/185px-Maltose_syrup.jpg"
      },
      {
        productID: 4,
        productName: "Chef Anton's Cajun Seasoning",
        supplierID: 2,
        categoryID: 2,
        quantityPerUnit: "48 - 6 oz jars",
        unitPrice: 22,
        discontinued: false,
        productImage:
          "https://upload.wikimedia.org/wikipedia/commons/thumb/8/82/Kruidenmengeling-spice.jpg/193px-Kruidenmengeling-spice.jpg"
      },
      {
        productID: 5,
        productName: "Chef Anton's Gumbo Mix",
        supplierID: 2,
        categoryID: 2,
        quantityPerUnit: "36 boxes",
        unitPrice: 21.35,
        discontinued: true,
        productImage:
          "https://upload.wikimedia.org/wikipedia/commons/thumb/4/45/Okra_in_a_Bowl_%28Unsplash%29.jpg/180px-Okra_in_a_Bowl_%28Unsplash%29.jpg"
      },
      {
        productID: 6,
        productName: "Grandma's Boysenberry Spread",
        supplierID: 3,
        categoryID: 2,
        quantityPerUnit: "12 - 8 oz jars",
        unitPrice: 25,
        discontinued: false,
        productImage:
          "https://upload.wikimedia.org/wikipedia/commons/thumb/1/10/Making_cranberry_sauce_-_in_the_jar.jpg/90px-Making_cranberry_sauce_-_in_the_jar.jpg"
      },
      {
        productID: 7,
        productName: "Uncle Bob's Organic Dried Pears",
        supplierID: 3,
        categoryID: 7,
        quantityPerUnit: "12 - 1 lb pkgs.",
        unitPrice: 30,
        discontinued: false,
        productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/f/fd/DriedPears.JPG/120px-DriedPears.JPG"
      },
      {
        productID: 8,
        productName: "Northwoods Cranberry Sauce",
        supplierID: 3,
        categoryID: 2,
        quantityPerUnit: "12 - 12 oz jars",
        unitPrice: 40,
        discontinued: false,
        productImage:
          "https://upload.wikimedia.org/wikipedia/commons/thumb/0/07/Making_cranberry_sauce_-_stovetop.jpg/90px-Making_cranberry_sauce_-_stovetop.jpg"
      },
      {
        productID: 9,
        productName: "Mishi Kobe Niku",
        supplierID: 4,
        categoryID: 6,
        quantityPerUnit: "18 - 500 g pkgs.",
        unitPrice: 97,
        discontinued: true,
        productImage: ""
      },
      {
        productID: 10,
        productName: "Ikura",
        supplierID: 4,
        categoryID: 8,
        quantityPerUnit: "12 - 200 ml jars",
        unitPrice: 31,
        discontinued: false,
        productImage: ""
      }
    ];


    const categories = [
      {
        categoryID: 1,
        categoryName: "Beverages",
        description: "Soft drinks, coffees, teas, beers, and ales"
      },
      {
        categoryID: 2,
        categoryName: "Condiments",
        description: "Sweet and savory sauces, relishes, spreads, and seasonings"
      },
      {
        categoryID: 3,
        categoryName: "Confections",
        description: "Desserts, candies, and sweet breads"
      },
      {
        categoryID: 4,
        categoryName: "Dairy Products",
        description: "Cheeses"
      },
      {
        categoryID: 5,
        categoryName: "Grains/Cereals",
        description: "Breads, crackers, pasta, and cereal"
      },
      {
        categoryID: 6,
        categoryName: "Meat/Poultry",
        description: "Prepared meats"
      },
      {
        categoryID: 7,
        categoryName: "Produce",
        description: "Dried fruit and bean curd"
      },
      {
        categoryID: 8,
        categoryName: "Seafood",
        description: "Seaweed and fish"
      }
    ];


    const suppliers = [
      {
        supplierID: 1,
        companyName: "Exotic Liquids",
        contactName: "Charlotte Cooper",
        contactTitle: "Purchasing Manager"
      },
      {
        supplierID: 2,
        companyName: "New Orleans Cajun Delights",
        contactName: "Shelley Burke",
        contactTitle: "Order Administrator"
      },
      {
        supplierID: 3,
        companyName: "Grandma Kelly's Homestead",
        contactName: "Regina Murphy",
        contactTitle: "Sales Representative"
      },
      {
        supplierID: 4,
        companyName: "Tokyo Traders",
        contactName: "Yoshi Nagase",
        contactTitle: "Marketing Manager",
        address: "9-8 Sekimai Musashino-shi"
      },
      {
        supplierID: 5,
        companyName: "Cooperativa de Quesos 'Las Cabras'",
        contactName: "Antonio del Valle Saavedra",
        contactTitle: "Export Administrator"
      },
      {
        supplierID: 6,
        companyName: "Mayumi's",
        contactName: "Mayumi Ohno",
        contactTitle: "Marketing Representative"
      },
      {
        supplierID: 7,
        companyName: "Pavlova, Ltd.",
        contactName: "Ian Devling",
        contactTitle: "Marketing Manager"
      },
      {
        supplierID: 8,
        companyName: "Specialty Biscuits, Ltd.",
        contactName: "Peter Wilson",
        contactTitle: "Sales Representative"
      }
    ];
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  [email protected]/client/core.min.js
  @types/core-js