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 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
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