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
This sample shows how to create entity values with references to other entity values.
Raw
Data types: Entity values with references.EXCEL.yaml
name: 'Data types: Entity values with references'
description: >-
This sample shows how to create entity values with 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 employees in the table,
// creates entity values for each of those employees, and adds the entities
// to the table.
const employeesTable = context.workbook.tables.getItem("EmployeesTable");
// Add a new column to the table for 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();
// 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.
const employee = getEmployee(rowValues[1]);
// 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.employeeID, 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.
employeeColumn.getDataBodyRange().valuesAsJson = entities;
employeeColumn.getRange().format.autofitColumns();
await context.sync();
});
}
// Create entities from employee properties.
function makeEmployeeEntity(employeeID: number, lastName: string, firstName:
string, employee?: any, managerReference?: number) {
const entity: Excel.EntityCellValue = {
type: Excel.CellValueType.entity,
text: `${firstName} ${lastName}`,
properties: {
"Employee ID": {
type: Excel.CellValueType.string,
basicValue: 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: ["First Name", "Last Name", "Title", "Employee ID"]
}
]
}
}
};
// Add manager reference
if (managerReference != null) {
entity.properties["Manager"] = {
type: "Reference",
reference: managerReference
};
entity.layouts.card.sections[0].properties.push("Manager");
}
return entity;
}
// 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);
}
/** 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 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 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: "<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: |
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]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment