Skip to content

Instantly share code, notes, and snippets.

@jakobpn

jakobpn/Data types: Entity values with references.EXCEL.yaml

Last active May 9, 2022 18:29
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jakobpn/e26e0eb3cbc5f23e93a3dde04cf5c5c4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jakobpn/e26e0eb3cbc5f23e93a3dde04cf5c5c4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jakobpn revised this gist May 9, 2022. 1 changed file with 7 additions and 3 deletions.
    10 changes: 7 additions & 3 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -117,8 +117,12 @@ script:
    // Create entities from employee properties.
    function makeEmployeeEntity(lastName: string, firstName: string, employee:
    any, references: number[]): Excel.EntityCellValue {
    function makeEmployeeEntity(
    lastName: string,
    firstName: string,
    employee: any,
    references: number[]
    ): Excel.EntityCellValue {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: `${firstName} ${lastName}`,
    @@ -177,7 +181,7 @@ script:
    reference: references.indexOf(direct.employeeID)
    }
    ];
    }),
    })
    };
    entity.layouts.card.sections[0].properties.push("Direct Reports");
    }
  2. jakobpn revised this gist May 6, 2022. No changes.
  3. jakobpn revised this gist May 6, 2022. 1 changed file with 14 additions and 24 deletions.
    38 changes: 14 additions & 24 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -37,12 +37,12 @@ script:
    // 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] /* LastName */,
    rowValues[3] /* FirstName */,
    employee,
    rowValues[2] /* LastName */,
    rowValues[3] /* FirstName */,
    employee,
    references
    );
    @@ -52,12 +52,7 @@ script:
    references.slice(1).forEach((id) => {
    const referencedEmployee = getEmployee(id);
    referencedValues.push(
    makeEmployeeEntity(
    referencedEmployee.lastName,
    referencedEmployee.firstName,
    referencedEmployee,
    references
    )
    makeEmployeeEntity(referencedEmployee.lastName, referencedEmployee.firstName, referencedEmployee, references)
    );
    });
    employeeEntity.referencedValues = referencedValues;
    @@ -85,7 +80,6 @@ script:
    function collectReferencesM(employee, references: number[]) {
    // Check if references has already been collected for the employee.
    if (references.indexOf(employee.employeeID) > -1) {
    return;
    @@ -123,12 +117,8 @@ script:
    // Create entities from employee properties.
    function makeEmployeeEntity(
    lastName: string,
    firstName: string,
    employee: any,
    references: number[]
    ) {
    function makeEmployeeEntity(lastName: string, firstName: string, employee:
    any, references: number[]): Excel.EntityCellValue {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: `${firstName} ${lastName}`,
    @@ -180,14 +170,14 @@ script:
    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)
    }]
    elements: employee.directReports.map((direct) => {
    return [
    {
    type: "Reference",
    reference: references.indexOf(direct.employeeID)
    }
    ];
    }),
    basicType: Excel.CellValueType.error, //RESTRICTED: What type of value is used for features that are unenlightened
    basicValue: "#VALUE!" //RESTRICTED: What value is used for features that are unenlightened
    };
    entity.layouts.card.sections[0].properties.push("Direct Reports");
    }
  4. jakobpn revised this gist May 6, 2022. 1 changed file with 9 additions and 6 deletions.
    15 changes: 9 additions & 6 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -33,13 +33,18 @@ script:
    // the sample JSON employee data.
    const entities = dataRange.values.map((rowValues) => {
    // Get employee properties.
    const employee = getEmployee(rowValues[1]);
    const employee = getEmployee(rowValues[1] /* Employee ID */);
    // Collect all the references that is needed to make the employee entity.
    // 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[1], rowValues[2], rowValues[3], employee, references);
    const employeeEntity = makeEmployeeEntity(
    rowValues[2] /* LastName */,
    rowValues[3] /* FirstName */,
    employee,
    references
    );
    // Add reference values to employee entity.
    const referencedValues: any[] = [{ type: "Root" }];
    @@ -48,7 +53,6 @@ script:
    const referencedEmployee = getEmployee(id);
    referencedValues.push(
    makeEmployeeEntity(
    referencedEmployee.employeeID,
    referencedEmployee.lastName,
    referencedEmployee.firstName,
    referencedEmployee,
    @@ -120,7 +124,6 @@ script:
    // Create entities from employee properties.
    function makeEmployeeEntity(
    employeeID: number,
    lastName: string,
    firstName: string,
    employee: any,
    @@ -132,7 +135,7 @@ script:
    properties: {
    "Employee ID": {
    type: Excel.CellValueType.string,
    basicValue: employeeID.toString() || ""
    basicValue: employee.employeeID.toString() || ""
    },
    "Last Name": {
    type: Excel.CellValueType.string,
  5. jakobpn revised this gist May 6, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -319,7 +319,7 @@ script:
    ];
    language: typescript
    template:
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values with references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>. Extract the <strong>Manager</strong> field to the grid to see the referenced entities.</p>\n</section>"
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values with references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>. Extract the <strong>Manager</strong> field or the <strong>Direct Reports</strong> field to the grid to see the referenced entities.</p>\n</section>"
    language: html
    style:
    content: |
  6. jakobpn revised this gist May 6, 2022. 1 changed file with 15 additions and 13 deletions.
    28 changes: 15 additions & 13 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -37,7 +37,6 @@ script:
    // Collect all the references that is needed to make the employee entity.
    const references = collectReferences(employee);
    console.log(references);
    // Create employee entity from employee properties.
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, references);
    @@ -58,13 +57,10 @@ script:
    );
    });
    employeeEntity.referencedValues = referencedValues;
    console.log(references);
    return [employeeEntity];
    });
    console.log(JSON.stringify(entities, null, 4));
    // Add the complete entities to the Employees Table.
    employeeColumn.getDataBodyRange().valuesAsJson = entities;
    @@ -79,14 +75,13 @@ script:
    function collectReferences(employee) {
    const references: number[] = [];
    console.log(`Collecting references for: ${employee.employeeID}`);
    collectReferencesM(employee, references);
    console.log(`Collecting: ${employee.employeeID} ${references}`);
    return references;
    }
    function collectReferencesM(employee, references: number[]) {
    // Check if references has already been collected for the employee.
    if (references.indexOf(employee.employeeID) > -1) {
    return;
    @@ -104,8 +99,10 @@ script:
    }
    // Collect references for each of the direct reports of the employee, if any.
    if (employee.directReports) {
    employee.directReports.forEach((direct) => collectReferencesM(direct, references));
    const directReports = employee.directReports || getDirectReports(employee.employeeID);
    if (directReports.length > 0) {
    directReports.forEach((direct) => collectReferencesM(direct, references));
    }
    }
    @@ -180,17 +177,15 @@ script:
    if (employee.directReports.length > 0) {
    entity.properties["Direct Reports"] = {
    type: Excel.CellValueType.array,
    elements: employee.directReports.map((direct) => { return [{
    elements: employee.directReports.map((direct) => {
    return [{
    type: "Reference",
    reference: references.indexOf(direct.employeeID)
    }]
    }),
    basicType: Excel.CellValueType.error, //RESTRICTED: What type of value is used for features that are unenlightened
    basicValue: "#VALUE!" //RESTRICTED: What value is used for features that are unenlightened
    };
    console.log(employee.employeeID);
    console.log(references);
    console.log(entity.properties["Direct Reports"]);
    entity.layouts.card.sections[0].properties.push("Direct Reports");
    }
    @@ -204,11 +199,18 @@ script:
    // Find the employee in the sample data.
    const employee = employees.find((e) => e.employeeID === employeeID);
    // Add direct reports for the employee.
    employee["directReports"] = employees.filter((e) => e.reportsTo === employeeID);
    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. */
    async function setup() {
  7. jakobpn revised this gist May 6, 2022. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -79,7 +79,9 @@ script:
    function collectReferences(employee) {
    const references: number[] = [];
    console.log(`Collecting references for: ${employee.employeeID}`);
    collectReferencesM(employee, references);
    console.log(`Collecting: ${employee.employeeID} ${references}`);
    return references;
    }
  8. jakobpn revised this gist May 6, 2022. 1 changed file with 101 additions and 32 deletions.
    133 changes: 101 additions & 32 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -35,30 +35,36 @@ script:
    // Get employee properties.
    const employee = getEmployee(rowValues[1]);
    // Collect all the references that is needed to make the employee entity.
    const references = collectReferences(employee);
    console.log(references);
    // Create employee entity from employee properties.
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, employee.reportsTo != null ? 1 : null);
    const referencedValues: Excel.ReferencedValue[] = [
    { type: "Root" }
    ];
    // Get manager properties and create manager entity.
    let manager = null;
    if (employee.reportsTo != null) {
    manager = getManager(employee.reportsTo);
    // Create manager entity from manager properties.
    const managerEntity = makeEmployeeEntity(manager.employeeID, manager.lastName, manager.firstName, manager, null);
    referencedValues.push(managerEntity);
    }
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, references);
    // Add reference values to employee entity.
    const referencedValues: any[] = [{ type: "Root" }];
    references.slice(1).forEach((id) => {
    const referencedEmployee = getEmployee(id);
    referencedValues.push(
    makeEmployeeEntity(
    referencedEmployee.employeeID,
    referencedEmployee.lastName,
    referencedEmployee.firstName,
    referencedEmployee,
    references
    )
    );
    });
    employeeEntity.referencedValues = referencedValues;
    console.log(references);
    return [employeeEntity];
    });
    console.log(JSON.stringify(entities, null, 4));
    // Add the complete entities to the Employees Table.
    employeeColumn.getDataBodyRange().valuesAsJson = entities;
    @@ -69,10 +75,58 @@ script:
    }
    // Collect all the references that is needed to make an employee entity.
    function collectReferences(employee) {
    const references: number[] = [];
    collectReferencesM(employee, references);
    return references;
    }
    function collectReferencesM(employee, references: number[]) {
    // Check if references has already been collected for the employee.
    if (references.indexOf(employee.employeeID) > -1) {
    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.
    collectReferencesM(manager, references);
    }
    // Collect references for each of the direct reports of the employee, if any.
    if (employee.directReports) {
    employee.directReports.forEach((direct) => collectReferencesM(direct, references));
    }
    }
    // Check whether a specific id exist in a list of ids. Add the id to the
    list, if it doesn't exist
    function ensureReferenceExist(list: number[], id: number) {
    if (list.indexOf(id) == -1) {
    list.push(id);
    }
    }
    // Create entities from employee properties.
    function makeEmployeeEntity(employeeID: number, lastName: string, firstName:
    string, employee?: any, managerReference?: number) {
    function makeEmployeeEntity(
    employeeID: number,
    lastName: string,
    firstName: string,
    employee: any,
    references: number[]
    ) {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: `${firstName} ${lastName}`,
    @@ -104,20 +158,38 @@ script:
    sections: [
    {
    layout: "List",
    properties: ["First Name", "Last Name", "Title", "Employee ID"]
    properties: ["Employee ID", "First Name", "Last Name", "Title"]
    }
    ]
    }
    }
    };
    // Add manager reference
    if (managerReference != null) {
    // Add manager reference, if the employee has a manager.
    if (employee.reportsTo != null) {
    entity.properties["Manager"] = {
    type: "Reference",
    reference: managerReference
    reference: references.indexOf(employee.reportsTo)
    };
    entity.layouts.card.sections[0].properties = ["First Name", "Last Name", "Title", "Manager", "Employee ID"]
    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)
    }]
    }),
    basicType: Excel.CellValueType.error, //RESTRICTED: What type of value is used for features that are unenlightened
    basicValue: "#VALUE!" //RESTRICTED: What value is used for features that are unenlightened
    };
    console.log(employee.employeeID);
    console.log(references);
    console.log(entity.properties["Direct Reports"]);
    entity.layouts.card.sections[0].properties.push("Direct Reports");
    }
    return entity;
    @@ -127,14 +199,11 @@ script:
    // Get employee properties.
    function getEmployee(employeeID: number): any {
    return employees.find((e) => e.employeeID == employeeID);
    }
    // Get manager properties.
    function getManager(managerID: number): any {
    return employees.find((e) => e.employeeID == managerID);
    // Find the employee in the sample data.
    const employee = employees.find((e) => e.employeeID === employeeID);
    // Add direct reports for the employee.
    employee["directReports"] = employees.filter((e) => e.reportsTo === employeeID);
    return employee;
    }
    @@ -246,7 +315,7 @@ script:
    ];
    language: typescript
    template:
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values with references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>.</p>\n</section>"
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values with references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>. Extract the <strong>Manager</strong> field to the grid to see the referenced entities.</p>\n</section>"
    language: html
    style:
    content: |
  9. jakobpn revised this gist May 3, 2022. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -42,7 +42,7 @@ script:
    { type: "Root" }
    ];
    // Get manager properties.
    // Get manager properties and create manager entity.
    let manager = null;
    if (employee.reportsTo != null) {
    manager = getManager(employee.reportsTo);
  10. jakobpn revised this gist May 3, 2022. 1 changed file with 2 additions and 3 deletions.
    5 changes: 2 additions & 3 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -36,7 +36,7 @@ script:
    const employee = getEmployee(rowValues[1]);
    // Create employee entity from employee properties.
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, 1);
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, employee.reportsTo != null ? 1 : null);
    const referencedValues: Excel.ReferencedValue[] = [
    { type: "Root" }
    @@ -57,7 +57,6 @@ script:
    employeeEntity.referencedValues = referencedValues;
    return [employeeEntity];
    });
    // Add the complete entities to the Employees Table.
    @@ -118,7 +117,7 @@ script:
    type: "Reference",
    reference: managerReference
    };
    entity.layouts.card.sections[0].properties.push("Manager");
    entity.layouts.card.sections[0].properties = ["First Name", "Last Name", "Title", "Manager", "Employee ID"]
    }
    return entity;
  11. jakobpn revised this gist May 2, 2022. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -32,28 +32,28 @@ script:
    // Set up the entities by mapping the employee IDs in table to
    // the sample JSON employee data.
    const entities = dataRange.values.map((rowValues) => {
    // Get employee properties
    // Get employee properties.
    const employee = getEmployee(rowValues[1]);
    // Create employee entity from employee properties
    // Create employee entity from employee properties.
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, 1);
    const referencedValues: Excel.ReferencedValue[] = [
    { type: "Root" }
    ];
    // Get manager properties
    // Get manager properties.
    let manager = null;
    if (employee.reportsTo != null) {
    manager = getManager(employee.reportsTo);
    // Create manager entity from manager properties
    const managerEntity = makeEmployeeEntity(manager.managerID, manager.lastName, manager.firstName, manager, null);
    // Create manager entity from manager properties.
    const managerEntity = makeEmployeeEntity(manager.employeeID, manager.lastName, manager.firstName, manager, null);
    referencedValues.push(managerEntity);
    }
    // Add reference values to employee entity
    // Add reference values to employee entity.
    employeeEntity.referencedValues = referencedValues;
    return [employeeEntity];
  12. jakobpn revised this gist May 2, 2022. 1 changed file with 42 additions and 5 deletions.
    47 changes: 42 additions & 5 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -35,8 +35,29 @@ script:
    // Get employee properties
    const employee = getEmployee(rowValues[1]);
    // Create entities from employee properties
    return [makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee)];
    // Create employee entity from employee properties
    const employeeEntity = makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee, 1);
    const referencedValues: Excel.ReferencedValue[] = [
    { type: "Root" }
    ];
    // Get manager properties
    let manager = null;
    if (employee.reportsTo != null) {
    manager = getManager(employee.reportsTo);
    // Create manager entity from manager properties
    const managerEntity = makeEmployeeEntity(manager.managerID, manager.lastName, manager.firstName, manager, null);
    referencedValues.push(managerEntity);
    }
    // Add reference values to employee entity
    employeeEntity.referencedValues = referencedValues;
    return [employeeEntity];
    });
    // Add the complete entities to the Employees Table.
    @@ -52,7 +73,7 @@ script:
    // Create entities from employee properties.
    function makeEmployeeEntity(employeeID: number, lastName: string, firstName:
    string, employee?: any) {
    string, employee?: any, managerReference?: number) {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: `${firstName} ${lastName}`,
    @@ -88,9 +109,18 @@ script:
    }
    ]
    }
    },
    referencedValues: [{ type: "Root" }]
    }
    };
    // Add manager reference
    if (managerReference != null) {
    entity.properties["Manager"] = {
    type: "Reference",
    reference: managerReference
    };
    entity.layouts.card.sections[0].properties.push("Manager");
    }
    return entity;
    }
    @@ -102,6 +132,13 @@ script:
    }
    // Get manager properties.
    function getManager(managerID: number): any {
    return employees.find((e) => e.employeeID == managerID);
    }
    /** Set up Sample worksheet. */
    async function setup() {
  13. jakobpn revised this gist May 2, 2022. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    name: 'Data types: Entity values with references'
    description: >-
    This sample shows how to create entity values that has references to other
    entity values.
    This sample shows how to create entity values with references to other entity
    values.
    host: EXCEL
    api_set: {}
    script:
    @@ -210,7 +210,7 @@ script:
    ];
    language: typescript
    template:
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values that has references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>.</p>\n</section>"
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values with references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>.</p>\n</section>"
    language: html
    style:
    content: |
  14. jakobpn revised this gist May 2, 2022. 1 changed file with 54 additions and 75 deletions.
    129 changes: 54 additions & 75 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -36,14 +36,14 @@ script:
    const employee = getEmployee(rowValues[1]);
    // Create entities from employee properties
    return [makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3],employee)];
    return [makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3], employee)];
    });
    // Add the complete entities to the Employees Table.
    employeeColumn.getDataBodyRange().valuesAsJson = entities;
    employeeColumn.getRange().format.autofitColumns();
    await context.sync();
    });
    }
    @@ -69,31 +69,27 @@ script:
    type: Excel.CellValueType.string,
    basicValue: firstName || ""
    },
    "Name": {
    Name: {
    type: Excel.CellValueType.string,
    basicValue: `${firstName || ""} ${lastName || ""}`
    },
    "Title": {
    Title: {
    type: Excel.CellValueType.string,
    basicValue: employee.title || ""
    },
    }
    },
    layouts: {
    card: {
    title: { property: "Name" },
    sections: [
    {
    layout: "List",
    properties: [
    "First Name",
    "Last Name",
    "Title",
    "Employee ID"
    ]
    properties: ["First Name", "Last Name", "Title", "Employee ID"]
    }
    ]
    }
    }
    },
    referencedValues: [{ type: "Root" }]
    };
    return entity;
    }
    @@ -149,89 +145,72 @@ script:
    const employees = [
    {
    "employeeID": 1,
    "lastName": "Davolio",
    "firstName": "Nancy",
    "title": "Sales Representative",
    "reportsTo": 2
    employeeID: 1,
    lastName: "Davolio",
    firstName: "Nancy",
    title: "Sales Representative",
    reportsTo: 2
    },
    {
    "employeeID": 2,
    "lastName": "Fuller",
    "firstName": "Andrew",
    "title": "Vice President, Sales",
    "reportsTo": null
    employeeID: 2,
    lastName: "Fuller",
    firstName: "Andrew",
    title: "Vice President, Sales",
    reportsTo: null
    },
    {
    "employeeID": 3,
    "lastName": "Leverling",
    "firstName": "Janet",
    "title": "Sales Representative",
    "reportsTo": 2
    employeeID: 3,
    lastName: "Leverling",
    firstName: "Janet",
    title: "Sales Representative",
    reportsTo: 2
    },
    {
    "employeeID": 4,
    "lastName": "Peacock",
    "firstName": "Margaret",
    "title": "Sales Representative",
    "reportsTo": 2
    employeeID: 4,
    lastName: "Peacock",
    firstName: "Margaret",
    title: "Sales Representative",
    reportsTo: 2
    },
    {
    "employeeID": 5,
    "lastName": "Buchanan",
    "firstName": "Steven",
    "title": "Sales Manager",
    "reportsTo": 2
    employeeID: 5,
    lastName: "Buchanan",
    firstName: "Steven",
    title: "Sales Manager",
    reportsTo: 2
    },
    {
    "employeeID": 6,
    "lastName": "Suyama",
    "firstName": "Michael",
    "title": "Sales Representative",
    "reportsTo": 5
    employeeID: 6,
    lastName: "Suyama",
    firstName: "Michael",
    title: "Sales Representative",
    reportsTo: 5
    },
    {
    "employeeID": 7,
    "lastName": "King",
    "firstName": "Robert",
    "title": "Sales Representative",
    "reportsTo": 5
    employeeID: 7,
    lastName: "King",
    firstName: "Robert",
    title: "Sales Representative",
    reportsTo: 5
    },
    {
    "employeeID": 8,
    "lastName": "Callahan",
    "firstName": "Laura",
    "title": "Inside Sales Coordinator",
    "reportsTo": 2
    employeeID: 8,
    lastName: "Callahan",
    firstName: "Laura",
    title: "Inside Sales Coordinator",
    reportsTo: 2
    },
    {
    "employeeID": 9,
    "lastName": "Dodsworth",
    "firstName": "Anne",
    "title": "Sales Representative",
    "reportsTo": 5
    employeeID: 9,
    lastName: "Dodsworth",
    firstName: "Anne",
    title: "Sales Representative",
    reportsTo: 5
    }
    ];
    language: typescript
    template:
    content: |-
    <section class="ms-font-m">
    <p>This sample shows how to create entity values for each row in a table. An entity value is a container for data types, similar to an object in object-oriented programming.</p>
    <p>In particular, this sample highlights the card layout options of an entity value, including the title, an image, collapsible sections, and nested entity values.</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>
    <button id="addEntitiesToTable" class="ms-Button">
    <span class="ms-Button-label">Add entity values</span>
    </button>
    <p>To see the entity value, click the icon to the left of the title in the <strong>Product</strong> column after selecting <strong>Add entity values</strong>.</p>
    </section>
    content: "<section class=\"ms-font-m\">\n\t<p>This sample shows how to create entity values that has references to other entity values.</p>\n</section>\n<section class=\"setup ms-font-m\">\n\t<h3>Set up</h3>\n\t<button id=\"setup\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Create table</span>\n </button>\n</section>\n<section class=\"samples ms-font-m\">\n\t<h3>Try it out</h3>\n\t<button id=\"addEntitiesToTable\" class=\"ms-Button\">\n <span class=\"ms-Button-label\">Add entity values</span>\n </button>\n\t<p>To see the entity value, click the icon to the left of the title in the <strong>Employee</strong> column after\n\t\tselecting <strong>Add entity values</strong>.</p>\n</section>"
    language: html
    style:
    content: |
  15. jakobpn revised this gist May 2, 2022. No changes.
  16. jakobpn revised this gist May 2, 2022. 1 changed file with 7 additions and 2 deletions.
    9 changes: 7 additions & 2 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -80,11 +80,16 @@ script:
    },
    layouts: {
    card: {
    title: { property: "First Name" },
    title: { property: "Name" },
    sections: [
    {
    layout: "List",
    properties: ["Employee ID"]
    properties: [
    "First Name",
    "Last Name",
    "Title",
    "Employee ID"
    ]
    }
    ]
    }
  17. jakobpn revised this gist May 2, 2022. 1 changed file with 10 additions and 2 deletions.
    12 changes: 10 additions & 2 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -55,7 +55,7 @@ script:
    string, employee?: any) {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: firstName,
    text: `${firstName} ${lastName}`,
    properties: {
    "Employee ID": {
    type: Excel.CellValueType.string,
    @@ -69,6 +69,14 @@ script:
    type: Excel.CellValueType.string,
    basicValue: firstName || ""
    },
    "Name": {
    type: Excel.CellValueType.string,
    basicValue: `${firstName || ""} ${lastName || ""}`
    },
    "Title": {
    type: Excel.CellValueType.string,
    basicValue: employee.title || ""
    },
    },
    layouts: {
    card: {
    @@ -100,7 +108,7 @@ script:
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");
    const employeesTable = sheet.tables.add("A1:C1", true /*hasHeaders*/);
    const employeesTable = sheet.tables.add("A1:D1", true /*hasHeaders*/);
    employeesTable.name = "EmployeesTable";
    employeesTable.getHeaderRowRange().values = [["Employee", "EmployeeID", "LastName", "FirstName"]];
  18. jakobpn revised this gist May 2, 2022. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -43,6 +43,7 @@ script:
    employeeColumn.getDataBodyRange().valuesAsJson = entities;
    employeeColumn.getRange().format.autofitColumns();
    await context.sync();
    });
    }
  19. jakobpn revised this gist May 2, 2022. 1 changed file with 100 additions and 162 deletions.
    262 changes: 100 additions & 162 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -13,206 +13,82 @@ script:
    async function addEntitiesToTable() {
    await Excel.run(async (context) => {
    // This method retrieves data for each of the existing products in the table,
    // creates entity values for each of those products, and adds the entities
    // This method retrieves data for each of the existing employees in the table,
    // creates entity values for each of those employees, and adds the entities
    // to the table.
    const productsTable = context.workbook.tables.getItem("ProductsTable");
    const employeesTable = context.workbook.tables.getItem("EmployeesTable");
    // Add a new column to the table for the entity values.
    productsTable.columns.getItemOrNullObject("Product").delete();
    const productColumn = productsTable.columns.add(0, null, "Product");
    employeesTable.columns.getItemOrNullObject("Employee").delete();
    const employeeColumn = employeesTable.columns.add(0, null, "Employee");
    // Get product data from the table.
    const dataRange = productsTable.getDataBodyRange();
    // Get employee data from the table.
    const dataRange = employeesTable.getDataBodyRange();
    dataRange.load("values");
    await context.sync();
    // Set up the entities by mapping the product names to
    // the sample JSON product data.
    // Set up the entities by mapping the employee IDs in table to
    // the sample JSON employee data.
    const entities = dataRange.values.map((rowValues) => {
    // Get products and product properties.
    const product = getProduct(rowValues[1]);
    // Get employee properties
    const employee = getEmployee(rowValues[1]);
    // Get product categories and category properties.
    const category = product ? getCategory(product.categoryID) : null;
    // Get product suppliers and supplier properties.
    const supplier = product ? getSupplier(product.supplierID) : null;
    // Create entities by combining product, category, and supplier properties.
    return [makeProductEntity(rowValues[1], rowValues[2], product, category, supplier)];
    // Create entities from employee properties
    return [makeEmployeeEntity(rowValues[1], rowValues[2], rowValues[3],employee)];
    });
    // Add the complete entities to the Products Table.
    productColumn.getDataBodyRange().valuesAsJson = entities;
    // Add the complete entities to the Employees Table.
    employeeColumn.getDataBodyRange().valuesAsJson = entities;
    productColumn.getRange().format.autofitColumns();
    employeeColumn.getRange().format.autofitColumns();
    await context.sync();
    });
    }
    // Create entities from product properties.
    // Create entities from employee properties.
    function makeProductEntity(productID: number, productName: string, product?:
    any, category?: any, supplier?: any) {
    function makeEmployeeEntity(employeeID: number, lastName: string, firstName:
    string, employee?: any) {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    text: firstName,
    properties: {
    "Product ID": {
    "Employee ID": {
    type: Excel.CellValueType.string,
    basicValue: productID.toString() || ""
    basicValue: employeeID.toString() || ""
    },
    "Product Name": {
    "Last Name": {
    type: Excel.CellValueType.string,
    basicValue: productName || ""
    basicValue: lastName || ""
    },
    "Quantity Per Unit": {
    "First Name": {
    type: Excel.CellValueType.string,
    basicValue: product.quantityPerUnit || ""
    basicValue: firstName || ""
    },
    // Add Unit Price as a formatted number.
    "Unit Price": {
    type: Excel.CellValueType.formattedNumber,
    basicValue: product.unitPrice,
    numberFormat: "$* #,##0.00"
    },
    Discontinued: {
    type: Excel.CellValueType.boolean,
    basicValue: product.discontinued || false
    }
    },
    layouts: {
    card: {
    title: { property: "Product Name" },
    title: { property: "First 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"]
    properties: ["Employee ID"]
    }
    ]
    }
    }
    };
    // Add image property to the entity and then add it to the card layout.
    if (product.productImage) {
    entity.properties["Image"] = {
    type: Excel.CellValueType.webImage,
    address: product.productImage || ""
    };
    entity.layouts.card.mainImage = { property: "Image" };
    }
    // Add a nested entity for the product category.
    if (category) {
    entity.properties["Category"] = {
    type: Excel.CellValueType.entity,
    text: category.categoryName,
    properties: {
    "Category ID": {
    type: Excel.CellValueType.double,
    basicValue: category.categoryID,
    propertyMetadata: {
    // Exclude the category ID property from the card view and auto complete.
    excludeFrom: {
    cardView: true,
    autoComplete: true
    }
    }
    },
    "Category Name": {
    type: Excel.CellValueType.string,
    basicValue: category.categoryName || ""
    },
    Description: {
    type: Excel.CellValueType.string,
    basicValue: category.description || ""
    }
    }
    };
    // Add nested product category to the card layout.
    entity.layouts.card.sections[0].properties.push("Category");
    }
    // Add a nested entity for the supplier.
    if (supplier) {
    entity.properties["Supplier"] = {
    type: Excel.CellValueType.entity,
    text: supplier.companyName,
    properties: {
    "Supplier ID": {
    type: Excel.CellValueType.double,
    basicValue: supplier.supplierID
    },
    "Company Name": {
    type: Excel.CellValueType.string,
    basicValue: supplier.companyName || ""
    },
    "Contact Name": {
    type: Excel.CellValueType.string,
    basicValue: supplier.contactName || ""
    },
    "Contact Title": {
    type: Excel.CellValueType.string,
    basicValue: supplier.contactTitle || ""
    }
    },
    layouts: {
    card: {
    title: { property: "Company Name" },
    sections: [
    {
    layout: "List",
    properties: ["Supplier ID", "Company Name", "Contact Name", "Contact Title"]
    }
    ]
    }
    }
    };
    // Add nested product supplier to the card layout.
    entity.layouts.card.sections[2].properties.push("Supplier");
    }
    return entity;
    }
    // Get products and product properties.
    function getProduct(productID: number): any {
    return products.find((p) => p.productID == productID);
    }
    // Get product categories and category properties.
    // Get employee properties.
    function getCategory(categoryID: number): any {
    return categories.find((c) => c.categoryID == categoryID);
    }
    // Get product suppliers and supplier properties.
    function getSupplier(supplierID: number): any {
    return suppliers.find((s) => s.supplierID == supplierID);
    function getEmployee(employeeID: number): any {
    return employees.find((e) => e.employeeID == employeeID);
    }
    @@ -223,14 +99,14 @@ script:
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");
    const productsTable = sheet.tables.add("A1:C1", true /*hasHeaders*/);
    productsTable.name = "ProductsTable";
    const employeesTable = sheet.tables.add("A1:C1", true /*hasHeaders*/);
    employeesTable.name = "EmployeesTable";
    productsTable.getHeaderRowRange().values = [["Product", "ProductID", "ProductName"]];
    employeesTable.getHeaderRowRange().values = [["Employee", "EmployeeID", "LastName", "FirstName"]];
    productsTable.rows.add(
    employeesTable.rows.add(
    null /*add at the end*/,
    products.map((p) => [null, p.productID, p.productName])
    employees.map((e) => [null, e.employeeID, e.lastName, e.firstName])
    );
    sheet.getUsedRange().format.autofitColumns();
    @@ -258,8 +134,70 @@ script:
    /** Sample JSON product data. */
    const employees = [
    ]
    {
    "employeeID": 1,
    "lastName": "Davolio",
    "firstName": "Nancy",
    "title": "Sales Representative",
    "reportsTo": 2
    },
    {
    "employeeID": 2,
    "lastName": "Fuller",
    "firstName": "Andrew",
    "title": "Vice President, Sales",
    "reportsTo": null
    },
    {
    "employeeID": 3,
    "lastName": "Leverling",
    "firstName": "Janet",
    "title": "Sales Representative",
    "reportsTo": 2
    },
    {
    "employeeID": 4,
    "lastName": "Peacock",
    "firstName": "Margaret",
    "title": "Sales Representative",
    "reportsTo": 2
    },
    {
    "employeeID": 5,
    "lastName": "Buchanan",
    "firstName": "Steven",
    "title": "Sales Manager",
    "reportsTo": 2
    },
    {
    "employeeID": 6,
    "lastName": "Suyama",
    "firstName": "Michael",
    "title": "Sales Representative",
    "reportsTo": 5
    },
    {
    "employeeID": 7,
    "lastName": "King",
    "firstName": "Robert",
    "title": "Sales Representative",
    "reportsTo": 5
    },
    {
    "employeeID": 8,
    "lastName": "Callahan",
    "firstName": "Laura",
    "title": "Inside Sales Coordinator",
    "reportsTo": 2
    },
    {
    "employeeID": 9,
    "lastName": "Dodsworth",
    "firstName": "Anne",
    "title": "Sales Representative",
    "reportsTo": 5
    }
    ];
    language: typescript
    template:
    content: |-
  20. jakobpn revised this gist May 2, 2022. 1 changed file with 3 additions and 306 deletions.
    309 changes: 3 additions & 306 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -257,312 +257,9 @@ script:
    /** 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: ""
    },
    {
    productID: 11,
    productName: "Queso Cabrales",
    supplierID: 5,
    categoryID: 4,
    quantityPerUnit: "1 kg pkg.",
    unitPrice: 21,
    discontinued: false,
    productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/9/96/Tilsit_cheese.jpg/190px-Tilsit_cheese.jpg"
    },
    {
    productID: 12,
    productName: "Queso Manchego La Pastora",
    supplierID: 5,
    categoryID: 4,
    quantityPerUnit: "10 - 500 g pkgs.",
    unitPrice: 38,
    discontinued: false,
    productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/5/59/Manchego.jpg/177px-Manchego.jpg"
    },
    {
    productID: 13,
    productName: "Konbu",
    supplierID: 6,
    categoryID: 8,
    quantityPerUnit: "2 kg box",
    unitPrice: 6,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 14,
    productName: "Tofu",
    supplierID: 6,
    categoryID: 7,
    quantityPerUnit: "40 - 100 g pkgs.",
    unitPrice: 23.25,
    discontinued: false,
    productImage:
    "https://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Korean.food-Dubu.gui-01.jpg/120px-Korean.food-Dubu.gui-01.jpg"
    },
    {
    productID: 15,
    productName: "Genen Shouyu",
    supplierID: 6,
    categoryID: 2,
    quantityPerUnit: "24 - 250 ml bottles",
    unitPrice: 15.5,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 16,
    productName: "Pavlova",
    supplierID: 7,
    categoryID: 3,
    quantityPerUnit: "32 - 500 g boxes",
    unitPrice: 17.45,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 17,
    productName: "Alice Mutton",
    supplierID: 7,
    categoryID: 6,
    quantityPerUnit: "20 - 1 kg tins",
    unitPrice: 39,
    discontinued: true,
    productImage: ""
    },
    {
    productID: 18,
    productName: "Carnarvon Tigers",
    supplierID: 7,
    categoryID: 8,
    quantityPerUnit: "16 kg pkg.",
    unitPrice: 62.5,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 19,
    productName: "Teatime Chocolate Biscuits",
    supplierID: 8,
    categoryID: 3,
    quantityPerUnit: "10 boxes x 12 pieces",
    unitPrice: 9.2,
    discontinued: false,
    productImage:
    "https://upload.wikimedia.org/wikipedia/commons/thumb/d/df/Macau_Koi_Kei_Bakery_Almond_Biscuits_2.JPG/120px-Macau_Koi_Kei_Bakery_Almond_Biscuits_2.JPG"
    },
    {
    productID: 20,
    productName: "Sir Rodney's Marmalade",
    supplierID: 8,
    categoryID: 3,
    quantityPerUnit: "30 gift boxes",
    unitPrice: 81,
    discontinued: false,
    productImage:
    "https://upload.wikimedia.org/wikipedia/commons/thumb/3/30/Homemade_marmalade%2C_England.jpg/135px-Homemade_marmalade%2C_England.jpg"
    }
    ];
    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"
    }
    ];
    const employees = [
    ]
    language: typescript
    template:
    content: |-
  21. jakobpn created this gist May 2, 2022.
    611 changes: 611 additions & 0 deletions Data types: Entity values with references.EXCEL.yaml
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,611 @@
    name: 'Data types: Entity values with references'
    description: >-
    This sample shows how to create entity values that has references to other
    entity values.
    host: EXCEL
    api_set: {}
    script:
    content: >
    $("#setup").click(() => tryCatch(setup));
    $("#addEntitiesToTable").click(() => tryCatch(addEntitiesToTable));
    async function addEntitiesToTable() {
    await Excel.run(async (context) => {
    // This method retrieves data for each of the existing products in the table,
    // creates entity values for each of those products, and adds the entities
    // to the table.
    const productsTable = context.workbook.tables.getItem("ProductsTable");
    // Add a new column to the table for the entity values.
    productsTable.columns.getItemOrNullObject("Product").delete();
    const productColumn = productsTable.columns.add(0, null, "Product");
    // Get product data from the table.
    const dataRange = productsTable.getDataBodyRange();
    dataRange.load("values");
    await context.sync();
    // Set up the entities by mapping the product names to
    // the sample JSON product data.
    const entities = dataRange.values.map((rowValues) => {
    // Get products and product properties.
    const product = getProduct(rowValues[1]);
    // Get product categories and category properties.
    const category = product ? getCategory(product.categoryID) : null;
    // Get product suppliers and supplier properties.
    const supplier = product ? getSupplier(product.supplierID) : null;
    // Create entities by combining product, category, and supplier properties.
    return [makeProductEntity(rowValues[1], rowValues[2], product, category, supplier)];
    });
    // Add the complete entities to the Products Table.
    productColumn.getDataBodyRange().valuesAsJson = entities;
    productColumn.getRange().format.autofitColumns();
    await context.sync();
    });
    }
    // Create entities from product properties.
    function makeProductEntity(productID: number, productName: string, product?:
    any, category?: any, supplier?: any) {
    const entity: Excel.EntityCellValue = {
    type: Excel.CellValueType.entity,
    text: productName,
    properties: {
    "Product ID": {
    type: Excel.CellValueType.string,
    basicValue: productID.toString() || ""
    },
    "Product Name": {
    type: Excel.CellValueType.string,
    basicValue: productName || ""
    },
    "Quantity Per Unit": {
    type: Excel.CellValueType.string,
    basicValue: product.quantityPerUnit || ""
    },
    // Add Unit Price as a formatted number.
    "Unit Price": {
    type: Excel.CellValueType.formattedNumber,
    basicValue: product.unitPrice,
    numberFormat: "$* #,##0.00"
    },
    Discontinued: {
    type: Excel.CellValueType.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: Excel.CellValueType.webImage,
    address: product.productImage || ""
    };
    entity.layouts.card.mainImage = { property: "Image" };
    }
    // Add a nested entity for the product category.
    if (category) {
    entity.properties["Category"] = {
    type: Excel.CellValueType.entity,
    text: category.categoryName,
    properties: {
    "Category ID": {
    type: Excel.CellValueType.double,
    basicValue: category.categoryID,
    propertyMetadata: {
    // Exclude the category ID property from the card view and auto complete.
    excludeFrom: {
    cardView: true,
    autoComplete: true
    }
    }
    },
    "Category Name": {
    type: Excel.CellValueType.string,
    basicValue: category.categoryName || ""
    },
    Description: {
    type: Excel.CellValueType.string,
    basicValue: category.description || ""
    }
    }
    };
    // Add nested product category to the card layout.
    entity.layouts.card.sections[0].properties.push("Category");
    }
    // Add a nested entity for the supplier.
    if (supplier) {
    entity.properties["Supplier"] = {
    type: Excel.CellValueType.entity,
    text: supplier.companyName,
    properties: {
    "Supplier ID": {
    type: Excel.CellValueType.double,
    basicValue: supplier.supplierID
    },
    "Company Name": {
    type: Excel.CellValueType.string,
    basicValue: supplier.companyName || ""
    },
    "Contact Name": {
    type: Excel.CellValueType.string,
    basicValue: supplier.contactName || ""
    },
    "Contact Title": {
    type: Excel.CellValueType.string,
    basicValue: supplier.contactTitle || ""
    }
    },
    layouts: {
    card: {
    title: { property: "Company Name" },
    sections: [
    {
    layout: "List",
    properties: ["Supplier ID", "Company Name", "Contact Name", "Contact Title"]
    }
    ]
    }
    }
    };
    // Add nested product supplier to the card layout.
    entity.layouts.card.sections[2].properties.push("Supplier");
    }
    return entity;
    }
    // Get products and product properties.
    function getProduct(productID: number): any {
    return products.find((p) => p.productID == productID);
    }
    // Get product categories and category properties.
    function getCategory(categoryID: number): any {
    return categories.find((c) => c.categoryID == categoryID);
    }
    // Get product suppliers and supplier properties.
    function getSupplier(supplierID: number): any {
    return suppliers.find((s) => s.supplierID == supplierID);
    }
    /** Set up Sample worksheet. */
    async function setup() {
    await Excel.run(async (context) => {
    context.workbook.worksheets.getItemOrNullObject("Sample").delete();
    const sheet = context.workbook.worksheets.add("Sample");
    const productsTable = sheet.tables.add("A1:C1", true /*hasHeaders*/);
    productsTable.name = "ProductsTable";
    productsTable.getHeaderRowRange().values = [["Product", "ProductID", "ProductName"]];
    productsTable.rows.add(
    null /*add at the end*/,
    products.map((p) => [null, p.productID, p.productName])
    );
    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 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: ""
    },
    {
    productID: 11,
    productName: "Queso Cabrales",
    supplierID: 5,
    categoryID: 4,
    quantityPerUnit: "1 kg pkg.",
    unitPrice: 21,
    discontinued: false,
    productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/9/96/Tilsit_cheese.jpg/190px-Tilsit_cheese.jpg"
    },
    {
    productID: 12,
    productName: "Queso Manchego La Pastora",
    supplierID: 5,
    categoryID: 4,
    quantityPerUnit: "10 - 500 g pkgs.",
    unitPrice: 38,
    discontinued: false,
    productImage: "https://upload.wikimedia.org/wikipedia/commons/thumb/5/59/Manchego.jpg/177px-Manchego.jpg"
    },
    {
    productID: 13,
    productName: "Konbu",
    supplierID: 6,
    categoryID: 8,
    quantityPerUnit: "2 kg box",
    unitPrice: 6,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 14,
    productName: "Tofu",
    supplierID: 6,
    categoryID: 7,
    quantityPerUnit: "40 - 100 g pkgs.",
    unitPrice: 23.25,
    discontinued: false,
    productImage:
    "https://upload.wikimedia.org/wikipedia/commons/thumb/e/e5/Korean.food-Dubu.gui-01.jpg/120px-Korean.food-Dubu.gui-01.jpg"
    },
    {
    productID: 15,
    productName: "Genen Shouyu",
    supplierID: 6,
    categoryID: 2,
    quantityPerUnit: "24 - 250 ml bottles",
    unitPrice: 15.5,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 16,
    productName: "Pavlova",
    supplierID: 7,
    categoryID: 3,
    quantityPerUnit: "32 - 500 g boxes",
    unitPrice: 17.45,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 17,
    productName: "Alice Mutton",
    supplierID: 7,
    categoryID: 6,
    quantityPerUnit: "20 - 1 kg tins",
    unitPrice: 39,
    discontinued: true,
    productImage: ""
    },
    {
    productID: 18,
    productName: "Carnarvon Tigers",
    supplierID: 7,
    categoryID: 8,
    quantityPerUnit: "16 kg pkg.",
    unitPrice: 62.5,
    discontinued: false,
    productImage: ""
    },
    {
    productID: 19,
    productName: "Teatime Chocolate Biscuits",
    supplierID: 8,
    categoryID: 3,
    quantityPerUnit: "10 boxes x 12 pieces",
    unitPrice: 9.2,
    discontinued: false,
    productImage:
    "https://upload.wikimedia.org/wikipedia/commons/thumb/d/df/Macau_Koi_Kei_Bakery_Almond_Biscuits_2.JPG/120px-Macau_Koi_Kei_Bakery_Almond_Biscuits_2.JPG"
    },
    {
    productID: 20,
    productName: "Sir Rodney's Marmalade",
    supplierID: 8,
    categoryID: 3,
    quantityPerUnit: "30 gift boxes",
    unitPrice: 81,
    discontinued: false,
    productImage:
    "https://upload.wikimedia.org/wikipedia/commons/thumb/3/30/Homemade_marmalade%2C_England.jpg/135px-Homemade_marmalade%2C_England.jpg"
    }
    ];
    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
    template:
    content: |-
    <section class="ms-font-m">
    <p>This sample shows how to create entity values for each row in a table. An entity value is a container for data types, similar to an object in object-oriented programming.</p>
    <p>In particular, this sample highlights the card layout options of an entity value, including the title, an image, collapsible sections, and nested entity values.</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>
    <button id="addEntitiesToTable" class="ms-Button">
    <span class="ms-Button-label">Add entity values</span>
    </button>
    <p>To see the entity value, click the icon to the left of the title in the <strong>Product</strong> column after selecting <strong>Add entity values</strong>.</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/beta/hosted/office.js
    @types/office-js-preview
    [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]