Build Office Add-ins with Microsoft Graph

This tutorial teaches you how to build an Office Add-in for Excel that uses the Microsoft Graph API to retrieve calendar information for a user.

Prerequisites

Before you start this demo, you should have Node.js and Yarn installed on your development machine. If you do not have Node.js or Yarn, visit the previous link for download options.

You should also have either a personal Microsoft account with a mailbox on Outlook.com, or a Microsoft work or school account. If you don't have a Microsoft account, there are a couple of options to get a free account:

• You can sign up for a new personal Microsoft account.
• You can sign up for the Office 365 Developer Program to get a free Office 365 subscription.

Feedback

Please provide any feedback on this tutorial in the GitHub repository.

Create an Office Add-in

• 45 minutes remaining

In this exercise you will create an Office Add-in solution using Express. The solution will consist of two parts.

• The add-in, implemented as static HTML and JavaScript files.
• A Node.js/Express server that serves the add-in and implements a web API to retrieve data for the add-in.

Create the server

1. Open your command-line interface (CLI), navigate to a directory where you want to create your project, and run the following command to generate a package.json file.

   yarn init
   

   Enter values for the prompts as appropriate. If you're unsure, the default values are fine.

2. Run the following commands to install dependencies.

   yarn add express@4.17.1 express-promise-router@4.1.0 dotenv@8.2.0 node-fetch@2.6.1 jsonwebtoken@8.5.1@
   yarn add jwks-rsa@2.0.2 @azure/msal-node@1.0.2 @microsoft/microsoft-graph-client@2.2.1
   yarn add date-fns@2.21.1 date-fns-tz@1.1.4 isomorphic-fetch@3.0.0 windows-iana@5.0.1
   yarn add -D typescript@4.2.4 ts-node@9.1.1 nodemon@2.0.7 @types/node@14.14.41 @types/express@4.17.11
   yarn add -D @types/node-fetch@2.5.10 @types/jsonwebtoken@8.5.1 @types/microsoft-graph@1.35.0
   yarn add -D @types/office-js@1.0.174 @types/jquery@3.5.5 @types/isomorphic-fetch@0.0.35
   

3. Run the following command to generate a tsconfig.json file.

   tsc --init
   

4. Open ./tsconfig.json in a text editor and make the following changes.

   • Change the target value to es6.
   • Uncomment the outDir value and set it to ./dist.
   • Uncomment the rootDir value and set it to ./src.

5. Open ./package.json and add the following property to the JSON.

   "scripts": {
     "start": "nodemon ./src/server.ts",
     "build": "tsc --project ./"
   },
   

6. Run the following command to generate and install development certificates for your add-in.

   npx office-addin-dev-certs install
   

   If prompted for confirmation, confirm the actions. Once the command completes, you will see output similar to the following.

   You now have trusted access to https://localhost.
   Certificate: <path>\localhost.crt
   Key: <path>\localhost.key
   

7. Create a new file named .env in the root of your project and add the following code.

   AZURE_APP_ID='YOUR_APP_ID_HERE'
   AZURE_CLIENT_SECRET='YOUR_CLIENT_SECRET_HERE'
   TLS_CERT_PATH='PATH_TO_LOCALHOST.CRT'
   TLS_KEY_PATH='PATH_TO_LOCALHOST.KEY'
   

   Replace PATH_TO_LOCALHOST.CRT with the path to localhost.crt and PATH_TO_LOCALHOST.KEY with the path to localhost.key output by the previous command.

8. Create a new directory in the root of your project named src.

9. Create two directories in the ./src directory: addin and api.

10. Create a new file named auth.ts in the ./src/api directory and add the following code.

    import Router from 'express-promise-router';
    
    const authRouter = Router();
    
    // TODO: Implement this router
    
    export default authRouter;
    

11. Create a new file named graph.ts in the ./src/api directory and add the following code.

    import Router from 'express-promise-router';
    
    const graphRouter = Router();
    
    // TODO: Implement this router
    
    export default graphRouter;
    

12. Create a new file named server.ts in the ./src directory and add the following code.

    import express from 'express';
    import https from 'https';
    import fs from 'fs';
    import dotenv from 'dotenv';
    import path from 'path';
    
    // Load .env file
    dotenv.config();
    
    import authRouter from './api/auth';
    import graphRouter from './api/graph';
    
    const app = express();
    const PORT = 3000;
    
    // Support JSON payloads
    app.use(express.json());
    app.use(express.static(path.join(__dirname, 'addin')));
    app.use(express.static(path.join(__dirname, 'dist/addin')));
    
    app.use('/auth', authRouter);
    app.use('/graph', graphRouter);
    
    const serverOptions = {
      key: fs.readFileSync(process.env.TLS_KEY_PATH!),
      cert: fs.readFileSync(process.env.TLS_CERT_PATH!),
    };
    
    https.createServer(serverOptions, app).listen(PORT, () => {
      console.log(`⚡️[server]: Server is running at https://localhost:${PORT}`);
    });
    

Create the add-in

1. Create a new file named taskpane.html in the ./src/addin directory and add the following code.

   <html>
     <head>
       <link rel="stylesheet"
         href="https://static2.sharepointonline.com/files/fabric/office-ui-fabric-core/11.0.0/css/fabric.min.css"/>
       <link rel="stylesheet" href="taskpane.css"/>
     </head>
     <body class="ms-Fabric">
       <div class="container">
         <p class="ms-fontSize-32">Checking authentication...</p>
       </div>
       <div class="status"></div>
       <div class="overlay">
         <p class="ms-fontSize-24 ms-fontColor-white">Working...</p>
       </div>
       <script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.5.1.min.js"></script>
       <script src="https://appsforoffice.microsoft.com/lib/beta/hosted/office.js"></script>
       <script src="https://cdn.jsdelivr.net/npm/luxon@1.25.0/build/global/luxon.min.js"></script>
       <script src="taskpane.js"></script>
     </body>
   </html>
   

2. Create a new file named taskpane.css in the ./src/addin directory and add the following code.

   .container {
     margin: 10px;
   }
   
   .status {
     margin: 10px;
   }
   
   .overlay {
     position: fixed;
     display: none;
     width: 100%;
     height: 100%;
     top: 0;
     left: 0;
     right: 0;
     bottom: 0;
     background-color: rgba(0,0,0,0.5);
     z-index: 2;
     cursor: pointer;
   }
   
   .overlay p {
     position: absolute;
     top: 50%;
     left: 50%;
     transform: translate(-50%,-50%);
   }
   
   .status-card {
     padding: 1em;
   }
   
   .primary-button {
     padding: .5em;
     color: white;
     background-color: #0078d4;
     border: none;
     display: block;
   }
   
   .success-msg {
     background-color: #dff6dd;
   }
   
   .error-msg {
     background-color: #fde7e9;
   }
   
   .date-picker {
     display: block;
     padding: .5em;
     margin: 10px 0;
   }
   
   .form-input {
     display: block;
     padding: .5em;
     margin: 10px 0;
     width: 100%;
   }
   

3. Create a new file named taskpane.js in the ./src/addin directory and add the following code.

   // TEMPORARY CODE TO VERIFY ADD-IN LOADS
   'use strict';
   
   Office.onReady(info => {
     if (info.host === Office.HostType.Excel) {
       $(function() {
         $('p').text('Hello World!!');
       });
     }
   });
   

4. Create a new directory in the .src/addin directory named assets.

5. Add three PNG files in this directory according to the following table.

   File name	Size in pixels
   icon-80.png	80x80
   icon-32.png	32x32
   icon-16.png	16x16

   Note

   You can use any image you want for this step. You can also download the images used in this sample directly from GitHub.

6. Create a new directory in the root of the project named manifest.

7. Create a new file named manifest.xml in the ./manifest folder and add the following code. Replace NEW_GUID_HERE with a new GUID, like b4fa03b8-1eb6-4e8b-a380-e0476be9e019.

   <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
   <OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.1"
              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
              xmlns:bt="http://schemas.microsoft.com/office/officeappbasictypes/1.0"
              xmlns:ov="http://schemas.microsoft.com/office/taskpaneappversionoverrides"
              xsi:type="TaskPaneApp">
     <Id>NEW_GUID_HERE</Id>
     <Version>1.0.0.0</Version>
     <ProviderName>Contoso</ProviderName>
     <DefaultLocale>en-US</DefaultLocale>
     <DisplayName DefaultValue="Excel Graph Calendar"/>
     <Description DefaultValue="An add-in that shows how to call Microsoft Graph to access the user's calendar."/>
     <IconUrl DefaultValue="https://localhost:3000/assets/icon-32.png"/>
     <HighResolutionIconUrl DefaultValue="https://localhost:3000/assets/icon-80.png"/>
     <SupportUrl DefaultValue="https://www.contoso.com/help"/>
     <AppDomains>
       <AppDomain>https://localhost:3000</AppDomain>
     </AppDomains>
     <Hosts>
       <Host Name="Workbook"/>
     </Hosts>
     <DefaultSettings>
       <SourceLocation DefaultValue="https://localhost:3000/taskpane.html"/>
     </DefaultSettings>
     <Permissions>ReadWriteDocument</Permissions>
     <VersionOverrides xmlns="http://schemas.microsoft.com/office/taskpaneappversionoverrides" xsi:type="VersionOverridesV1_0">
       <Hosts>
         <Host xsi:type="Workbook">
           <DesktopFormFactor>
             <GetStarted>
               <Title resid="GetStarted.Title"/>
               <Description resid="GetStarted.Description"/>
               <LearnMoreUrl resid="GetStarted.LearnMoreUrl"/>
             </GetStarted>
             <ExtensionPoint xsi:type="PrimaryCommandSurface">
               <OfficeTab id="TabHome">
                 <Group id="CommandsGroup">
                   <Label resid="CommandsGroup.Label"/>
                   <Icon>
                     <bt:Image size="16" resid="Icon.16x16"/>
                     <bt:Image size="32" resid="Icon.32x32"/>
                     <bt:Image size="80" resid="Icon.80x80"/>
                   </Icon>
                   <Control xsi:type="Button" id="TaskpaneButton">
                     <Label resid="TaskpaneButton.Label"/>
                     <Supertip>
                       <Title resid="TaskpaneButton.Label"/>
                       <Description resid="TaskpaneButton.Tooltip"/>
                     </Supertip>
                     <Icon>
                       <bt:Image size="16" resid="Icon.16x16"/>
                       <bt:Image size="32" resid="Icon.32x32"/>
                       <bt:Image size="80" resid="Icon.80x80"/>
                     </Icon>
                     <Action xsi:type="ShowTaskpane">
                       <TaskpaneId>ImportCalendar</TaskpaneId>
                       <SourceLocation resid="Taskpane.Url"/>
                     </Action>
                   </Control>
                 </Group>
               </OfficeTab>
             </ExtensionPoint>
           </DesktopFormFactor>
         </Host>
       </Hosts>
       <Resources>
         <bt:Images>
           <bt:Image id="Icon.16x16" DefaultValue="https://localhost:3000/assets/icon-16.png"/>
           <bt:Image id="Icon.32x32" DefaultValue="https://localhost:3000/assets/icon-32.png"/>
           <bt:Image id="Icon.80x80" DefaultValue="https://localhost:3000/assets/icon-80.png"/>
         </bt:Images>
         <bt:Urls>
           <bt:Url id="GetStarted.LearnMoreUrl" DefaultValue="https://docs.microsoft.com/graph"/>
           <bt:Url id="Taskpane.Url" DefaultValue="https://localhost:3000/taskpane.html"/>
         </bt:Urls>
         <bt:ShortStrings>
           <bt:String id="GetStarted.Title" DefaultValue="Get started with the Excel Graph Calendar add-in!"/>
           <bt:String id="CommandsGroup.Label" DefaultValue="Graph Calendar"/>
           <bt:String id="TaskpaneButton.Label" DefaultValue="Import Calendar"/>
         </bt:ShortStrings>
         <bt:LongStrings>
           <bt:String id="GetStarted.Description" DefaultValue="Add-in loaded succesfully. Go to the HOME tab and click the 'Import Calendar' button to get started."/>
           <bt:String id="TaskpaneButton.Tooltip" DefaultValue="Click to open the Import Calendar task pane"/>
         </bt:LongStrings>
       </Resources>
       <WebApplicationInfo>
         <Id>YOUR_APP_ID_HERE</Id>
         <Resource>api://localhost:3000/YOUR_APP_ID_HERE</Resource>
         <Scopes>
           <Scope>openid</Scope>
           <Scope>profile</Scope>
           <Scope>access_as_user</Scope>
         </Scopes>
       </WebApplicationInfo>
     </VersionOverrides>
   </OfficeApp>
   

