data-types-custom-functions.yaml

order: 6
id: excel-data-types-custom-functions
name: 'Data types: Custom functions'
description: This sample shows how to write custom functions that return entity value data types.
host: EXCEL
api_set:
    CustomFunctionsRuntime: 1.4
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: "Error",
            errorType: "NotAvailable"
          };

          // 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);

            // If the search result is empty, return the error.
            if (searchResult.length == 0) {
              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

    core-js@2.4.1/client/core.min.js
    @types/core-js