data-types-references.yaml

order: 7
id: excel-data-types-references
name: 'Data types: Entity values with references'
description: 'This sample shows how to create entity values with references to other entity values. An entity value is a container for data, and this container can reference (or contain) other entities within the original entity. One entity can contain multiple additional entities.'
host: EXCEL
api_set:
    ExcelApi: '1.16'
script:
    content: |
        $("#setup").on("click", () => tryCatch(setup)); 
        $("#add-entities-to-table").on("click", () => tryCatch(addEntitiesToTable));

        async function addEntitiesToTable() {
          // This function creates entity values with references to other entity values. 
          // It retrieves data for each of the employees in the table on the worksheet, 
          // creates entity values for each of those employees, and adds the entities 
          // to the table. Each employee entity references (or contains) other 
          // employee entities. In this scenario, the purpose of one employee 
          // referencing another is to show a managerial hierarchy.
          await Excel.run(async (context) => {
            const employeesTable = context.workbook.tables.getItem("EmployeesTable");

            // Refresh the table column with the entity values.
            employeesTable.columns.getItemOrNullObject("Employee").delete();
            const employeeColumn = employeesTable.columns.add(0, null, "Employee");

            // Get employee data from the table.
            const dataRange = employeesTable.getDataBodyRange();
            dataRange.load("values");
            await context.sync();

            // Create the entities by mapping the employee IDs in table to the sample
            // JSON employee data.
            const entities = dataRange.values.map((rowValues) => {
              // Get employee properties.
              const employee = getEmployee(rowValues[1] /* Employee ID */);

              // Collect all the references that are needed to make the employee entity.
              const references = collectReferences(employee);

              // Create employee entity from employee properties. 
              const employeeEntity = makeEmployeeEntity(
                rowValues[2], /* Last name */
                rowValues[3], /* First name */
                employee,
                references
              );

              // Collect reference values for the employee entity. 
              const referencedValues: any[] = [{ type: "Root" }];
              references.slice(1).forEach((id) => {
                const referencedEmployee = getEmployee(id);
                referencedValues.push(
                  makeEmployeeEntity(referencedEmployee.lastName, referencedEmployee.firstName, referencedEmployee, references)
                );
              });
              employeeEntity.referencedValues = referencedValues;

              return [employeeEntity];
            });

            // Add the complete entities to the Employees Table. 
            employeeColumn.getDataBodyRange().valuesAsJson = entities;
            employeeColumn.getRange().format.autofitColumns();
            await context.sync();
          });
        }

        // Helper function to collect all the references for the employee entity. 
        // A reference means one entity contains another entity.
        function collectReferences(employee) {
          const references: number[] = [];
          collectManagerReferences(employee, references);
          return references;
        }

        // Helper function to collect manager and direct report references for each employee entity.
        function collectManagerReferences(employee, references: number[]) {
          // Confirm references haven't already been collected for this employee.
          if (references.indexOf(employee.employeeID) >= 0) {
            return;
          }

          // Record the reference for the employee.
          ensureReferenceExist(references, employee.employeeID);

          // Record the reference for the manager, if the employee has a manager. 
          if (employee.reportsTo != null) {
            // Get the manager.
            const manager = getEmployee(employee.reportsTo);
            // Collect references for the manager.
            collectManagerReferences(manager, references);
          }

          // Collect references for each of the direct reports of the employee, if any. 
          const directReports = employee.directReports || getDirectReports(employee.employeeID);
          if (directReports.length > 0) {
            directReports.forEach((direct) => collectManagerReferences(direct, references));
          }
        }

        // Helper function to check whether a specific reference ID exists in a list of IDs.  
        // If the ID doesn't exist, add it to the list. 
        function ensureReferenceExist(list: number[], id: number) {
          if (list.indexOf(id) < 0) {
            list.push(id);
          }
        }

        // Create entities from employee properties.  
        function makeEmployeeEntity(lastName: string, firstName: string, employee: any, references: number[]): Excel.EntityCellValue {
          const entity: Excel.EntityCellValue = {
            type: Excel.CellValueType.entity,
            text: `${firstName} ${lastName}`,
            properties: {
              "Employee ID": {
                type: Excel.CellValueType.string,
                basicValue: employee.employeeID.toString()
              },
              "Last Name": {
                type: Excel.CellValueType.string,
                basicValue: lastName
              },
              "First Name": {
                type: Excel.CellValueType.string,
                basicValue: firstName
              },
              Name: {
                type: Excel.CellValueType.string,
                basicValue: `${firstName} ${lastName}`
              },
              Title: {
                type: Excel.CellValueType.string,
                basicValue: employee.title
              }
            },
            layouts: {
              card: {
                title: { property: "Name" },
                sections: [
                  {
                    layout: "List",
                    properties: ["Employee ID", "First Name", "Last Name", "Title"]
                  }
                ]
              }
            }
          };

          // Add manager reference, if the employee has a manager. 
          if (employee.reportsTo != null) {
            entity.properties["Manager"] = {
              type: "Reference",
              reference: references.indexOf(employee.reportsTo)
            };
            entity.layouts.card.sections[0].properties.push("Manager");
          }

          // Add references for direct reports, if any. 
          if (employee.directReports.length > 0) {
            entity.properties["Direct Reports"] = {
              type: Excel.CellValueType.array,
              elements: employee.directReports.map((direct) => {
                return [
                  {
                    type: "Reference",
                    reference: references.indexOf(direct.employeeID)
                  }
                ];
              }),
            };
            entity.layouts.card.sections[0].properties.push("Direct Reports");
          }

          return entity;
        }

        // Get employee properties. 
        function getEmployee(employeeID: number): any {
          // Find the employee in the sample data.
          const employee = employees.find((e) => e.employeeID === employeeID);
          
          // Add direct reports for the employee. 
          employee["directReports"] = getDirectReports(employeeID);
          return employee;
        }

        // Get direct reports of employee. 
        function getDirectReports(employeeID: number): any {
          return employees.filter((e) => e.reportsTo === employeeID);
        }

        /** Set up Sample worksheet with a table of employee data. */  
        async function setup() {
          await Excel.run(async (context) => {
            context.workbook.worksheets.getItemOrNullObject("Sample").delete();
            const sheet = context.workbook.worksheets.add("Sample");

            const employeesTable = sheet.tables.add("A1:D1", true /* hasHeaders */);
            employeesTable.name = "EmployeesTable";

            employeesTable.getHeaderRowRange().values = [["Employee", "EmployeeID", "LastName", "FirstName"]];

            employeesTable.rows.add(
              null /* Add at the end. */,
              employees.map((e) => [null, e.employeeID, e.lastName, e.firstName])
            );

            sheet.getUsedRange().format.autofitColumns();
            sheet.getUsedRange().format.autofitRows();
            sheet.activate();

            await context.sync();
          });
        }

        /** Default helper for invoking an action and handling errors. */ 
        async function tryCatch(callback) {
          try {
            await callback();
          } catch (error) {
            // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
            console.error(error);
          }
        }

        /** Sample JSON employee data. */ 
        const employees = [
          {
            employeeID: 1,
            lastName: "Davolio",
            firstName: "Nancy",
            title: "Vice President, Sales",
            reportsTo: null
          },
          {
            employeeID: 2,
            lastName: "Fuller",
            firstName: "Andrew",
            title: "Sales Representative",
            reportsTo: 1
          },
          {
            employeeID: 3,
            lastName: "Leverling",
            firstName: "Janet",
            title: "Sales Representative",
            reportsTo: 4
          },
          {
            employeeID: 4,
            lastName: "Peacock",
            firstName: "Margaret",
            title: "Sales Manager",
            reportsTo: 1
          },
          {
            employeeID: 5,
            lastName: "Buchanan",
            firstName: "Steven",
            title: "Sales Representative",
            reportsTo: 4
          },
          {
            employeeID: 6,
            lastName: "Suyama",
            firstName: "Michael",
            title: "Sales Representative",
            reportsTo: 4
          },
          {
            employeeID: 7,
            lastName: "King",
            firstName: "Robert",
            title: "Sales Representative",
            reportsTo: 4
          },
          {
            employeeID: 8,
            lastName: "Callahan",
            firstName: "Laura",
            title: "Inside Sales Coordinator",
            reportsTo: 1
          },
          {
            employeeID: 9,
            lastName: "Dodsworth",
            firstName: "Anne",
            title: "Sales Representative",
            reportsTo: 4
          }
        ];
    language: typescript
template:
    content: |
        <section class="ms-font-m">
          <p>This sample shows how to create entity values with references to other entity values. An entity value is a container for data, and this container can reference (or contain) other entities within the original entity. One entity can contain multiple additional entities.</p>
        </section>
        <section class="setup ms-font-m">
          <h3>Set up</h3>
          <button id="setup" class="ms-Button">
            <span class="ms-Button-label">Create table</span>
          </button>
        </section>
        <section class="samples ms-font-m">
          <h3>Try it out</h3>
          <p>To see referenced entities within an entity, take the following steps.</p>
          <ol>
            <li>Select <strong>Add entity values</strong> to add entity values to the table.
              <p><button id="add-entities-to-table" class="ms-Button">
                <span class="ms-Button-label">Add entity values</span>
              </button></p></li>
            <li>Open the <strong>Andrew Fuller</strong> entity card by selecting the icon to the left of this name in the <strong>Employee</strong> column.</li>
            <li>In the <strong>Andrew Fuller</strong> entity card, select the <strong>Manager</strong> field. This opens the referenced entity for <strong>Nancy Davolio</strong>.</li>
            <li>The referenced <strong>Nancy Davolio</strong> entity itself contains multiple referenced entities in the <strong>Direct Reports</strong> field. Select the <strong>Direct Reports</strong> field to explore the referenced entities for all the employees that report to <strong>Nancy Davolio</strong>.</li>
            <li>To navigate back to the original <strong>Andrew Fuller</strong> entity, select the <strong>Back</strong> arrow in the top left corner of the entity card.</li>
          </ol>
          <p>You can also use the <strong>Extract to grid</strong> button in the card modal window, to the right of the <strong>Manager</strong> or <strong>Direct Reports</strong> fields, to add the referenced entities to the table in new columns.</p>
        </section>  
    language: html
style:
    content: |
        section.samples {
            margin-top: 20px;
        }

        section.samples .ms-Button, section.setup .ms-Button {
            display: block;
            margin-bottom: 5px;
            margin-left: 20px;
            min-width: 80px;
        }
    language: css
libraries: |
    https://appsforoffice.microsoft.com/lib/1/hosted/office.js
    @types/office-js

    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