Side-load the add-in in Excel

1. Start the server by running the following command.

   yarn start
   

2. Open your browser and browse to https://localhost:3000/taskpane.html. You should see a Not loaded message.

3. In your browser, go to Office.com and sign in. Select Create in the left-hand toolbar, then select Spreadsheet.

   

4. Select the Insert tab, then select Office Add-ins.

5. Select Upload My Add-in, then select Browse. Upload your ./manifest/manifest.xml file.

6. Select the Import Calendar button on the Home tab to open the taskpane.

   

7. After the taskpane opens, you should see a Hello World! message.

   

Register the app in the portal

• 38 minutes remaining

In this exercise, you will create a new Azure AD web application registration using the Azure Active Directory admin center.

1. Open a browser and navigate to the Azure Active Directory admin center. Login using a personal account (aka: Microsoft Account) or Work or School Account.

2. Select Azure Active Directory in the left-hand navigation, then select App registrations under Manage.

   

3. Select New registration. On the Register an application page, set the values as follows.

   • Set Name to Office Add-in Graph Tutorial.
   • Set Supported account types to Accounts in any organizational directory and personal Microsoft accounts.
   • Under Redirect URI, set the first drop-down to Single-page application (SPA) and set the value to https://localhost:3000/consent.html.

   

4. Select Register. On the Office Add-in Graph Tutorial page, copy the value of the Application (client) ID and save it, you will need it in the next step.

   

5. Select Authentication under Manage. Locate the Implicit grant section and enable Access tokens and ID tokens. Select Save.

   

6. Select Certificates & secrets under Manage. Select the New client secret button. Enter a value in Description and select one of the options for Expires and select Add.

7. Copy the client secret value before you leave this page. You will need it in the next step.

   Important

   This client secret is never shown again, so make sure you copy it now.

8. Select API permissions under Manage, then select Add a permission.

9. Select Microsoft Graph, then Delegated permissions.

10. Select the following permissions, then select Add permissions.

    • offline_access - this will allow the app to refresh access tokens when they expire.
    • Calendars.ReadWrite - this will allow the app to read and write to the user's calendar.
    • MailboxSettings.Read - this will allow the app to get the user's time zone from their mailbox settings.

    

Configure Office Add-in single sign-on

In this section you'll update the app registration to support Office Add-in single sign-on (SSO).

1. Select Expose an API. In the Scopes defined by this API section, select Add a scope. When prompted to set an Application ID URI, set the value to api://localhost:3000/YOUR_APP_ID_HERE, replacing YOUR_APP_ID_HERE with the application ID. Choose Save and continue.

2. Fill in the fields as follows and select Add scope.

   • Scope name: access_as_user
   • Who can consent?: Admins and users
   • Admin consent display name: Access the app as the user
   • Admin consent description: Allows Office Add-ins to call the app's web APIs as the current user.
   • User consent display name: Access the app as you
   • User consent description: Allows Office Add-ins to call the app's web APIs as you.
   • State: Enabled

   

3. In the Authorized client applications section, select Add a client application. Enter a client ID from the following list, enable the scope under Authorized scopes, and select Add application. Repeat this process for each of the client IDs in the list.

   • d3590ed6-52b3-4102-aeff-aad2292ab01c (Microsoft Office)
   • ea5a67f6-b6f3-4338-b240-c655ddc3cc8e (Microsoft Office)
   • 57fb890c-0dab-4253-a5e0-7188c88b2bb4 (Office on the web)
   • 08e18876-6177-487e-b8b5-cf950c1e598c (Office on the web)

Add Azure AD authentication

• 35 minutes remaining

In this exercise you will enable Office Add-in single sign-on (SSO) in the add-in, and extend the web API to support on-behalf-of flow. This is required to obtain the necessary OAuth access token to call the Microsoft Graph.

Overview

Office Add-in SSO provides an access token, but that token is only enables the add-in to call it's own web API. It does not enable direct access to the Microsoft Graph. The process works as follows.

1. The add-in gets a token by calling getAccessToken. This token's audience (the aud claim) is the application ID of the add-in's app registration.
2. The add-in sends this token in the Authorization header when it makes a call to the web API.
3. The web API validates the token, then uses the on-behalf-of flow to exchange this token for a Microsoft Graph token. This new token's audience is https://graph.microsoft.com.
4. The web API uses the new token to make calls to the Microsoft Graph, and returns the results back to the add-in.

Configure the solution

1. Open ./.env and update the AZURE_APP_ID and AZURE_CLIENT_SECRET with the application ID and client secret from your app registration.

   Important

   If you're using source control such as git, now would be a good time to exclude the .env file from source control to avoid inadvertently leaking your app ID and client secret.

2. Open ./manifest/manifest.xml and replace all instances of YOUR_APP_ID_HERE with the application ID from your app registration.

3. Create a new file in the ./src/addin directory named config.js and add the following code, replacing YOUR_APP_ID_HERE with the application ID from your app registration.

   authConfig = {
     clientId: 'YOUR_APP_ID_HERE'
   };
   

Implement sign-in

1. Open ./src/api/auth.ts and add the following import statements at the top of the file.

   import jwt, { SigningKeyCallback, JwtHeader } from 'jsonwebtoken';
   import jwksClient from 'jwks-rsa';
   import * as msal from '@azure/msal-node';
   

2. Add the following code after the import statements.

   // Initialize an MSAL confidential client
   const msalClient = new msal.ConfidentialClientApplication({
     auth: {
       clientId: process.env.AZURE_APP_ID!,
       clientSecret: process.env.AZURE_CLIENT_SECRET!
     }
   });
   
   const keyClient = jwksClient({
     jwksUri: 'https://login.microsoftonline.com/common/discovery/v2.0/keys'
   });
   
   // Parses the JWT header and retrieves the appropriate public key
   function getSigningKey(header: JwtHeader, callback: SigningKeyCallback): void {
     if (header) {
       keyClient.getSigningKey(header.kid!, (err, key) => {
         if (err) {
           callback(err, undefined);
         } else {
           callback(null, key.getPublicKey());
         }
       });
     }
   }
   
   // Validates a JWT and returns it if valid
   async function validateJwt(authHeader: string): Promise<string | null> {
     return new Promise((resolve, reject) => {
       const token = authHeader.split(' ')[1];
   
       // Ensure that the audience matches the app ID
       // and the signature is valid
       const validationOptions = {
         audience: process.env.AZURE_APP_ID
       };
   
       jwt.verify(token, getSigningKey, validationOptions, (err, payload) => {
         if (err) {
           console.log(`Verify error: ${JSON.stringify(err)}`);
           resolve(null);
         } else {
           resolve(token);
         }
       });
     });
   }
   
   // Gets a Graph token from the API token contained in the
   // auth header
   export async function getTokenOnBehalfOf(authHeader: string): Promise<string | undefined> {
     // Validate the supplied token if present
     const token = await validateJwt(authHeader);
   
     if (token) {
       const result = await msalClient.acquireTokenOnBehalfOf({
         oboAssertion: token,
         skipCache: true,
         scopes: ['https://graph.microsoft.com/.default']
       });
   
       return result?.accessToken;
     }
   }
   

   This code initializes an MSAL confidential client, and exports a function to get a Graph token from the token sent by the add-in.

3. Add the following code before the export default authRouter; line.

   // Checks if the add-in token can be silently exchanged
   // for a Graph token. If it can, the user is considered
   // authenticated. If not, then the add-in needs to do an
   // interactive login so the user can consent.
   authRouter.get('/status',
     async function(req, res) {
       // Validate access token
       const authHeader = req.headers['authorization'];
       if (authHeader) {
         try {
           const graphToken = await getTokenOnBehalfOf(authHeader);
   
           // If a token was returned, consent is already
           // granted
           if (graphToken) {
             console.log(`Graph token: ${graphToken}`);
             res.status(200).json({
               status: 'authenticated'
             });
           } else {
             // Respond that consent is required
             res.status(200).json({
               status: 'consent_required'
             });
           }
         } catch (error) {
           // Respond that consent is required if the error indicates,
           // otherwise return the error.
           const payload = error.name === 'InteractionRequiredAuthError' ?
             { status: 'consent_required' } :
             { status: 'error', error: error};
   
           res.status(200).json(payload);
         }
       } else {
         // No auth header
         res.status(401).end();
       }
     }
   );
   

   This code implements an API (GET /auth/status) that checks if the add-in token can be silently exchanged for a Graph token. The add-in will use this API to determine if it needs to present an interactive login to the user.

4. Open ./src/addin/taskpane.js and add the following code to the file.

   // Handle to authentication pop dialog
   let authDialog = undefined;
   
   // Build a base URL from the current location
   function getBaseUrl() {
     return location.protocol + '//' + location.hostname +
     (location.port ? ':' + location.port : '');
   }
   
   // Process the response back from the auth dialog
   function processConsent(result) {
     const message = JSON.parse(result.message);
   
     authDialog.close();
     if (message.status === 'success') {
       showMainUi();
     } else {
       const error = JSON.stringify(message.result, Object.getOwnPropertyNames(message.result));
       showStatus(`An error was returned from the consent dialog: ${error}`, true);
     }
   }
   
   // Use the Office Dialog API to show the interactive
   // login UI
   function showConsentPopup() {
     const authDialogUrl = `${getBaseUrl()}/consent.html`;
   
     Office.context.ui.displayDialogAsync(authDialogUrl,
       {
         height: 60,
         width: 30,
         promptBeforeOpen: false
       },
       (result) => {
         if (result.status === Office.AsyncResultStatus.Succeeded) {
           authDialog = result.value;
           authDialog.addEventHandler(Office.EventType.DialogMessageReceived, processConsent);
         } else {
           // Display error
           const error = JSON.stringify(error, Object.getOwnPropertyNames(error));
           showStatus(`Could not open consent prompt dialog: ${error}`, true);
         }
       });
   }
   
   // Inform the user we need to get their consent
   function showConsentUi() {
     $('.container').empty();
     $('<p/>', {
       class: 'ms-fontSize-24 ms-fontWeight-bold',
       text: 'Consent for Microsoft Graph access needed'
     }).appendTo('.container');
     $('<p/>', {
       class: 'ms-fontSize-16 ms-fontWeight-regular',
       text: 'In order to access your calendar, we need to get your permission to access the Microsoft Graph.'
     }).appendTo('.container');
     $('<p/>', {
       class: 'ms-fontSize-16 ms-fontWeight-regular',
       text: 'We only need to do this once, unless you revoke your permission.'
     }).appendTo('.container');
     $('<p/>', {
       class: 'ms-fontSize-16 ms-fontWeight-regular',
       text: 'Please click or tap the button below to give permission (opens a popup window).'
     }).appendTo('.container');
     $('<button/>', {
       class: 'primary-button',
       text: 'Give permission'
     }).on('click', showConsentPopup)
     .appendTo('.container');
   }
   
   // Display a status
   function showStatus(message, isError) {
     $('.status').empty();
     $('<div/>', {
       class: `status-card ms-depth-4 ${isError ? 'error-msg' : 'success-msg'}`
     }).append($('<p/>', {
       class: 'ms-fontSize-24 ms-fontWeight-bold',
       text: isError ? 'An error occurred' : 'Success'
     })).append($('<p/>', {
       class: 'ms-fontSize-16 ms-fontWeight-regular',
       text: message
     })).appendTo('.status');
   }
   
   function toggleOverlay(show) {
     $('.overlay').css('display', show ? 'block' : 'none');
   }
   

   This code adds functions to update the UI, and to use the Office Dialog API to initiate an interactive authentication flow.

5. Add the following function to implement a temporary main UI.

   function showMainUi() {
     $('.container').empty();
     $('<p/>', {
       class: 'ms-fontSize-24 ms-fontWeight-bold',
       text: 'Authenticated!'
     }).appendTo('.container');
   }
   

6. Replace the existing Office.onReady call with the following.

   Office.onReady(info => {
     // Only run if we're inside Excel
     if (info.host === Office.HostType.Excel) {
       $(async function() {
         let apiToken = '';
         try {
           apiToken = await OfficeRuntime.auth.getAccessToken({ allowSignInPrompt: true });
           console.log(`API Token: ${apiToken}`);
         } catch (error) {
           console.log(`getAccessToken error: ${JSON.stringify(error)}`);
           // Fall back to interactive login
           showConsentUi();
         }
   
         // Call auth status API to see if we need to get consent
         const authStatusResponse = await fetch(`${getBaseUrl()}/auth/status`, {
           headers: {
             authorization: `Bearer ${apiToken}`
           }
         });
   
         const authStatus = await authStatusResponse.json();
         if (authStatus.status === 'consent_required') {
           showConsentUi();
         } else {
           // report error
           if (authStatus.status === 'error') {
             const error = JSON.stringify(authStatus.error,
               Object.getOwnPropertyNames(authStatus.error));
             showStatus(`Error checking auth status: ${error}`, true);
           } else {
             showMainUi();
           }
         }
       });
     }
   });
   

   Consider what this code does.

   • When the task pane first loads, it calls getAccessToken to get a token scoped for the add-in's web API.
   • It uses that token to call the /auth/status API to check if the user has given consent to the Microsoft Graph scopes yet.
     • If the user has not consented, it uses a pop-up window to get the user's consent through an interactive login.
     • If the user has consented, it loads the main UI.

Getting user consent

Even though the add-in is using SSO, the user still has to consent to the add-in accessing their data via Microsoft Graph. Getting consent is a one-time process. Once the user has granted consent, the SSO token can be exchanged for a Graph token without any user interaction. In this section you'll implement the consent experience in the add-in using msal-browser.

