Excel with TypeScript SSO

You can use the Yeoman Generator to create an Excel Add-in project using VS Code with TypeScript SSO
The link above displays the contents of these three files:

package.json 
webpack.config.js
manifest.xml

This page displays all the other important files in this project:

.ENV 
src/helpers/fallbackauthdialog.html
src/helpers/fallbackauthdialog.ts
src/helpers/fallbackauthhelper.ts
src/helpers/ssoauthhelper.ts
src/taskpane/taskpane.html
src/taskpane/taskpane.ts
src/commands/commands.html
src/commands/commands.ts
babel.config.json
tsconfig.json

.ENV

When deployed this could be placed in the Azure Key Vault.

CLIENT_ID={Application GUID} 
GRAPH_URL_SEGMENT=/me
NODE_ENV=development
PORT={PORT}
QUERY_PARAM_SEGMENT=
SCOPE=User.Read

fallbackauthdialog.html

<!DOCTYPE html> 
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<script type="text/javascript" src="https://ajax.microsoft.com/ajax/4.0/MicrosoftAjax.js"></script>
<script type="text/javascript" src="https://alcdn.msauth.net/lib/1.1.3/js/msal.min.js"></script>
<script type="text/javascript" src="https://appsforoffice.microsoft.com/lib/1.1/hosted/office.debug.js"></script>
</head>
<body>
</body>
</html>

fallbackauthdialog.ts

/* global console, localStorage, Office */ 
import * as Msal from "msal";

Office.onReady(() => {
  if (Office.context.ui.messageParent) {
    userAgentApp.handleRedirectCallback(authCallback);

    if (localStorage.getItem("loggedIn") === "yes") {
      userAgentApp.acquireTokenRedirect(requestObj);
    } else {
      userAgentApp.loginRedirect(requestObj);
    }
  }
});

const msalConfig: Msal.Configuration = {
  auth: {
    clientId: "Application GUID",
    authority: "https://login.microsoftonline.com/common",
    redirectUri: "https://localhost:{PORT}/fallbackauthdialog.html",
    navigateToLoginRequestUrl: false,
  },
  cache: {
    cacheLocation: "localStorage",
    storeAuthStateInCookie: true,
  },
};

var requestObj: Object = {
  scopes: [`https://graph.microsoft.com/User.Read`],
};

const userAgentApp: Msal.UserAgentApplication = new Msal.UserAgentApplication(msalConfig);

function authCallback(error, response) {
  if (error) {
    console.log(error);
    Office.context.ui.messageParent(JSON.stringify({ status: "failure", result: error }));
  } else {
    if (response.tokenType === "id_token") {
      console.log(response.idToken.rawIdToken);
      localStorage.setItem("loggedIn", "yes");
    } else {
      console.log("token type is:" + response.tokenType);
      Office.context.ui.messageParent(JSON.stringify({ status: "success", result: response.accessToken }));
    }
  }
}

fallbackauthhelper.ts

/* global console, location, Office */ 
import * as sso from "office-addin-sso";
import { writeDataToOfficeDocument } from "./../taskpane/taskpane";
var loginDialog;

export function dialogFallback() {
const url = "/fallbackauthdialog.html";
showLoginPopup(url);
}

async function processMessage(arg) {
console.log("Message received in processMessage: " + JSON.stringify(arg));
let messageFromDialog = JSON.parse(arg.message);

if (messageFromDialog.status === "success") {
loginDialog.close();
const response = await sso.makeGraphApiCall(messageFromDialog.result);
writeDataToOfficeDocument(response);
} else {
loginDialog.close();
sso.showMessage(JSON.stringify(messageFromDialog.error.toString()));
}
}

function showLoginPopup(url) {
var fullUrl = location.protocol + "//" + location.hostname + (location.port ? ":" + location.port : "") + url;

Office.context.ui.displayDialogAsync(fullUrl, { height: 60, width: 30 }, function (result) {
console.log("Dialog has initialized. Wiring up events");
loginDialog = result.value;
loginDialog.addEventHandler(Office.EventType.DialogMessageReceived, processMessage);
});
}

ssoauthhelper.ts

/* global OfficeRuntime */ 
import { dialogFallback } from "./fallbackauthhelper";
import * as sso from "office-addin-sso";
import { writeDataToOfficeDocument } from "./../taskpane/taskpane";
let retryGetAccessToken = 0;

export async function getGraphData(): Promise<void> {
  try {
    let bootstrapToken: string = await OfficeRuntime.auth.getAccessToken({ allowSignInPrompt: true });
    let exchangeResponse: any = await sso.getGraphToken(bootstrapToken);
    if (exchangeResponse.claims) {
      let mfaBootstrapToken: string = await OfficeRuntime.auth.getAccessToken({
        authChallenge: exchangeResponse.claims,
      });
      exchangeResponse = sso.getGraphToken(mfaBootstrapToken);
    }

    if (exchangeResponse.error) {
      handleAADErrors(exchangeResponse);
    } else {
      const response: any = await sso.makeGraphApiCall(exchangeResponse.access_token);
      writeDataToOfficeDocument(response);
      sso.showMessage("Your data has been added to the document.");
    }
  } catch (exception) {
    if (exception.code) {
      if (sso.handleClientSideErrors(exception)) {
        dialogFallback();
      }
    } else {
      sso.showMessage("EXCEPTION: " + JSON.stringify(exception));
    }
  }
}

function handleAADErrors(exchangeResponse: any): void {
  if (exchangeResponse.error_description.indexOf("AADSTS500133") !== -1 && retryGetAccessToken <= 0) {
    retryGetAccessToken++;
    sso.getGraphData();
  } else {
    dialogFallback();
  }
}

