Unlocking the Power of M365 Copilot: Access external data throught a plugin
I am very fascinated by the new copilot plugin in M365. But when you purchase the licence, you get only a limited number of possibilities. Yes, it is helpful to summarize a long email or tasks from a meeting. But using it with your internal systems and complex data will be very helpful.
You can create your own plugins that will use Microsoft Copilot.
What is a plugin in Copilot?
A Plugin for copilot is a reusable (and maybe small) piece of code (building blog). Let's talk about pro-code plugin. These types of plugins are normally extensions within a bot. So hey, It's an AI that's calling a bot.
The use-case
Let's assume you have a small database like absences and want to know who is on vacation on a specific date.
So, normally, you would write the sentence
"Hey, tell me, who is on vacation in August?"
So normally, you would get any answer, maybe a helpful one, but the M365 Copilot doesn't know anything about absence data. So, for this, the plugin comes into the game.
The Plugin Manifest
So how to create a copilot plugin is described in my post here. But you can also use the existing GitHub project (see at the end of the post)
The manifest is the descriptive entry point where the copilot emits the information. I decided to use the following definition of a compose extension.
"composeExtensions": [
{
"botId": "${{BOT_ID}}",
"commands": [
{
"id": "absence",
"context": [
"compose",
"commandBox"
],
"description": "Get the absences for the sepcific date or daterange.",
"title": "Urlaubsabfrage",
"type": "query",
"initialRun": true,
"fetchTask": false,
"parameters": [
{
"name": "startDate",
"title": "Start",
"description": "Contains the requested start date. Output is a date",
"inputType": "date"
},
{
"name": "endDate",
"title": "End",
"description": "Contains the requested start date if exists. Output is a date",
"inputType": "date"
}
]
}
]
}This will tell the M365 copilot that this plugin can fetch absence data for a date range or specific date.
This is the most important thing to do! Copilot will look into the description of each plugin and handler to identify its purpose. So, do it very declaratively to take effect.
The data structure
Next, let me describe the data structure. So, my Table structure looks like this.
So don't worry—I don't create an entry for every day in any range; I created a view for this ;). However, this will make it easy to select the data with the above query.
You will see the start and end date of the requested absence, as well as the state andusername.
Now, it's time to query this kind of data within the plugin.
The code
Now, it's time to analyze the code that I wrote. This will done in the following step
- Activate the handler
- Parse the parameters
- Select the data
- Returning the data
Activate the handler
The activation is a little no-brainer. Every handler class must derive from the TeamsActivityHandler like this:
export class PromptApp extends TeamsActivityHandler ....
In this, I will do the "magic" for getting the data and resulting it to the caller (actually as a hero card (or multiple ones).
To handle the incoming message, I must insert the handling into the message endpoint like this.
// Listen for incoming server requests.
server.post("/api/messages", async (req, res) => {
// Route received a request to adapter for processing
await adapter.process(req, res as any, async (context) => {
await promtApp.run(context);
});
});Here, I created an instance called promtApp, called the run method, and handed over the context to the method.
Parse the parameters
The input parameters are defined in the manifest.json. So remember these parts of the manifest.json
...
"parameters": [
{
"name": "startDate",
"title": "Start",
"description": "Contains the requested start date. Output is a date",
"inputType": "date"
},
{
"name": "endDate",
"title": "End",
"description": "Contains the requested start date if exists. Output is a date",
"inputType": "date"
}
...So, in the manifest, we defined two input variables
- startDate
- endDate
This will contain the start date in a defined date format and, when it exists, the end date. The name property of the parameters is now important. I will now use these names to map the parameter value to the internal class property. My mapper looks like this:
private async parseParameters(inputParameters: MessagingExtensionParameter[]): Promise<InputParameters> {
let output: InputParameters = { Start: new Date(), End: new Date() };
inputParameters.forEach((parameter: MessagingExtensionParameter) => {
switch (parameter.name) {
case "startDate":
output.Start = moment(parameter.value,'MM/DD/YYYY').toDate();
break;
case "endDate":
output.End = moment(parameter.value,'MM/DD/YYYY').toDate();
break
}
});
return output;
}Now that the input parameters are parsed, it will come to the main logic.
Select the data
To keep it very simple, I will directly select the data in the table; this is not a best practice! So do it over an or-mapper or s.th. to prevent SQL injections. So my code looks like this:
public async GetAbsencesByDate(start: Date, end: Date): Promise<AbsenceItem[]> {
let result: AbsenceItem[] = [];
try {
this.poolConnection = await sql.connect(this.config);
let m: Moment = moment(start);
let startDate: string = m.format("YYYY-MM-DD")
let query: string = `SELECT * from AbsendeByDateView where '${startDate}'[DateValue]`;
if (end != null) {
let mEnd: Moment = moment(end);
let endDate: string = mEnd.format("YYYY-MM-DD")
query = `select * from [dbo].[AbsendeByDateView] where [DateValue] between '${startDate}' and '${endDate}'`;
}
console.log(query);
var resultSet = await this.poolConnection.request().query(query);
// Map to object
resultSet.recordset.forEach(row => {
result.push({
name: row.UserDisplayName,
Start: moment(row.Begin).toDate(),
End: moment(row.End).toDate(),
Duration: moment(row.Begin).diff(moment(row.End), 'd'),
State:row.State
});
console.log("%s\t%s\t%s", row.UserDisplayName, row.Begin, row.End);
});
this.poolConnection.close();
} catch (err) {
console.error(err.message);
}
return result;
}This will retrieve the data from the SQL Server, source database, and table. Next, it will transform the result into a usable (typed) object.
Returning the data
Now that we have the data we need, I will return the data, but not only the JSON representation itself. I will return it as an Adaptive card (Herocard) so that I can create some activities (like approving or other things) later.
Here is the code for generating the Adaptive card
public async handleAbsenceRequest(
context: TurnContext,
query: MessagingExtensionQuery,
inputParameters: InputParameters
): Promise<MessagingExtensionResponse> {
let rates: AbsenceItem[] = await this.absenceService.GetAbsencesByDate(inputParameters.Start, inputParameters.End);
let attachments: Attachment[] = [];
rates.forEach((item: AbsenceItem) => {
// Load the result Hero card template
attachments.push(this.GetAbsenceHerocard(item));
});
// Return the result
return {
composeExtension: {
type: "result",
attachmentLayout: "list",
attachments: attachments,
},
};
return null;
}
You will see that I will generate a Herocard using a separate method.
public GetAbsenceHerocard(item: AbsenceItem): any {
let template: ACData.Template = new ACData.Template(personaCard);
let preview = CardFactory.heroCard(`${item.name} (Duration ${item.Duration} Day(s))`);
const card = template.expand({
$root: {
Name: item.name,
Start: item.Start,
End: item.End,
Duration: item.Durationm
State:item.State
},
});
// Adapt to the attachemnt
const attachment = { ...CardFactory.adaptiveCard(card), preview };
return attachment;
}These will load a Card template defined in an extra file. Then, it will apply the data from the given absence information and push it back to the caller.
The result will then be attached to the caller so that it gets a screenshot of the data to the copilot. The fancy thing is that when you hover over the data reference, it will show you the adaptive card.
Tryout
Now, it's time to test the plugin. So first, I activated the Plugin, and after that, it was ready to use. I asked the M365 Copilot the following.
Then, the Copilot extracts the information that the scope is the absences. Now it extracts the required parameter to select "in August" so it will send me over the first day and the last day of August as parameter.
With this, the selection tour can start, and you know I will send the results back afterward.
So M365 Copilot will answer like this following prompt
Nice work!
Closing words
This is a small and very simple example of how to use the M365 Copilot plugin architecture to access custom data within your organization. Please be aware that I did not do any specific work about security or other issues, so it's clear that you are responsible for implementing security (especially for HR data).
Take this example implementation to start with your own data structure. It was a very simple case, but it greatly impacted usability because you integrated other systems into the user flow.
Now, enough words, here is the GitHub source
SBajonczak