1. Create a new file in the ./src/addin directory named consent.js and add the following code.

   'use strict';
   
   const msalClient = new msal.PublicClientApplication({
     auth: {
       clientId: authConfig.clientId
     }
   });
   
   const msalRequest = {
     scopes: [
       'https://graph.microsoft.com/.default'
     ]
   };
   
   // Function that handles the redirect back to this page
   // once the user has signed in and granted consent
   function handleResponse(response) {
     localStorage.removeItem('msalCallbackExpected');
     if (response !== null) {
       localStorage.setItem('msalAccountId', response.account.homeId);
       Office.context.ui.messageParent(JSON.stringify({ status: 'success', result: response.accessToken }));
     }
   }
   
   Office.initialize = function () {
     if (Office.context.ui.messageParent) {
       // Let MSAL process a redirect response if that's what
       // caused this page to load.
       msalClient.handleRedirectPromise()
         .then(handleResponse)
         .catch((error) => {
           console.log(error);
           Office.context.ui.messageParent(JSON.stringify({ status: 'failure', result: error }));
         });
   
       // If we're not expecting a callback (because this is
       // the first time the page has loaded), then start the
       // login process
       if (!localStorage.getItem('msalCallbackExpected')) {
         // Set the msalCallbackExpected property so we don't
         // make repeated token requests
         localStorage.setItem('msalCallbackExpected', 'yes');
   
         // If the user has signed into this machine before
         // do a token request, otherwise do a login
         if (localStorage.getItem('msalAccountId')) {
           msalClient.acquireTokenRedirect(msalRequest);
         } else {
           msalClient.loginRedirect(msalRequest);
         }
       }
     }
   }
   

   This code does login for the user, requesting the set of Microsoft Graph permissions that are configured on the app registration.

2. Create a new file in the ./src/addin directory named consent.html and add the following code.

   <!DOCTYPE html>
   <html>
     <head>
       <script src="https://appsforoffice.microsoft.com/lib/beta/hosted/office.js"></script>
       <script src="https://alcdn.msauth.net/browser/2.6.1/js/msal-browser.min.js"></script>
       <script src="config.js"></script>
       <script src="consent.js"></script>
     </head>
     <body class="ms-Fabric">
       <p>Authenticating...</p>
     </body>
   </html>
   

   This code implements a basic HTML page to load the consent.js file. This page will be loaded in a pop-up dialog.

3. Save all of your changes and restart the server.

4. Re-upload your manifest.xml file using the same steps in Side-load the add-in in Excel.

5. Select the Import Calendar button on the Home tab to open the task pane.

6. Select the Give permission button in the task pane to launch the consent dialog in a pop-up window. Sign in and grant consent.

7. The task pane updates with an "Authenticated!" message. You can check the tokens as follows.

   • In your brower's developer tools, the API token is shown in the Console.
   • In your CLI where you are running the Node.js server, the Graph token is printed.

   You can compare these token at https://jwt.ms. Notice that the API token's audience (aud) is set to the application ID of your app registration, and the scope (scp) is access_as_user.

Get a calendar view

• 20 minutes remaining

In this exercise you will incorporate Microsoft Graph into the application. For this application, you will use the microsoft-graph-client library to make calls to Microsoft Graph.

Get calendar events from Outlook

Start by adding an API to get a calendar view from the user's calendar.

1. Open ./src/api/graph.ts and add the following import statements to the top of the file.

   import { zonedTimeToUtc } from 'date-fns-tz';
   import { findIana } from 'windows-iana';
   import * as graph from '@microsoft/microsoft-graph-client';
   import { Event, MailboxSettings } from 'microsoft-graph';
   import 'isomorphic-fetch';
   import { getTokenOnBehalfOf } from './auth';
   

2. Add the following function to initialize the Microsoft Graph SDK and return a Client.

   async function getAuthenticatedClient(authHeader: string): Promise<graph.Client> {
     const accessToken = await getTokenOnBehalfOf(authHeader);
   
     return graph.Client.init({
       authProvider: (done) => {
         // Call the callback with the
         // access token
         done(null, accessToken!);
       }
     });
   }
   

3. Add the following function to get the user's time zone from their mailbox settings, and to convert that value to an IANA time zone identifier.

   interface TimeZones {
     // The string returned by Microsoft Graph
     // Could be Windows name or IANA identifier.
     graph: string;
     // The IANA identifier
     iana: string;
   }
   
   async function getTimeZones(client: graph.Client): Promise<TimeZones> {
     // Get mailbox settings to determine user's
     // time zone
     const settings: MailboxSettings = await client
       .api('/me/mailboxsettings')
       .get();
   
     // Time zone from Graph can be in IANA format or a
     // Windows time zone name. If Windows, convert to IANA
     const ianaTzs = findIana(settings.timeZone!)
     const ianaTz = ianaTzs ? ianaTzs[0] : null;
   
     const returnValue: TimeZones = {
       graph: settings.timeZone!,
       iana: ianaTz ?? settings.timeZone!
     };
   
     return returnValue;
   }
   