taskpane.html

<!DOCTYPE html> 
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Contoso Task Pane Add-in</title>

<link rel="stylesheet"
href="https://static2.sharepointonline.com/files/fabric/office-ui-fabric-core/9.6.1/css/fabric.min.css" />
<script type="text/javascript" src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.2.1.min.js"></script>
<script src="https://appsforoffice.microsoft.com/lib/beta/hosted/office.js" type="text/javascript"></script>

<link href="taskpane.css" rel="stylesheet" type="text/css" />
</head>

<body class="ms-font-m ms-welcome ms-Fabric">
<header class="ms-welcome__header ms-bgColor-neutralLighter">
<img width="90" height="90" src="../../assets/logo-filled.png" alt="Contoso" title="Contoso" />
<h1 class="ms-font-su">Welcome</h1>
</header>
<main class="ms-firstrun-instructionstep">
<ul class="ms-List ms-welcome__features">
<li class="ms-ListItem">
<i class="ms-Icon ms-Icon--Ribbon ms-font-xl"></i>
<span class="ms-font-m">Achieve more with Office integration</span>
</li>
<li class="ms-ListItem">
<i class="ms-Icon ms-Icon--Unlock ms-font-xl"></i>
<span class="ms-font-m">Unlock features and functionality</span>
</li>
<li class="ms-ListItem">
<i class="ms-Icon ms-Icon--Design ms-font-xl"></i>
<span class="ms-font-m">Create and visualize like a pro</span>
</li>
</ul>
<section class="ms-firstrun-instructionstep__header">
<h2 class="ms-font-m"> This add-in demonstrates how to use single sign-on by making a call to Microsoft
Graph to get user profile data.</h2>
<div class="ms-firstrun-instructionstep__header--image"></div>
</section>
<div class="ms-firstrun-instructionstep__welcome-body">
<p class="ms-font-m ms-firstrun-instructionstep__welcome-intro"><b>To use this add-in:</b></p>
<ul class="ms-List ms-firstrun-instructionstep__list">
<li class="ms-ListItem">
<span class="ms-ListItem-primaryText">Click the <b>Get My User Profile Information</b>
button.</span>
<div class="clearfix"></div>
</li>
<li class="ms-ListItem">
<span class="ms-ListItem-secondaryText">If you are not signed into Office, you are prompted to sign
in.</span>
<div class="clearfix"></div>
</li>
<li class="ms-ListItem">
<span class="ms-ListItem-primaryText">You may also be prompted to accept the app's permissions request.</span>
<div class="clearfix"></div>
</li>
<li class="ms-ListItem">
<span class="ms-ListItem-primaryText">Your user profile information will be displayed in the document.</span>
<div class="clearfix"></div>
</li>
</ul>
<br>
<p align="center">
<button id="getGraphDataButton" class="popupButton ms-Button ms-Button--primary"><span
class="ms-Button-label">Get My User Profile Information </span></button>
</p>
</div>
</main>
</body>

</html>

taskpane.ts

/* global $, document, Excel, Office */ 
import { getGraphData } from "./../helpers/ssoauthhelper";

Office.onReady((info) => {
if (info.host === Office.HostType.Excel) {
$(document).ready(function () {
$("#getGraphDataButton").click(getGraphData);
});
}
});

export function writeDataToOfficeDocument(result: Object): Promise<any> {
return Excel.run(function (context) {
const sheet = context.workbook.worksheets.getActiveWorksheet();
let data = [];
let userProfileInfo: string[] = [];
userProfileInfo.push(result["displayName"]);
userProfileInfo.push(result["jobTitle"]);
userProfileInfo.push(result["mail"]);
userProfileInfo.push(result["mobilePhone"]);
userProfileInfo.push(result["officeLocation"]);

for (let i = 0; i < userProfileInfo.length; i++) {
if (userProfileInfo[i] !== null) {
let innerArray = [];
innerArray.push(userProfileInfo[i]);
data.push(innerArray);
}
}
const rangeAddress = `B5:B${5 + (data.length - 1)}`;
const range = sheet.getRange(rangeAddress);
range.values = data;
range.format.autofitColumns();

return context.sync();
});
}

commands.html

<!DOCTYPE html> 
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<script type="text/javascript" src="https://appsforoffice.microsoft.com/lib/1.1/hosted/office.js"></script>
</head>
<body>
</body>
</html>

commands.ts

/* global global, Office, self, window */ 

Office.onReady(() => {
});

export function action(event: Office.AddinCommands.Event) {
const message: Office.NotificationMessageDetails = {
type: Office.MailboxEnums.ItemNotificationMessageType.InformationalMessage,
message: "Performed action.",
icon: "Icon.80x80",
persistent: true,
};

Office.context.mailbox.item.notificationMessages.replaceAsync("action", message);
event.completed();
}

function getGlobal() {
return typeof self !== "undefined"
? self
: typeof window !== "undefined"
? window
: typeof global !== "undefined"
? global
: undefined;
}

const g = getGlobal() as any;
g.action;

babel.config.json

{ 
"presets": ["@babel/preset-typescript"]
}

tsconfig.json

{ 
"compilerOptions": {
"allowJs": true,
"baseUrl": ".",
"esModuleInterop": true,
"experimentalDecorators": true,
"jsx": "react",
"noEmitOnError": true,
"outDir": "lib",
"sourceMap": true,
"target": "es5",
"lib": [
"es2015",
"dom"
]
},
"exclude": [
"node_modules",
"dist",
"lib",
"lib-amd",
],
"ts-node": {
"files": true
}
}

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext