Create a new snippet from a blank template.

Data types:cards.EXCEL.yaml

name: 'Data types:cards'
description: Create a new snippet from a blank template.
host: EXCEL
api_set: {}
script:
  content: >
    /**
     * @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.
     * @param {any[][]} [getProduct]
     * @return {Promise<any[][]>} Search results as one or more data type entity values.
     */
    async function productSearch(query: string, completeMatch?: boolean,
    getProduct?: any[][]): 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 = [["error"]];

      // 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, getProduct);
        //console.log(searchResult);

        // 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)]);
        console.log(JSON.stringify(entities))
        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, getProduct:
    any[][]): any {
      const queryUpperCase = query.toUpperCase();
      let products: any[];
      if (getProduct === null) {
        products = productsOriginal;
      } else {
        products = tableToJson(getProduct);
      }

      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 productsOriginal = [
      {
        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"
      }
    ];


    /**
     * 將表格轉換為 JSON 輸出。
     * @customfunction
     * @param range 目標範圍。
     * @returns 表格的 JSON 輸出。
     */
    function tableToJson(range: any[][]): any[] {
      // 建立一個陣列，用來儲存結果
      const result: any[] = [];
      // 取得表格的欄位名稱
      const fieldNames: string[] = range[0];
      // 對目標範圍的每個儲存格進行迴圈
      for (let row = 1; row < range.length; row++) {
        // 建立一個物件，用來儲存這一列的資料
        const obj: any = {};
        for (let col = 0; col < range[row].length; col++) {
          // 將欄位名稱當作屬性名稱，儲存格值當作屬性值
          obj[fieldNames[col]] = range[row][col];
        }
        // 將物件加入結果陣列
        result.push(obj);
      }

      // 將結果陣列轉換為 JSON 字串並回傳
      return result;
    }


    /**
     * 將 JSON 轉換為二維陣列。
     * @customfunction
     * @param json 要轉換的 JSON。
     * @returns 轉換後的二維陣列。
     */
    function jsonToArray(jsonSource: any): any[][] {
      //console.log(jsonSource)
      console.log(JSON.parse(jsonSource));

      const json = JSON.parse(jsonSource);

      // 建立一個陣列，用來儲存結果
      const result: any[][] = [];

      // 取得第一個物件的屬性名稱，作為標題行
      const fieldNames: string[] = Object.keys(json[0]);
      result.push(fieldNames);

      // 對 JSON 中的每個物件進行迴圈
      for (const obj of json) {
        // 建立一個陣列，用來儲存這個物件的屬性值
        const arr: any[] = [];
        // 對這個物件的屬性進行迴圈
        for (const key in obj) {
          // 將屬性值轉換為字串，並加入陣列
          arr.push(obj[key]);
        }
        // 將陣列加入結果陣列
        result.push(arr);
      }

      // 回傳結果陣列
      return result;
    }
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/1/hosted/office.js
  @types/office-js

  [email protected]/dist/css/fabric.min.css
  [email protected]/dist/css/fabric.components.min.css

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

  [email protected]
  @types/[email protected]