4. Add the following function (below the const graphRouter = Router(); line) to implement an API endpoint (GET /graph/calendarview).

   graphRouter.get('/calendarview',
     async function(req, res) {
       const authHeader = req.headers['authorization'];
   
       if (authHeader) {
         try {
           const client = await getAuthenticatedClient(authHeader);
   
           const viewStart = req.query['viewStart']?.toString();
           const viewEnd = req.query['viewEnd']?.toString();
   
           const timeZones = await getTimeZones(client);
   
           // Convert the start and end times into UTC from the user's time zone
           const utcViewStart = zonedTimeToUtc(viewStart!, timeZones.iana);
           const utcViewEnd = zonedTimeToUtc(viewEnd!, timeZones.iana);
   
           // GET events in the specified window of time
           const eventPage: graph.PageCollection = await client
             .api('/me/calendarview')
             // Header causes start and end times to be converted into
             // the requested time zone
             .header('Prefer', `outlook.timezone="${timeZones.graph}"`)
             // Specify the start and end of the calendar view
             .query({
               startDateTime: utcViewStart.toISOString(),
               endDateTime: utcViewEnd.toISOString()
             })
             // Only request the fields used by the app
             .select('subject,start,end,organizer')
             // Sort the results by the start time
             .orderby('start/dateTime')
             // Limit to at most 25 results in a single request
             .top(25)
             .get();
   
           const events: any[] = [];
   
           // Set up a PageIterator to process the events in the result
           // and request subsequent "pages" if there are more than 25
           // on the server
           const callback: graph.PageIteratorCallback = (event) => {
             // Add each event into the array
             events.push(event);
             return true;
           };
   
           const iterator = new graph.PageIterator(client, eventPage, callback, {
             headers: {
               'Prefer': `outlook.timezone="${timeZones.graph}"`
             }
           });
           await iterator.iterate();
   
           // Return the array of events
           res.status(200).json(events);
         } catch (error) {
           console.log(error);
           res.status(500).json(error);
         }
       } else {
         // No auth header
         res.status(401).end();
       }
     }
   );
   

   Consider what this code does.

   • It gets the user's time zone and uses that to convert the start and end of the requested calendar view into UTC values.
   • It does a GET to the /me/calendarview Graph API endpoint.
     • It uses the header function to set the Prefer: outlook.timezone header, causing the start and end times of the returned events to be adjusted to the user's time zone.
     • It uses the query function to add the startDateTime and endDateTime parameters, setting the start and end of the calendar view.
     • It uses the select function to request only the fields used by the add-in.
     • It uses the orderby function to sort the results by the start time.
     • It uses the top function to limit the results in a single request to 25.
   • It uses a PageIteratorCallback object to iterate through the results and to make additional requests if more pages of results are available.

Update the UI

Now let's update the task pane to allow the user to specify a start and end date for the calendar view.

1. Open ./src/addin/taskpane.js and replace the existing showMainUi function with the following.

   function showMainUi() {
     $('.container').empty();
   
     // Use luxon to calculate the start
     // and end of the current week. Use
     // those dates to set the initial values
     // of the date pickers
     const now = luxon.DateTime.local();
     const startOfWeek = now.startOf('week');
     const endOfWeek = now.endOf('week');
   
     $('<h2/>', {
       class: 'ms-fontSize-24 ms-fontWeight-semibold',
       text: 'Select a date range to import'
     }).appendTo('.container');
   
     // Create the import form
     $('<form/>').on('submit', getCalendar)
     .append($('<label/>', {
       class: 'ms-fontSize-16 ms-fontWeight-semibold',
       text: 'Start'
     })).append($('<input/>', {
       class: 'form-input',
       type: 'date',
       value: startOfWeek.toISODate(),
       id: 'viewStart'
     })).append($('<label/>', {
       class: 'ms-fontSize-16 ms-fontWeight-semibold',
       text: 'End'
     })).append($('<input/>', {
       class: 'form-input',
       type: 'date',
       value: endOfWeek.toISODate(),
       id: 'viewEnd'
     })).append($('<input/>', {
       class: 'primary-button',
       type: 'submit',
       id: 'importButton',
       value: 'Import'
     })).appendTo('.container');
   
     $('<hr/>').appendTo('.container');
   
     $('<h2/>', {
       class: 'ms-fontSize-24 ms-fontWeight-semibold',
       text: 'Add event to calendar'
     }).appendTo('.container');
   
     // Create the new event form
     $('<form/>').on('submit', createEvent)
     .append($('<label/>', {
       class: 'ms-fontSize-16 ms-fontWeight-semibold',
       text: 'Subject'
     })).append($('<input/>', {
       class: 'form-input',
       type: 'text',
       required: true,
       id: 'eventSubject'
     })).append($('<label/>', {
       class: 'ms-fontSize-16 ms-fontWeight-semibold',
       text: 'Start'
     })).append($('<input/>', {
       class: 'form-input',
       type: 'datetime-local',
       required: true,
       id: 'eventStart'
     })).append($('<label/>', {
       class: 'ms-fontSize-16 ms-fontWeight-semibold',
       text: 'End'
     })).append($('<input/>', {
       class: 'form-input',
       type: 'datetime-local',
       required: true,
       id: 'eventEnd'
     })).append($('<input/>', {
       class: 'primary-button',
       type: 'submit',
       id: 'importButton',
       value: 'Create'
     })).appendTo('.container');
   }
   

   This code adds a simple form so the user can specify a start and end date. It also implements a second form for creating a new event. That form doesn't do anything for now, you'll implement that feature in the next section.

2. Add the following code to the file to create a table in the active worksheet containing the events retrieved from the calendar view.

   const DAY_MILLISECONDS = 86400000;
   const DAY_MINUTES = 1440;
   const EXCEL_DATE_OFFSET = 25569;
   
   // Excel date cells require an OLE Automation date format
   // You can use the Moment-MSDate plug-in
   // (https://docs.microsoft.com/office/dev/add-ins/excel/excel-add-ins-ranges-advanced#work-with-dates-using-the-moment-msdate-plug-in)
   // Or you can do the conversion yourself
   function convertDateToOAFormat(dateTime) {
     const date = new Date(dateTime);
   
     // Get the time zone offset for the browser's time zone
     // since all of the dates here are handled in that time zone
     const tzOffset = date.getTimezoneOffset() / DAY_MINUTES;
   
     // Calculate the OLE Automation date, which is
     // the number of days since midnight, December 30, 1899
     const oaDate = date.getTime() / DAY_MILLISECONDS + EXCEL_DATE_OFFSET - tzOffset;
     return oaDate;
   }
   
   async function writeEventsToSheet(events) {
     await Excel.run(async (context) => {
       const sheet = context.workbook.worksheets.getActiveWorksheet();
       const eventsTable = sheet.tables.add('A1:D1', true);
   
       // Create the header row
       eventsTable.getHeaderRowRange().values = [[
         'Subject',
         'Organizer',
         'Start',
         'End'
       ]];
   
       // Create the data rows
       const data = [];
       events.forEach((event) => {
         data.push([
           event.subject,
           event.organizer.emailAddress.name,
           convertDateToOAFormat(event.start.dateTime),
           convertDateToOAFormat(event.end.dateTime)
         ]);
       });
   
       eventsTable.rows.add(null, data);
   
       const tableRange = eventsTable.getRange();
       tableRange.numberFormat = [["[$-409]m/d/yy h:mm AM/PM;@"]];
       tableRange.format.autofitColumns();
       tableRange.format.autofitRows();
   
       try {
         await context.sync();
       } catch (err) {
         console.log(`Error: ${JSON.stringify(err)}`);
         showStatus(err, true);
       }
     });
   }
   

3. Add the following function to call the calendar view API.

   async function getCalendar(evt) {
     evt.preventDefault();
     toggleOverlay(true);
   
     try {
       const apiToken = await OfficeRuntime.auth.getAccessToken({ allowSignInPrompt: true });
   
       const viewStart = $('#viewStart').val();
       const viewEnd = $('#viewEnd').val();
   
       const requestUrl =
         `${getBaseUrl()}/graph/calendarview?viewStart=${viewStart}&viewEnd=${viewEnd}`;
   
       const response = await fetch(requestUrl, {
         headers: {
           authorization: `Bearer ${apiToken}`
         }
       });
   
       if (response.ok) {
         const events = await response.json();
         writeEventsToSheet(events);
         showStatus(`Imported ${events.length} events`, false);
       } else {
         const error = await response.json();
         showStatus(`Error getting events from calendar: ${JSON.stringify(error)}`, true);
       }
   
       toggleOverlay(false);
     } catch (err) {
       console.log(`Error: ${JSON.stringify(err)}`);
       showStatus(`Exception getting events from calendar: ${JSON.stringify(error)}`, true);
     }
   }
   

4. Save all of your changes, restart the server, and refresh the task pane in Excel (close any open task panes and re-open).

   

5. Choose start and end dates and choose Import.

   

Create a new event

• 10 minutes remaining

In this section you will add the ability to create events on the user's calendar.

Implement the API

1. Open ./src/api/graph.ts and add the following code to implement a new event API (POST /graph/newevent).

   graphRouter.post('/newevent',
     async function(req, res) {
       const authHeader = req.headers['authorization'];
   
       if (authHeader) {
         try {
           const client = await getAuthenticatedClient(authHeader);
   
           const timeZones = await getTimeZones(client);
   
           // Create a new Graph Event object
           const newEvent: Event = {
             subject: req.body['eventSubject'],
             start: {
               dateTime: req.body['eventStart'],
               timeZone: timeZones.graph
             },
             end: {
               dateTime: req.body['eventEnd'],
               timeZone: timeZones.graph
             }
           };
   
           // POST /me/events
           await client.api('/me/events')
             .post(newEvent);
   
           // Send a 201 Created
           res.status(201).end();
         } catch (error) {
           console.log(error);
           res.status(500).json(error);
         }
       } else {
         // No auth header
         res.status(401).end();
       }
     }
   );
   

2. Open ./src/addin/taskpane.js and add the following function to call the new event API.

   async function createEvent(evt) {
     evt.preventDefault();
     toggleOverlay(true);
   
     const apiToken = await OfficeRuntime.auth.getAccessToken({ allowSignInPrompt: true });
   
     const payload = {
       eventSubject: $('#eventSubject').val(),
       eventStart: $('#eventStart').val(),
       eventEnd: $('#eventEnd').val()
     };
   
     const requestUrl = `${getBaseUrl()}/graph/newevent`;
   
     const response = await fetch(requestUrl, {
       method: 'POST',
       headers: {
         authorization: `Bearer ${apiToken}`,
         'Content-Type': 'application/json'
       },
       body: JSON.stringify(payload)
     });
   
     if (response.ok) {
       showStatus('Event created', false);
     } else {
       const error = await response.json();
       showStatus(`Error creating event: ${JSON.stringify(error)}`, true);
     }
   
     toggleOverlay(false);
   }
   

3. Save all of your changes, restart the server, and refresh the task pane in Excel (close any open task panes and re-open).

   

4. Fill in the form and choose Create. Verify that the event is added to the user's calendar.

Congratulations!

• 100% complete!

You've completed the Office Add-in Microsoft Graph tutorial. Now that you have a working add-in that calls Microsoft Graph, you can experiment and add new features. Visit the Overview of Microsoft Graph to see all of the data you can access with Microsoft Graph.

Feedback

Please provide any feedback on this tutorial in the GitHub repository.