Creating Word or PDF Documents in Power Platform with Power Automate Word Online connector
Today's post shows a way to create a document with data from arbitrary tables in Dataverse. The data is queried and transformed in a web resource and the document - based on a word template - is created with Power Automate's Connector for Word Online to render the final document as PDF. This allows the creation of sophisticated documents without the limitations (single table+referencing tables, poor formatting) of OOB document templates.
Generating a document in Power Platform with OOB features usually involves Document Templates or SSRS reports based on FetchXML queries. While the first one has several limitations regarding data retrieval - the latter is frankly just cumbersome to work with although it has received some updates to the report authoring extensions lately.
With Power Automate's connector for Word Online we can easily author the template in Word and fill placeholders including repeating content controls like tables or bulleted lists to make sophisticated documents without additional costs for a specific document generation engine.
The main concern one has with the OOB Word Templates in Power Apps is the fact that it is always scoped to a single entity along with its related entities. This makes it not feasible if the document has to span arbitrary tables.
Therefore we are going to fetch all data upfront in the client and pass a tailored dataset for easy consumption to Power Automate. This gives us the flexibility to do the data shaping in javascript and leave it to the Word connector to create the final document.
In our sample, we will print out teams with their roles and members as well as users with there roles and memberships.
Set Up the Word Document
As the process of template creation is thoroughly described by FlowJoe, I would advise going there for further information on how to get going with the document placeholders. The template I have set up can be downloaded here. The template uses two repeating section content controls for the users and teams tables.
The flow
When we create the flow we use a HTTP trigger that receives all data already in its desired shape. To set up the HTTP trigger use the sample JSON below
{
"users": [
{
"fullname": "Test User1",
"caltype": "Enterprise",
"title": "Software Architect",
"teams": "• Team1",
"roles": "• System Administrator\n• Basic User\n• Sales, Enterprise app access"
}
],
"teams": [
{
"name": "Team1",
"description": "Default team for the parent business unit. The name and membership for default team are inherited from their parent business unit.",
"admin": "Test User1",
"isdefault": "Yes",
"teamtype": "Owner",
"members": "• Test User2\n• Test User1",
"roles": ""
}
]
}
- The flow uses an HTTP trigger (1) so that we can call it from a web resource.
- Use the sample JSON above to generate the schema for the requests body (2).
- Place the template in OneDrive and wire the documents placeholders with the array from the request body (3)/(4).
- Create a temporary file in OneDrive (5) and convert it to PDF (6).
- Finally, return the file PDF file (7).
Get the data
As explained before we will retrieve the data and pass it in good shape to the flow that is triggered via HTTP. This approach makes it easy to get the data via WebAPI and make the necessary transformations on the data. For example it would be easy to format dates, numbers and currencies to a desired appearance.
If you create a command bar button with the handler PowerAutomateDocCreation.CreateDocument, the users und teams are queried via WebAPI and the document is fetched for the Power Automate HTTP endpoint. Finally the file is download and offered to be downloaded.
var PowerAutomateDocCreation;
(function (PowerAutomateDocCreation) {
PowerAutomateDocCreation.CreateDocument = async function () {
const userDto = await getUsers();
const teamDto = await getTeams();
const blob = await fetchDocument(userDto, teamDto);
offerDownload(blob);
};
async function getUsers() {
const users = await Xrm.WebApi.online.retrieveMultipleRecords(
"systemuser",
"?$select=fullname,domainname,caltype,userlicensetype,isemailaddressapprovedbyo365admin,islicensed,title,employeeid,_businessunitid_value&$filter=accessmode eq 0&$expand=teammembership_association($select=name,teamid,systemmanaged,isdefault,teamtype),systemuserroles_association($select=name,ismanaged,roleid)"
);
const userDto = users.entities.map((x) => {
return {
fullname: x.fullname,
caltype: x["caltype@OData.Community.Display.V1.FormattedValue"],
title: x.title ?? "",
isdefault: x["isdefault@OData.Community.Display.V1.FormattedValue"],
teamtype: x["teamtype@OData.Community.Display.V1.FormattedValue"],
teams: x.teammembership_association
.map((y) => "• " + y.name)
.join("\n"),
roles: x.systemuserroles_association
.map((y) => "• " + y.name)
.join("\n"),
};
});
return userDto;
}
async function getTeams() {
const teams = await Xrm.WebApi.online.retrieveMultipleRecords(
"team",
"?$select=name,teamid,description,_businessunitid_value,_administratorid_value,systemmanaged,isdefault,teamtype,teamid&$expand=teammembership_association($select=fullname,domainname;$filter=accessmode eq 0),teamroles_association($select=name,ismanaged,roleid)"
);
const teamDto = teams.entities.map((x) => {
return {
name: x.name,
description: x.description ?? "",
admin:
x["_administratorid_value@OData.Community.Display.V1.FormattedValue"],
isdefault: x["isdefault@OData.Community.Display.V1.FormattedValue"],
teamtype: x["teamtype@OData.Community.Display.V1.FormattedValue"],
members: x.teammembership_association
.map((y) => "• " + y.fullname)
.join("\n"),
roles: x.teamroles_association.map((y) => "• " + y.name).join("\n"),
};
});
return teamDto;
}
async function fetchDocument(userDto, teamDto) {
const payload = {
users: userDto,
teams: teamDto,
};
response = await fetch(
"https://<URL TO YOUR HTTP TRIGGER>",
{
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify(payload),
}
);
const blob = await response.blob();
return blob;
}
function offerDownload(blob) {
const url = window.URL.createObjectURL(blob);
const a = document.createElement("a");
a.href = url;
a.download = "UserAndTeamsReport.pdf";
document.body.appendChild(a); // we need to append the element to the dom -> otherwise it will not work in firefox
a.click();
a.remove();
}
})(PowerAutomateDocCreation || (PowerAutomateDocCreation = {}));
