Users just love working in Excel and there is no avoiding it! This post will explore how we can bring Microsoft Dataverse to Excel by implementing Office Add-ins using Office JavaScript APIs and consuming Dataverse APIs straight from the add-ins. This is a condensed written version on the topic that I presented at the Power Community – Power Apps Front End Development day. You can find the session information here and I believe the recordings from the event will be made available sometime in the next couple of months 🤞.
In this post we are going to cover the following:
- Why use Excel Office Add-ins?
- What are Office Add-ins?
- Set the scene! Our sample Microsoft Dataverse scenario
- Set up Excel Office Add-in project
- Work With Office JavaScript APIs
- Why use Office Dialog API?
- Authenticate with Microsoft Dataverse API from Office Add-in
Why Use Excel Office Add-ins?
Being on the side of IT, our opinions might be a little biased but we all know that Excel is one of the favourite tools that the users love to use for their work. In certain scenarios rightfully so, as there is no competing tool that is as flexible and powerful enough to handle the complexity and variance of rules like Excel. Excel is an excellent tool, period. The issue is when there is a lack of discipline and governance around the use of Excel, especially around data integrity, disparity and duplication. The opportunity to streamline the business processes and the ability to access data from a single centralised location are often missed when users work in their silos in Excel. The cause of this might be manifold; that it is inevitably easier to work in Excel or the business systems that are built-for-purpose do not cater for all scenarios. Whatever the cause might be, we need to find a way to enable users to use Excel efficiently with our business systems which in our case would be Power Apps and Dynamics 365.
We have tools in Power Platform that we can use to interact with Excel, notably Power BI for data analytics and Canvas Apps and Power Automate flows through connectors, but the experience is limited as dynamic CRUD operations are not supported through these tools whilst the user is working on the Excel document. To tackle this particular challenge, we are going to explore implementing Excel Office Add-ins along with Office and Excel JavaScript APIs.
What are Office Add-ins?
Most of us are quite familiar with the concept of add-ins if we have worked with any Office applications. For example, we track emails from our Outlook to Dynamics 365 by enabling the Dynamics 365 App for Outlook add-in. In a nutshell, Office Add-ins are simply websites embedded in Office applications. Its platform allows us to build solutions that can interact with the contents of the Office document as well as external data like Microsoft Graph and Dataverse.
There are two components to Office Add-ins:
- Manifest file – an XML file that defines the setting and capability of the add-in. It contains metadata to describe the add-in, specifies the target Office client and permission allowed, and most importantly dictates how the add-in will extend and interact with the Office client, e.g. custom ribbon buttons, task panes etc.
- Web application – standard web technologies like HTML, CSS, JavaScript can be utilised to provide UI and functionality for the add-in components. In this context, Office JavaScript APIs can be used to interact with the Office client and the content of the Office document.
There are two types of APIs you need to know about when working with Office Add-ins:
- Common API – provides access to common functionalities and shared object models across multiple Office applications. This includes common UI components like Dialogs, Office client settings and context object which gives access to the runtime environment after the initialisation.
- Application-specific API – provides access to host-specific functionalities and object models. For example, Excel JavaScript API gives access to strongly typed objects for worksheets, ranges and tables, etc.
Set the Scene! Our Sample Microsoft Dataverse Scenario
In our sample scenario, the user has an Excel document with multiple worksheets with tables containing data that needs to be stored in the Power Apps instance. The rules for entering this data are widely varied depending on different scenarios and too complex to implement as logic on the model-driven or canvas app. Re-entering this data manually is out of the question and creating import files every time to import the data is too inefficient. The user is seeking a dynamic and automated way to enter this data into the Power Apps instance.
Given this scenario, we need to consider few things:
- Ability to identify tables in an Excel document and retrieve data from them
- Ability to authenticate the user to the Microsoft identity platform from the add-in to access the Microsoft Dataverse data
- Ability to call the Microsoft Dataverse API with the token acquired to perform CRUD operations from the add-in
Set up Excel Office Add-in Project
There are a few things you need to get up and running with Excel Office Add-ins development. Here are the prerequisites:
- Language of choice: JavaScript or TypeScript
- Tools:
- Latest Node.js and npm
- Yeoman and Yeoman generator for Office Add-ins npm packages
- IDE – preferably Visual Studio Code to get the benefit of the Microsoft Office Add-ins Debugger extension
- Microsoft 365 Account with Office subscription – I prefer to have Excel installed on my local machine
- On Windows, Microsoft Edge and Microsoft Edge DevTools (optional)
Install the following npm packages globally:
npm install -g yo generator-officeScaffold the Office Add-ins project using the Yeoman generator for Office Add-ins:
yo officeChoose the following options when prompted:
- Choose a project type:
Office Add-in Task Pane project using React framework - Choose a script type:
TypeScript - What do you want to name your add-in?:
<Name for your project e.g. table-mapper> - Which Office client application would you like to support?:
Excel
Once the project has been scaffolded and local npm packages installed, navigate to the project directory to browse the code and test what has been created. Start the local dev server and sideload the Office Add-in to desktop Excel by:
npm startUsing this scaffolding as a base, I have set up and modified the project structure as follows to implement our scenario:
src/
login/
login.html
login.ts
logout/
logout.html
logout.ts
logoutcomplete/
logoutcomplete.html
taskpane/
components/
Login.tsx
TableColumns.tsx
TableFilters.tsx
TableMapper.tsx
helpers/
dataverseHelper.ts
excelHelper.ts
officeHelper.ts
hooks/
useAppContext.ts
useAuth.ts
useOfficeAuth.ts
interfaces/
...
utils/
...
App.tsx
AppContext.tsx
index.tsx
taskpane.html
.envI have also installed few other npm packages:
npm install @azure/msal-browser @fluentui/react axios dotenv dotenv-webpackNote: I won't cover all the code in this project but will highlight the important parts to demonstrate the 3 aspects that I pointed out earlier.
In order to get a feel for what we are about to discuss, here is a demo of the task pane add-in built to capture the essence of the scenario:
Work With Office JavaScript APIs
To work with Office JavaScript APIs, we must first reference the Office.js library from our web application. In our example, the CDN for this library will be referenced from the taskpane.html:
<html lang="en">
<head>
...
<!-- Office JavaScript API -->
<!-- Note: Add-ins submitted to AppSource must reference Office.js by this CDN. They can't use a local reference -->
<script type="text/javascript" src="https://appsforoffice.microsoft.com/lib/1.1/hosted/office.js"></script>
</head>
<body>
<div id="container"></div>
</body>
</html>
The initiating point for any Office add-in is to wait for the runtime environment to load and the add-in is in a state that is ready to interact with the Office application and the host document. For us, that entry point is in Index.tsx with the Office.onReady() method. Here we can check for things like whether the application host type is Excel and examine the Excel requirement sets to see if the user’s version of Excel supports all the APIs that our add-in uses:
let isOfficeInitialized = false;
const dataverseHelper
= new DataverseHelper(process.env.DATAVERSE_URL + process.env.DATAVERSE_URL_API_SEGMENT);
const render = (Component) => {
const props = {
title: "Dataverse Excel Demo 1 Table Mapper Add-in",
isOfficeInitialized: isOfficeInitialized,
dataverseHelper: dataverseHelper
};
ReactDOM.render(
<AppContainer>
<Component {...props} />
</AppContainer>,
document.getElementById("container")
);
};
Office.onReady()
.then(function () {
isOfficeInitialized = true;
if (Office.context.host !== Office.HostType.Excel) {
console.log("Sorry, this add-in only works with Excel.");
isOfficeInitialized = false;
}
if (!Office.context.requirements.isSetSupported('ExcelApi', '1.7')) {
console.log("Sorry, this add-in only works with newer versions of Excel.");
isOfficeInitialized = false;
}
if (isOfficeInitialized) {
render(App);
}
});
Once these are set up, calling Excel JavaScript API from the add-in is easy. In our scenario, we want to retrieve the names of all the Excel tables defined in the current worksheet into a dropdown control. This way we can select which of the Excel tables we want to map into a Dataverse table. Once we select the Excel table name from the dropdown, we want to load the table header values to use as columns. In excelHelper.ts:
export const getTableNames = async (): Promise<string[]> =>
Excel.run(async (context: Excel.RequestContext) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const tables = sheet.tables.load("name");
await context.sync();
const tableNames = [];
tables.items.sort(sortByTableName).forEach((table) => {
tableNames.push(table.name);
});
return tableNames;
});
export const getTableHeaderValues = async (tableName: string): Promise<string[]> =>
Excel.run(async (context: Excel.RequestContext) => {
const sheet = context.workbook.worksheets.getActiveWorksheet();
const table = sheet.tables.getItem(tableName);
table.getRange().select();
const headerRange = table.getHeaderRowRange().load("values");
await context.sync();
const headerValues = headerRange.values[0];
return headerValues;
});
The Excel.run function, from the Excel JavaScript API, can perform actions on the Excel object model using the request context. Since the Office application and the add-in are running in two different processes with different runtime environments, the context object acts as an intermediary between them. It allows access to object models such as worksheets, tables and ranges of the Excel document from the add-in using promise-based requests. An important thing to note here is that these object models are proxy objects. Any methods or properties you set or load is just pending commands until you call the context.sync() method. In our example, to retrieve the names of the tables, we must first load the property of the table object and then call the sync method to synchronise the state between the proxy objects and actual Excel objects. It is only after this that we would be able to access the property data, otherwise, it will throw an error.
From here we can populate the dropdown control with the names of the Excel tables and load the table header values as columns in TableFilters.tsx:
import * as React from "react";
import { useEffect, useState } from "react";
import { IconButton } from "@fluentui/react/lib/Button";
import { Dropdown, IDropdownOption } from "@fluentui/react/lib/Dropdown";
import { Stack } from "@fluentui/react/lib/Stack";
import * as ExcelHelper from "../helpers/excelHelper";
import { useAppContext } from "../hooks/useAppContext";
import { useAuthContext } from "../hooks/useAuthContext";
import { IEntityDefinition } from "../interfaces/IEntityDefinition";
import { IAttribute } from "../interfaces/IAttribute";
export interface ITableFiltersProps {
loadExcelColumns: (options:IDropdownOption[]) => void;
loadDataverseColumns: (columns: IAttribute[]) => void;
notifySelectedExcelTable: (tableName: string) => void;
notifySelectedDataverseTable: (table: IEntityDefinition) => void;
}
export const TableFilters: React.FC<ITableFiltersProps> = (props) => {
const { dataverseHelper } = useAppContext();
const { authState } = useAuthContext();
const { authenticated, token } = authState;
const {
loadExcelColumns, loadDataverseColumns,
notifySelectedExcelTable, notifySelectedDataverseTable } = props;
const [excelTables, setExcelTables] = useState<IDropdownOption[]>([]);
const [selectedExcelTable, setSelectedExcelTable] = useState<string>(null);
const [dataverseTables, setDataverseTables] = useState<IDropdownOption[]>([]);
const [selectedDataverseTable, setSelectedDataverseTable] = useState<string>(null);
const [dataverseTableDefinitions, setDataverseTableDefinitions] = useState<IEntityDefinition[]>([]);
useEffect(() => {
if (!authenticated) {
setDataverseTableDefinitions([]);
setDataverseTables([]);
setSelectedDataverseTable(null);
}
}, [authenticated]);
useEffect(() => {
getExcelTables();
}, []);
useEffect(() => {
if (token) {
dataverseHelper.getEntityDefinitions(token).then((result: IEntityDefinition[]) => {
setDataverseTables(
result.map<IDropdownOption>(value => ({
key: value.logicalName,
text: value.schemaName
})));
setDataverseTableDefinitions([...result]);
});
}
}, [authenticated]);
useEffect(() => {
if (selectedExcelTable) {
ExcelHelper.getTableHeaderValues(selectedExcelTable).then((result: string[]) => {
const options
= result.sort()
.map<IDropdownOption>(name => ({
key: name,
text: name
}));
options.unshift({ key: "", text: '(none)' });
loadExcelColumns(options);
})
}
}, [selectedExcelTable]);
useEffect(() => {
if (selectedDataverseTable) {
dataverseHelper.getEntityAttributes(selectedDataverseTable, token)
.then((result: IAttribute[]) => {
loadDataverseColumns([...result]);
});
}
}, [selectedDataverseTable]);
const getExcelTables = () => {
ExcelHelper.getTableNames().then((result: string[]) => {
setExcelTables(
result.map<IDropdownOption>(name => ({
key: name,
text: name
})));
});
};
const onChangeExcelTables
= (_event: React.FormEvent<HTMLDivElement>, option?: IDropdownOption<any>) => {
const tableName = option?.key as string;
setSelectedExcelTable(tableName);
notifySelectedExcelTable(tableName);
}
const onChangeDataverseTables
= (_event: React.FormEvent<HTMLDivElement>, option?: IDropdownOption<any>) => {
const tableLogicalName = option?.key as string;
setSelectedDataverseTable(tableLogicalName);
notifySelectedDataverseTable(
dataverseTableDefinitions.find(x=> x.logicalName == tableLogicalName));
}
return (
<React.Fragment>
<Stack horizontal verticalAlign="end" tokens={{ childrenGap: 10 }}>
<Stack.Item grow={1}>
<Dropdown
label="Destination (Dataverse Tables)"
options={dataverseTables}
defaultSelectedKey={selectedDataverseTable}
onChange={onChangeDataverseTables}
placeholder="Select table"
/>
</Stack.Item>
<Stack.Item grow={1}>
<Dropdown
label="Source (Excel Tables)"
options={excelTables}
defaultSelectedKey={selectedExcelTable}
onChange={onChangeExcelTables}
placeholder="Select table"
/>
</Stack.Item>
<Stack.Item>
<IconButton
iconProps={{ iconName: 'Refresh' }}
title="Refresh excel tables"
onClick={getExcelTables} />
</Stack.Item>
</Stack>
</React.Fragment>
);
}
Why use Office Dialog API?
Before we can discuss how to get authenticated to access Microsoft Dataverse API from our add-in, we must first discuss the need for Office Dialog API. The type of add-in we are implementing here is called “web” add-ins and these can run cross-platform, including Windows, Mac, iPad and in a browser. No code needs to be installed or executed directly on users devices since the host page of the add-in, such as the task pane, is executed in the context of a sandboxed browser. Depending on the platform, the Office client will host the add-in differently, for example on the Office web client, the host page is hosted in an iframe. This is where things get tricky, especially when there is an authentication requirement to call an identity authority, like the Microsoft Identity platform from the add-in. This is because, for security reasons, almost all identity providers will prevent their login page from opening in an iframe.
This is where we will use Office Dialog API. It provides a non-modal dialog box that spins up a completely separate browser instance from the host page. It has its own JavaScript runtime environment and no shared execution context or storage with the host page. From the context of the dialog, we can redirect the user to the login page of the identity provider and handle the redirect to obtain the authorization response which will include the access token. The Office.context.ui.messageParent method can be used to return messages from the dialog to the host, in which case certain Office dialog event types, such as Office.EventType.DialogMessageReceived, should be observed.
Authenticate with Microsoft Dataverse API from Office Add-in
There are three parts we need to set up in order to authenticate with Microsoft Dataverse API from the add-in:
#1 Register an app in the Azure App Registration and create an application user on the Power Apps instance
In order to authenticate the user from the add-in to access the Microsoft Dataverse API we must register an app in the app registration through Azure Portal.
#1.a. Set up Azure App Registration
- Navigate to the App Registration section of the Azure Portal (Use this link to navigate directly). Select + New Registration
- On the Register an Application page, enter the following information:
- Name:
TableMapperAddIn - Supported account types:
Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox) - Redirect URI: select
Single-page application (SPA)and typehttps://<your local dev server url>/login/login.html
- Name:
- On the Overview page, copy and note down the
Application (client) ID - On the API permissions page, add the following delegated permission by selecting + Add a permission:
#1.b Set up Application User on Power Apps instance
- Navigate to the Environments section of the Power Platform admin center (Use this link to navigate directly). Select your Power Apps environment from the list
- On the Environment hub page, under Access box, click See all S2S apps
- On the Application users page, click + New app user
- On the Create a new app user dialog, click + Add an app to find the
TableMapperAddInapp registration:
- Select Business Unit and save the application user. (Note: no need to assign a security role at the application user level as the app will use the signed-in user’s permission granted through the user consent process)
#2 Implement login, logout and redirect interactions with the Microsoft Identity sign-in page with the help of Office Dialog API
To share the authentication state and logic implementation between the React components, I have implemented the useOfficeAuth custom hook that is instantiated and shared as a prop via the AuthProvider React context API.
In App.tsx:
export const App: React.FC<IAppProps> = (props: IAppProps) => {
return (
<AppProvider {...props}>
<AuthProvider>
<TableMapper></TableMapper>
</AuthProvider>
</AppProvider>
)
}
In AppContext.tsx:
export type AuthStatus = "NotLoggedIn" | "LoggedIn" | "Failed" | "Error" | "InProgress";
export interface IAuthState {
authenticated: boolean;
status: AuthStatus;
error?: string | any;
token?: string;
}
export interface IAuthContext {
authState: IAuthState;
login: () => void;
logout: () => void;
}
export const AuthContext = createContext<IAuthContext>({} as IAuthContext);
export const AuthProvider: React.FC = (props) => {
const officeAuth = useOfficeAuth();
return (
<AuthContext.Provider value={officeAuth}>
{props.children}
</AuthContext.Provider>
);
}
In useOfficeAuth.ts:
import { useState } from "react";
import { AuthStatus, IAuthContext, IAuthState } from "../AppContext";
import * as OfficeHelper from "../helpers/officeHelper";
export const useOfficeAuth = (): IAuthContext => {
const [authState, setAuthState] = useState<IAuthState>({
authenticated: false,
status: "NotLoggedIn"
});
const login = () => {
OfficeHelper.signin(loginCallback);
};
const loginCallback = (dialog: Office.Dialog, result: Office.AsyncResult<Office.Dialog>) => {
if (result.status === Office.AsyncResultStatus.Failed) {
setAuthState({
authenticated: false,
status: "Failed",
error: `${result.error.code} ${result.error.message}`,
token: null
});
}
else {
dialog = result.value;
dialog.addEventHandler(Office.EventType.DialogMessageReceived,
(args: { message: string }) => loginDialogMessageReceived(dialog, args));
dialog.addEventHandler(Office.EventType.DialogEventReceived, dialogEventReceived);
}
}
const loginDialogMessageReceived = (dialog: Office.Dialog, args: { message: string }) => {
let messageFromDialog = JSON.parse(args.message);
if (messageFromDialog.status === 'success') {
dialog.close();
setAuthState({
authenticated: true,
status: "LoggedIn",
error: null,
token: messageFromDialog.result
});
}
else {
dialog.close();
setAuthState({...authState,
authenticated: false,
status: "Failed",
error: messageFromDialog.result
});
}
}
const logout = () => {
OfficeHelper.signout(logoutCallback);
};
const logoutCallback = (dialog: Office.Dialog, result: Office.AsyncResult<Office.Dialog>) => {
if (result.status === Office.AsyncResultStatus.Failed) {
setAuthState({
authenticated: false,
status: "Failed",
error: `${result.error.code} ${result.error.message}`
});
}
else {
dialog = result.value;
dialog.addEventHandler(Office.EventType.DialogMessageReceived,
(_) => logoutDialogMessageReceived(dialog));
dialog.addEventHandler(Office.EventType.DialogEventReceived, dialogEventReceived);
}
}
const logoutDialogMessageReceived = (dialog: Office.Dialog) => {
dialog.close();
setAuthState({...authState,
authenticated: false,
status: "NotLoggedIn",
token: null
});
}
const dialogEventReceived = (args: { error: number, type: string }) => {
let errorMessage:string;
let status: AuthStatus = "Error";
switch (args.error) {
case 12002:
errorMessage = 'The dialog box has been directed to a page that it cannot find or load, or the URL syntax is invalid.';
break;
case 12003:
errorMessage ='The dialog box has been directed to a URL with the HTTP protocol. HTTPS is required.';
break;
case 12006:
status = "NotLoggedIn";
break;
default:
errorMessage = 'Unknown error in dialog box.';
break;
}
setAuthState({...authState,
status: status,
error: errorMessage
});
}
return {
authState,
login,
logout
}
}
The login and logout actions are triggered through the methods provided in the custom hook and the authentication responses are captured through the callback methods. We attach an event handler to the instance of the Office Dialog returned via the callback to be notified when message from the dialog is received.
In officeHelper.ts:
let signinDialog: Office.Dialog;
let signoutDialog: Office.Dialog;
const serverUrl: string = location.protocol + '//' + location.hostname + (location.port ? ':' + location.port : '');
export const signin
= (callback: (dialog: Office.Dialog, result: Office.AsyncResult<Office.Dialog>) => void) => {
Office.context.ui.displayDialogAsync(
serverUrl + '/login/login.html',
{ width: 30, height: 40 },
(result: Office.AsyncResult<Office.Dialog>) => {
callback(signinDialog, result);
});
}
export const signout
= (callback: (dialog: Office.Dialog, result: Office.AsyncResult<Office.Dialog>) => void) => {
Office.context.ui.displayDialogAsync(
serverUrl + '/logout/logout.html',
{ width: 30, height: 40 },
(result: Office.AsyncResult<Office.Dialog>) => {
callback(signoutDialog, result);
});
}
The Office.context.ui.displayDialogAsync method takes the URL of the HTML pages that are hosted in the same full domain as the host page, it will not work otherwise. The result that is returned as the second parameter to the callback represents the attempt to open the dialog box and not the outcome of the event. Therefore, the event handler must be attached to the dialog to receive further event notifications.
In the project structure under src, I have added three sections: login, logout and logoutcomplete. These will be used to handle the authentication process with the Azure AD sign-in page. I’m using dotenv npm package to hold all my environment variables for the project in one place.
Here I have pasted the client ID of the app registration along with other variable values in .env:
CLIENT_ID=<paste your client ID here>
REDIRECT_URI=https://<your local dev server url>/login/login.html
POST_LOGOUT_REDIRECT_URI=https://<your local dev server url>/logoutcomplete/logoutcomplete.html
SCOPE=https://<your power apps instance name>.crm6.dynamics.com/user_impersonation
DATAVERSE_URL=https://<your power apps instance name>.crm6.dynamics.com
DATAVERSE_URL_API_SEGMENT=/api/data/v9.1/In login.ts:
import { PublicClientApplication } from "@azure/msal-browser";
(() => {
Office.initialize = () => {
const msalInstance = new PublicClientApplication({
auth: {
clientId: process.env.CLIENT_ID,
authority: 'https://login.microsoftonline.com/common',
redirectUri: process.env.REDIRECT_URI
},
cache: {
cacheLocation: 'localStorage',
storeAuthStateInCookie: true
}
});
msalInstance.handleRedirectPromise()
.then((response) => {
if (response) {
Office.context.ui.messageParent(
JSON.stringify({ status: 'success', result: response.accessToken }));
} else {
msalInstance.loginRedirect({
scopes: [process.env.SCOPE]
});
}
})
.catch((error) => {
const errorData: string = `errorMessage: ${error.errorCode}
message: ${error.errorMessage}
errorCode: ${error.stack}`;
Office.context.ui.messageParent(
JSON.stringify({ status: 'failure', result: errorData }));
});
};
})();
This is using the @azure/msal-browser npm package to authenticate the user and acquire the access token from the Microsoft identity platform to use when accessing the Microsoft Dataverse API. This is using the Redirect API interaction type to redirect the browser window to the login page using the handleRedirectPromise method and the redirectUri that is specified is the same login.html page. Important to note that we must wait for the Office runtime context to be initialised before starting any of these processes. The result that is returned from the identity provider can be sent back to the host page using the Office.context.ui.messageParent which only accepts string or boolean types as a value. Therefore, if the response is complex, it’s best to serialise the object as a stringified JSON value as we did in our example.
The logout and post logout processes follow the same pattern as the login page. In logout.ts:
import { PublicClientApplication } from '@azure/msal-browser';
(() => {
Office.initialize = () => {
const msalInstance = new PublicClientApplication({
auth: {
clientId: process.env.CLIENT_ID,
redirectUri: process.env.POST_LOGOUT_REDIRECT_URI,
postLogoutRedirectUri: process.env.POST_LOGOUT_REDIRECT_URI
}
});
msalInstance.logout();
};
})();
In logoutcomplete.html:
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<!-- Office JavaScript API -->
<script type="text/javascript" src="https://appsforoffice.microsoft.com/lib/1.1/hosted/office.js"></script>
<script>
Office.initialize = () => {
Office.context.ui.messageParent("");
};
</script>
</head>
<body></body>
</html>
As part of the post logout process, an empty message is sent in order to notify the host page of an event to trigger the closing of the dialog box.
Finally, we need to update the webpack.config to include these extra sections to include in the build and bundling:
const Dotenv = require("dotenv-webpack");
...
module.exports = async (env, options) => {
...
const config = {
...,
entry: {
...,
login: "./src/login/login.ts",
logout: "./src/logout/logout.ts"
},
...,
plugins: [
...,
new Dotenv(),
new HtmlWebpackPlugin({
filename: "login/login.html",
template: "./src/login/login.html",
chunks: ["login"]
}),
new HtmlWebpackPlugin({
filename: "logout/logout.html",
template: "./src/logout/logout.html",
chunks: ["logout"]
}),
new HtmlWebpackPlugin({
filename: "logoutcomplete/logoutcomplete.html",
template: "./src/logoutcomplete/logoutcomplete.html",
chunks: ["logoutcomplete"]
})
],
...
};
return config;
};
#3 Use the access token acquired to consume the Microsoft Dataverse API from the add-in
If you refer back to the implementation for the TableFilters.tsx, we call the Microsoft Dataverse API via dataverseHelper to retrieve the entity (table) definitions and the attributes for the selected table. This is possible by retrieving the auth state from the AuthContext using the useAuthContext custom hook.
From here we pass in an access token for all CRUD methods in DataverseHelper.ts:
import axios from "axios";
import { IAttribute } from "../interfaces/IAttribute";
import { IEntityDefinition } from "../interfaces/IEntityDefinition";
import { sortByString } from "../utils/shared";
export class DataverseHelper {
private _serviceUrl: string;
constructor(serviceUrl: string) {
this._serviceUrl = serviceUrl?.endsWith('/') ? serviceUrl : serviceUrl + '/';
}
async getEntityDefinitions(accessToken: string): Promise<IEntityDefinition[]> {
let url = this._serviceUrl;
url += "EntityDefinitions";
url += "?$select=LogicalName,PrimaryNameAttribute,SchemaName,LogicalCollectionName,IsCustomizable,OwnershipType"
url += "&$filter=IsCustomizable/Value eq true";
const response = await this.getAsync(url, accessToken);
if (response.status >= 200 && response.status < 300) {
const entityDefinitions: IEntityDefinition[] = response.data.value.map((value) => ({
logicalName: value.LogicalName,
logicalCollectionName: value.LogicalCollectionName,
metadataId: value.MetadataId,
primaryNameAttribute: value.PrimaryNameAttribute,
schemaName: value.SchemaName
}));
return entityDefinitions.sort((a, b) => sortByString(a, b, "schemaName"));
}
return [];
}
async getEntityAttributes(entityLogicalName: string, accessToken: string): Promise<IAttribute[]> {
let url = this._serviceUrl;
url += `EntityDefinitions(LogicalName='${entityLogicalName}')/Attributes`;
url += "?$select=EntityLogicalName,LogicalName,SchemaName,DisplayName,MetadataId,AttributeType"
url += "&$filter=IsValidForForm eq true and IsValidForUpdate eq true and IsValidForCreate eq true";
const response = await this.getAsync(url, accessToken);
if (response.status >= 200 && response.status < 300) {
const attributes: IAttribute[] = response.data.value.map((value) => ({
entityLogicalName: value.EntityLogicalName,
logicalName: value.LogicalName,
metadataId: value.MetadataId,
displayName: `${value.DisplayName?.UserLocalizedLabel?.Label} (${value.LogicalName})`,
schemaName: value.SchemaName,
attributeType: value.AttributeType
}));
return attributes.sort((a, b) => sortByString(a, b, "displayName"));
}
return [];
}
async mapTableRows(entityCollectionName: string, rows: any[], accessToken: string): Promise<boolean> {
let counter = 0;
let url = this._serviceUrl + entityCollectionName;
if (rows) {
for (let i = 0; i < rows.length; i++) {
try {
await this.postAsync(url, rows[i], accessToken);
counter++;
}
catch (ex) {
console.log("Error exception = ", ex);
console.log("Error mapping = ", rows[i])
}
}
}
return rows?.length == counter ? true : false;
}
async getAsync(url: string, accessToken: string) {
const response = await axios({
url: url,
method: 'get',
headers: {
'Authorization': `Bearer ${accessToken}`,
'Prefer': 'odata.include-annotations=*'
}
});
return response;
}
async postAsync(url: string, data: any, accessToken: string) {
const response = await axios({
url: url,
method: 'post',
headers: {
'Authorization': `Bearer ${accessToken}`,
'Accept': 'application/json',
'Content-Type': 'application/json; charset=utf-8'
},
data: data
});
return response;
}
}
It was a pretty lengthy and code-heavy post but I hope it contained enough information to get you started with building Office Add-ins for Microsoft Dataverse scenarios. The presentation I did had a full demo for this and another demo for the custom Excel function that calls the Microsoft Dataverse, so keep a lookout for that when it comes out!
Hope you enjoyed the post, let me know your thoughts in the comments below or any of my contact channels 😎
