Office Add-ins with Contextual Bots via Back Channel
Last week I published a post on using the SharePoint Framework to embed contextual bots in SharePoint. In it, I described how the same approach could be used to embed a contextual bot in an Office Add-in. This post will illustrate how to do exactly that. I will walk through the development of a modern day "Clippy" powered by the Bot Framework and Office.js. The sample that accompanies this post works in Word, Excel, and Outlook, but could also be updated for OneNote and PowerPoint.
[embed]https://www.youtube.com/watch?v=LpO37oSmWcY[/embed]
Determine the host
Clippy was a help tool that was pervasive across the Office suite. As such, I wanted to build my Clippy add-in to run in most of the Office clients. This required my add-in and bot to be "client aware". If you are writing an Office Add-in for a specific client, then you can likely omit this section and code directly against that Office product. For Word/Excel/PowerPoint/OneNote you can use Office.context.host to determine what Office product the add-in is being hosted in. For Outlook/OWA, you can check if Office.context.mailbox is defined. In the Clippy Bot, I check for the host as soon as the add-in is launched and pass that information to my bot via an "initialize" event to the back channel. You can use any event name you want with the back channel and can use the name for event listening logic.
Initializing the add-in upon launch
<div id="botContainer"></div>
<script src="https://unpkg.com/botframework-webchat/botchat.js"></script>
<script type="text/javascript">
Office.initialize = function (reason) {
var host = "", user = { id: "", name: "" };
// Determine the host and user information (Outlook)
if (Office.context.host) {
host = Office.context.host;
user.id = host + " User";
user.name = host + " User";
}
else if (Office.context.mailbox) {
host = "Outlook";
user.id = Office.context.mailbox.userProfile.emailAddress;
user.name = Office.context.mailbox.userProfile.displayName;
}
// Initialize the bot connection and webchat component
var botConnection = new BotChat.DirectLine({
secret: "ezjFbUYS_TRUNCATED_0w6f0"
});
BotChat.App({
botConnection: botConnection,
user: user
}, document.getElementById("botContainer"));
// Post the initialize event to the bot backend
botConnection
.postActivity({ type: "event", name: "initialize", value: host, from: user })
.subscribe(console.log("success"));
Mail add-ins provide better context
Office.js provides richer contextual information for Mail Add-ins such as user context and the abilities to get to additional data in Exchange/Exchange Online (all in the context of the mailbox user). In the code above, you may have noticed I use Office.js to retrieve user information via Office.context.mailbox.userProfile and passed the details to my bot (via back channel). However, I could have gone much further with Office.js by retrieving tokens that can be used to call into Exchange/Exchange Online (Office.context.mailbox.getCallbackTokenAsync or Office.context.mailbox.makeEwsRequestAsync).
Bot Logic
Earlier in the post I demonstrated sending an "initialize" event through the back channel from my add-in to the bot. The bot looks for events as activity is sent to the messages endpoint. When an activity of type "event" and name "initialize" occurs, the bot pulls out the data sent with the event (host client and user information) and stores it in the bot state.
Listening for events in the bot's messaging endpoint
[BotAuthentication]
public class MessagesController : ApiController
{
// POST: api/Messages
public async Task<HttpResponseMessage> Post([FromBody]Activity activity)
{
if (activity.Type == ActivityTypes.Event)
{
if (activity.Name == "initialize")
{
// Get the Office host from activity Properties then save it into BotState
var host = activity.Value.ToString();
var state = activity.GetStateClient();
var userdata = state.BotState.GetUserData(activity.ChannelId, activity.From.Id);
userdata.SetProperty<string>("host", host);
userdata.SetProperty<string>("user", activity.From.Name);
state.BotState.SetUserData(activity.ChannelId, activity.From.Id, userdata);
// Route the activity to the correct dialog
await routeActivity(activity);
}
...TRUNCATED
Messages sent into the bot are dispatched to client-specific dialogs corresponding to the different Office clients that can host the add-in.
Dispatching messages to client-specific dialogs
private async Task routeActivity(Activity activity)
{
// Make sure we know the host
var state = activity.GetStateClient();
var userdata = state.BotState.GetUserData(activity.ChannelId, activity.From.Id);
var host = userdata.GetProperty<string>("host");
switch (host)
{
case "Word":
await Conversation.SendAsync(activity, () => new WordDialog());
break;
case "Excel":
await Conversation.SendAsync(activity, () => new ExcelDialog());
break;
case "Outlook":
await Conversation.SendAsync(activity, () => new OutlookDialog());
break;
default:
ConnectorClient connector = new ConnectorClient(new Uri(activity.ServiceUrl));
Activity reply = activity.CreateReply($"Sorry, I can't figure out where you are running me from. You may not have given me enough time to initialize.");
await connector.Conversations.ReplyToActivityAsync(reply);
break;
}
}
Each of the client-specific dialogs offer the user a choice of operations. For example, the ExcelDialog might allow the user to insert a Range or a Chart. When they select an operation, the bot sends an event with the name "officeOperation" to the add-in through the back channel. In the case of the Clippy Bot, I am only sending the operation name, but it could be any complex data.
Client-specific dialog logic
[Serializable]
public class ExcelDialog : IDialog<IMessageActivity>
{
public async Task StartAsync(IDialogContext context)
{
context.Wait(MessageReceivedAsync);
}
public async Task MessageReceivedAsync(IDialogContext context, IAwaitable<IMessageActivity> result)
{
var msg = await result;
string[] options = new string[] { "Chart", "Range" };
string prompt = $"I see you are running the Clippy Bot in Excel. Select something and I'll insert it into a *NEW* worksheet:";
PromptDialog.Choice(context, async (IDialogContext choiceContext, IAwaitable<string> choiceResult) =>
{
var selection = await choiceResult;
OfficeOperation op = (OfficeOperation)Enum.Parse(typeof(OfficeOperation), selection);
// Send the operation through the backchannel using Event activity
var reply = choiceContext.MakeMessage() as IEventActivity;
reply.Type = "event";
reply.Name = "officeOperation";
reply.Value = op.ToString();
await choiceContext.PostAsync((IMessageActivity)reply);
}, options, prompt);
}
}
Add-in listening on back channel
The add-in can easily listen for events sent through the back channel by leveraging the filter and subscribe functions of the activity on the bot connection. In the Clippy Bot, the add-in listens for activity of type=event and name=officeOperation. When one of these activities comes through, the activity.value contains the data sent from the bot. In the case of Clippy Bot, this is the operation the add-in should perform with office.js.
Listening and responding to events in the add-in
// Listen for events from the bot back channel
botConnection.activity$
.filter(function (a) {
return (a.type === "event" && a.name === "officeOperation");
})
.subscribe(function (a) {
switch (a.value)
{
case "Reply":
Office.context.mailbox.item.displayReplyForm(
{
"htmlBody": "<h1>Hello from the Clippy Bot!!!</h1><img href='https://https://klippybot.azurewebsites.net/images/clippy.png' alt='Clippy Bot Image' />"
});
botConnection.postActivity({ type: "event", name: "confirmation", value: true, from: user }).subscribe(console.log("bot operation success"));
break;
case "ReplyAll":
Office.context.mailbox.item.displayReplyAllForm(
{
"htmlBody": "<h1>Hello from the Clippy Bot!!!</h1><img href='https://https://klippybot.azurewebsites.net/images/clippy.png' alt='Clippy Bot Image' />"
});
botConnection.postActivity({ type: "event", name: "confirmation", value: true, from: user }).subscribe(console.log("bot operation success"));
break;
case "Chart":
if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
Excel.run(function (context) {
var sheet = context.workbook.worksheets.add("ClippySheet" + cnt++);
var rangeData = [["Character", "Coolness Score"],
["Clippy", 10],
["Cortana", 8],
["Siri", 4],
["Alexa", 6]];
sheet.getRange("A1:B5").values = rangeData;
sheet.getRange("A1:B1").format.font.bold = true;
sheet.tables.add("A1:B5", true);
sheet.charts.add("ColumnClustered", sheet.getRange("A1:B5"), "auto");
return context.sync().then(function () { botConnection.postActivity({ type: "event", name: "confirmation", value: true, from: user }).subscribe(console.log("bot operation success")); });
// Ignore any errors on context.sync
}); // Ignore any errors on Word.run
} // Ignore old versions of Office
break;
case "Range":
if (Office.context.requirements.isSetSupported("ExcelApi", "1.2")) {
Excel.run(function (context) {
var sheet = context.workbook.worksheets.add("ClippySheet" + cnt++);
var rangeData = [["Character", "Coolness Score"],
["Clippy", 10],
["Cortana", 8],
["Siri", 4],
["Alexa", 6]];
sheet.getRange("A1:B5").values = rangeData;
sheet.getRange("A1:B1").format.font.bold = true;
sheet.tables.add("A1:B5", true);
return context.sync().then(function () { botConnection.postActivity({ type: "event", name: "confirmation", value: true, from: user }).subscribe(console.log("bot operation success")); });
// Ignore any errors on context.sync
}); // Ignore any errors on Word.run
} // Ignore old versions of Office
break;
case "Image":
if (Office.context.requirements.isSetSupported("WordApi", "1.2")) {
Word.run(function (context) {
context.document.body.insertInlinePictureFromBase64("iVB_TRUNCATED_5CYII=", "End");
return context.sync().then(function () { botConnection.postActivity({ type: "event", name: "confirmation", value: true, from: user }).subscribe(console.log("bot operation success")); });
// Ignore any errors on context.sync
}); // Ignore any errors on Word.run
} // Ignore old versions of Office
break;
case "Paragraph":
if (Office.context.requirements.isSetSupported("WordApi", "1.2")) {
Word.run(function (context) {
context.document.body.insertText("Hello from the Clippy Bot!!!", "End");
return context.sync().then(function () { botConnection.postActivity({ type: "event", name: "confirmation", value: true, from: user }).subscribe(console.log("bot operation success")); });
// Ignore any errors on context.sync
}); // Ignore any errors on Word.run
} // Ignore old versions of Office
break;
}
});
Final Thoughts
Although "Clippy" was just a silly way to illustrate the back channel techniques with bots and add-ins, I hope you can see how the techniques could be used to deliver powerful scenarios. The entire Clippy Bot solution is available at the GitHub repo below. Like the SharePoint Framework samples, I have checked-in the solution with a working DirectLine secret of a published bot so you can try it immediately. Enjoy!
https://github.com/richdizz/Office-Embedded-Bot-Back-Channel