How to create your own data type and return as an entity through a custom function. ScriptLab demo.

Excel Custom Data Type Custom Function Demo.EXCEL.yaml

name: Excel Custom Data Type Custom Function Demo
description: >-
  How to create your own data type and return as an entity through a custom
  function. ScriptLab demo.
host: EXCEL
api_set: {}
script:
  content: |
    /**
     * @customfunction
     * @param {string} keyword
     * @param {string} [lookin]
     * @return {any[][]}
     */

    async function getBooks(keyword, lookin) {
      if (lookin === null) {
        lookin = "title";
      }

      keyword = "?" + lookin + "=" + keyword;

      const url = "https://demo.batcoder.com/api/book/" + keyword;

      const response = await fetch(url);
      if (!response.ok) {
        throw new Error(response.statusText);
      }

      const books = await response.json();

      var bookEntities = [];

      for (var i = 0; i < books.length; i++) {
        var book = books[i];

        var authors = [];

        for (var j = 0; j < book.authors.length; j++) {
          var author = book.authors[j];

          var authorBooks = [];

          for (var k = 0; k < author.books.length; k++) {
            var authorBook = author.books[k];

            var authorBookEntity = {
              type: "String",
              basicValue: authorBook
            };
            authorBooks.push([authorBookEntity]);
          }

          var authorEntity = {
            type: "Entity",
            text: author.name,
            properties: {
              Name: {
                type: "String",
                basicValue: author.name
              },
              WebPage: {
                type: "String",
                basicValue: author.url
              },
              Website: {
                type: "String",
                basicValue: author.website
              },
              Image: {
                type: "WebImage",
                address: author.image
              },
              Books: {
                type: "Array",
                elements: authorBooks
              }
            }
          };
          authors.push([authorEntity]);
        }

        var bookEditions = [];
        for (var m = 0; m < book.editions.length; m++) {
          var bookEdition = book.editions[m];

          var bookEditionEntity = {
            type: "String",
            basicValue: bookEdition
          };
          bookEditions.push([bookEditionEntity]);
        }

        var entity = {
          type: "Entity",
          text: book.title,
          properties: {
            Title: {
              type: "String",
              basicValue: book.title
            },
            PublishDate: {
              type: "FormattedNumber",
              basicValue: book.published_on_numeric,
              numberFormat: "mm/dd/yyyy"
            },
            ISBN: {
              type: "String",
              basicValue: book.isbn
            },
            URL: {
              type: "String",
              basicValue: book.url
            },
            CoverImage: {
              type: "WebImage",
              address: book.image
            },
            Authors: {
              type: "Array",
              elements: authors
            }
          }
        };

        if (bookEditions.length > 0) {
          entity.properties["Editions"] = {
            type: "Array",
            elements: bookEditions
          };
        }

        bookEntities.push([entity]);
      }

      return bookEntities;
    }
  language: typescript
libraries: |
  https://appsforoffice.microsoft.com/lib/beta/hosted/office.js
  @types/office-js-preview

  office-ui-fabric-js@1.4.0/dist/css/fabric.min.css
  office-ui-fabric-js@1.4.0/dist/css/fabric.components.min.css

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

  jquery@3.1.1
  @types/jquery@3.3.1