Create a Word brochure generator with Power Apps and Power Automate: Part Two

YOU'LL RECALL THAT LAST TIME  we covered adding metadata columns to the Document Library and the building of cascading menus in Power Apps to manage that metadata ...

So ... now we're going to create the Word Template, insert the Quick Parts that will hold the data or text from the Library metadata columns, then attach it to the Content Type.

I'm not going to spend too much time telling you how to style the Word document here. There's plenty of places where you can look that up online. But what we will cover here is how we add in the Quick Parts that will hold the text that you entered into the Library's metadata columns, and how we style those.

The trick to creating a Word document template with working Quick Parts is to upload it to the Library first. Though this will form the basis of our online template, DO NOT create the Word document as a template, just a simple Word document. You can style up the Word document ahead of loading it up to the system. I did a bit of work on the Word document so it looks a little better than a blank white page.

Opening the document in desktop Word. If you can't see clearly,
click on any image to enlarge it.

Once you have the Word document loaded into the Library, open the Word document in the desktop version of Word. This is important, as Quick Parts don't work in the browser version of Word.

Once you have the Word doc open, click on the Insert tab and find the Quick Parts drop-down on the right side of the ribbon. Click on Quick Parts to display the drop-down, hover over Document Property to see the fly-out menu of all the Site Columns you can select to insert as a Quick Part.

For the first Quick Part we'll be choosing the Client name. Select that and the Client name is inserted into the document as a Quick Part. Now we'll need to add some styling. Highlight the Quick Part in the document by clicking on it.

Now, with the Quick Part selected, click on the Home tab and then on the Title option in the Styles section of the ribbon (this might not be the style you want, but this is just for demo purposes).

Now the type looks bigger, but a little wan ... let's add some focus to it by selecting a colour. You might want navy blue... so select a strong colour from the Type Colour drop-down.

Feel free to add a bold if you want to.

On the next line, let's add the type of brochure we're creating, in the same way as we did with the Client name. Finally, you're going to need a date ... add the Date QuickPart in the same way. Your document should look like this:

One more thing, we're going to add the Client name to the legal disclaimer on the back cover. Scroll down the Word document until you can see the Disclaimer text at the end of the document and click to place the cursor at the right place in the text (I put in a bit of holding text, so I'd know where the Quick Part goes).

If you've done it right, you won't even need to apply any styling, as the Quick Part should pick up the styling of the surrounding text.

Cool, eh?

You can keep on going, adding Quick Parts to headers and footers if you want, it's up to you. For example, if you're filling the rest of the document with standard text, you can mention the client every so often by dropping in the Client Quick Part, to give the whole brochure more of a personalised feel.

When you're done, Save your document (just in case the AutoSave hasn't worked or hasn't run in the last couple of minutes).

If you haven't done it already, give your document the same name as the Content Type you'll be attaching it to. If you click on the Document's link in the Library window, the document will open in the browser version of Word and you can rename it, like this:

That's it. Your template is ready. 

The next task is to add the Content Type to your Document Library. I was pretty sure I had done this earlier, but after having some trouble attaching the template to the Content type in the following steps, I investigated and found my Content Type hadn't been added to my Library. 

... so double-check, and if it's missing in your set-up, go to Library Settings, find the Content Type section and click on Add from existing Content Types.

Find the Content Type in the Available Site Content Types window, select and click the Add button. Then click on OK.

Now you can attach the Word Template to the Content Type. So, first download it from the Library to your desktop.

Now, navigate to the Site Content Type via Library Settings. Find the link for Advanced Settings and click it.

Use the Browse button to find the Word Document you saved to your desktop. Then click OK to add the Word document to your Content Type.

Now if you navigate back to the "AllItems" view of your Document Library, you should be able to select your template from the New drop-down menu.

When you open the Brochure template, you want it to open in the browser version of Word. Don't worry that it doesn't look quite right. Browser Word is funny like that. You're only opening in browser Word so you can easily change the name of the brochure. Like this:

Once you've changed the name from "Document" you can close the tab.  You can see your new Brochure sitting in the "All Items" view of the library.

The next job would be to add the metadata to that new brochure, thereby updating the QuickParts. To do that, click on the new document's Properties ... this will bring up the wonderful Power Apps input form you created earlier.

I still haven't hidden the text input fields in the Power Apps form, but that's so you can see how it all works. When I start changing the values of the drop-downs, you can see the cascade in action.

Set the values as you need to, then Save to lock the changes in

Now, if we open in the desktop version of Word, we'll see that the data from the Properties form has filled in the QuickParts in the document. Not unlike magic.

So, that's all working fine, then.

Right ... on to the next bit. Setting up the Flow to get manager sign-off and to convert the final Word document into a PDF form mailing out to the client.

SETTING UP THE FLOW

Navigate back to the "All Items" view of your library. You should see the Automate menu option sitting above the list of documents. Click on that and select:

Power Automate > See your Flows

I prefer See your Flows, as it's less restricting than the Create a flow option.

From the New menu, select Automated from blank, as this is the type of Flow we're going to use.

In the popup window, give your new Flow a name and select the trigger for the Flow ... we're using When an item is created or modified.

Well done for getting this far. Now the real work can start.

If you have had some experience of creating SharePoint Designer WorkFlows, then much of this will be familiar to you. The principles are the same. WorkFlows and Flows are all about the conditions and the actions. It's just some of the terminology that's changed in Flows. But, of course, not everything is the same ...

Your stage for your new Flow should look like this.

Your first task will be to choose the Site where your Library sits ... 

If you can't see your site listed, scroll to the foot of the drop-down, where you'll find Enter custom value.

... then in the second drop-down, the name of your Library.

Now we need to set up some conditions, so we're not firing off random alerts every time something changes in the Library. So ...

First row - Approved = false

Second row - Approver_txt is not = Null

Third row - Checked Out = false

When you're done, your Conditions panel should look like this.

So what we're saying is: If the document hasn't been approved, and the Approver email address isn't empty, and the document has been checked in, the Flow can run. If any one of those conditions isn't met, the Flow goes down the empty If no branch and essentially does nothing. So that's what we want to happen.

Next, we're going to turn our attention to the If yes branch, and start loading up actions.

So ... we need to Get file properties so we can identify (by its ID) the file the Flow is working on. So Add an action and find Get file properties. 

Then as before, select the Site and the Library, then add the item ID, like this:

Then I added another condition, to check the status of the Approval (that we've not yet asked for), like this.

Approved = false

I would recommend that you start giving your Conditions and Actions descriptive names at this point. Otherwise, things will start to get confusing if all you've got to go on is "Condition 2", "Condition 3" and so on.

So now we have it set up to only continue if the Approval status of the document is "No".

Next, we have to ask the Approver to approve the document. Flow makes that easy for us, as there's a built-in function to ask for approval. Much better than the old SharePoint Designer way of doing things.

So Add an action, Start and wait for an approval.

I used Approve/Reject - Everyone must approve

You'll need to fill in the details of the approval request. Here's how I set mine up:

Title is the subject line of the Approval request email

Assigned to will ask the Flow to grab the value from the Approver_txt

Details is the message in the body of the email. I personalised it by inserting the Name of the document.

Item link is useful as it gives the Approver a quick and easy way to open the document for inspection before approving. Use this format: SiteURL + Full Path

Item link description is the text in the Approval email that the Approver will click on to open the document.

Now we need another Condition ... this time to see if the document has been approved before moving on to the next (lengthy) sequence.

So, add a Condition and set the single row to:

Outcome = Approve

Right, now we're ready for the meat of this Flow. In this series of Actions, if the Document is Approved by the Approver, we'll:

1. Check out the file so we can have the Flow make changes to the metadata (so make sure documents need to be checked out to make changes in the Library Settings)
2. Update the File Properties, setting the Approved value to true
3. Check in the file
4. Get the file content, ready for creating a PDF
5. Create a New Word Doc and add the file content
6. Convert the file to a PDF
7. Give the new PDF the same File Properties (metadata) as the original Word Doc
8. Check in the PDF (we don't want to send that to the Approver for Approval, as they've already Approved that content)
9. Delete the copy of the Word Doc we stored in OneDrive
10. Send an Email with the PDF attached (for the convenience of the original author, the PDF has also been stored in the Document Library.

STEP-BY-STEP

First, check out the Word Document so we can make changes to the metadata field, specifically the Approved field, which we're going to change to Yes.

Add an action, type "Check Out" to narrow the options, and select Check Out File. Once you've done that select the Site URL and the Document library (as you did for earlier Get File Properties actions. In the ID field, add ID from the Dynamic Content list - we need to know what document we're working on.

Add an action, Update file properties. Select the Site, the Document Library and the ID again. Leave everything else blank, except for Approved, which you should set to Yes. You could blank out DocType Value and Successful by selecting Custom value and leaving it blank. Or you could maintain the DocType Value by inserting DocType Value from the Dynamic Content listing (I did the latter, last time round).

Now we've done that, we should Check In the file. So Add an action of Check In File, then fill in the form presents with Site, Library, ID, a comment and the version type, Major Version.

That's the sequence for updating the Document's status to Approved. Now we're going to create the PDF. This is a bit clunky, and I'm sure we really should be using a generic access to OneDrive, but I just used my own, since we're deleting the copy of the Document right after we've PDFed it.

So first, we need to hoover up all the content from the document. Add an action and select Get file content from the Dynamic Content list - make sure you choose the SharePoint one, not the OneDrive one!

Fill in the Site URL (again!), and for File Identifier, choose Identifier from the Dynamic Content listing.

Now we have the file content, we need somewhere to put it, so we'll create a new document on OneDrive. Add an action of Create file (this time choose the OneDrive version and not the SharePoint one).

I created a new folder on my OneDrive to hold these temporary documents. And because I'd already created it, it was available as a choice option on the fly-out menu.

Make the name of the file the same as the source Document (don't forget the ".docx" suffix of the new doc won't be recognised) and add the File Content from the earlier Get file content action.

Now we just have to convert the file to a PDF. Add an action and choose Convert File (Preview). In the File field add ID (from the OneDrive options). Leave the Target type as PDF.

So, we have a PDF of the original Word Document stored on our OneDrive. We need to bring it back to the SharePoint Document Library, so we do the reverse of how we created the PDF.

Add an action to Create a file, choosing the SharePoint version. Select the site URL (yet again!), choose the Library in the Folder Path, Name the file using Name from the Dynamic Content listing and get the File Content from the previous Convert File (Preview) step. It should look like this:

Now we need to Update the properties of the new PDF in the SharePoint Library with the same metadata as the source document. So Add an action and select Update file properties. Fill it in like this:

These should all be from the Update file properties - to Approved action.

Now check your new PDF in (Note: Make sure the Library requires documents to be checked out before editing, otherwise, this Action will cause an error). Add an action, select Check in file. Add Site URL and Library. Get the ID from the previous action - Update file properties - PDF.

And as we don't want our OneDrive getting cluttered with old Word documents, let's delete the copy file. Add an action and select Delete file from the OneDrive options. Choose the ID from the OneDrive Create file action.

All that remains now is to send an email notification the the original document author to let them know their brochure has been approved and to let them have a PDF of the final document.

Use Send an email (V2). Set it up like this:

And that's it.

You may want to create a couple of new views in your document library, perhaps separating completed and approved Word Docs and finished PDFs into different views, just to keep things tidy. Otherwise, you have a working system,

All that remains is to do some testing.

When I had some time, I was going to look into tracking which brochures led to successful pitches and how I could track the resulting revenues. But that's for another post and another time.

I hope that's helped someone.

Create a Word brochure generator with Power Apps and Power Automate: Part One

I was asked by my Marketing colleagues whether I could build an application in SharePoint that would generate and store marketing brochures from Word templates and organise them by region and line of business.

In the old world of SharePoint Designer I would be able to do that quite easily, but as we're all aware, Microsoft started deprecating core SPD functions, like custom input forms, around the beginning of June 2020, leaving us SPD workers a bit stranded.

Though I haven't seen any posting about it anywhere, my work was further limited by being unable to connect Data View Web Parts to Data Sources in SPD. This may be an internal problem at my company, but the effects are just as real.

So, with no DVWPs, no custom forms in SPD and 2010 workflows being turned off in November 2020 I didn't have a lot to work with. My company was able to arrange a course for me on Power Platform and Power Automate, though these turned out to be quite entry-level and lacking the depth I'd need for this brochure project. But with the help of some more knowledgeable colleagues, I was able to find a way through and create quite an effective application.

How it works

Here's how the marketing team wanted the Brochure Generator to work.

1. The user creates a new document from a template
2. A form pops up for the User to add the customer's name, the date of the brochure as well as region, Line of Business and, behind the scenes, the name of the Supervising Manager. 
3. The user then adds the sales pitch into the Word document and saves and checks in. This initiates an automated approval process
4. The Supervising Manager receives an alert that a new brochure has been created. They review and approve (or not approve)
5. If approved, the system converts the Word doc to a PDF
6. The user receives notification of the manager's approval, with the PDF as an attachment, ready to send out to the customer
7. If not approved, the user makes amendments and saves and checks in and the Approval process kicks off again (repeat until approval obtained).

What you need

• A SharePoint Modern Experience Teamsite
• A Document Library (set to "Require documents to be checked out before they can be edited", in versioning settings)
• A Custom List with four columns - Region, Dept, Line of Business, Approving Manager
• One or more Word templates, configured with Quick Parts to hold the dynamic data
• Access to Power Apps and Power Automate
• Access to OneDrive

How I did it

I started with a Modern Experience Teamsite. You may be able to use an old-school Classic Teamsite, but I was told on my course that some Power Platform functions don't work properly on Teamsites that were created in Classic mode.

You don't have to have a nice-looking Landing Page, but it does give the whole thing more of a professional look, don't you think?

I believe all Teamsites come with some basic components, like a Document Library. In our site farm, they do, anyway. 

Navigate to Site Content Types, click on create and follow the onscreen prompts. If you need detailed instructions for this, a quick Goggle search will help.

My first task was to create custom Content Types to hold the metadata for the Documents in the Document Library. I think it's possible to use List/Library Columns in Content types, but why do that? Using Site Columns means the Content Types can be used with other Libraries in the site, something that saved me a bunch of work later, as you'll see. In addition, since we're planning to import list column values into the Word Document, I believe that the Quick Parts we'll be using for that only recognise Content Types and not List Columns.

You may want to use more than one template, so make sure you give your Content Types simple and descriptive names, so your users know what they're getting. 

So I got to work to create some Site Columns that would make up my first Content Type.

As an inexperienced Power Platform student, my first thought was to create Site Columns as Lookups. That's what we'd do if we were using SharePoint Designer, right? Then add a bit of JQuery to get the cascades to work? You don't do that with Power Apps.

I wasted a whole lot of time searching via Google to find a way to do cascading menus in Power Apps. I found several "solutions". None of them worked. That could have been my fault, but I don't think so. I'm fairly good at following instructions. The working solution came from my friend and colleague Ernani, who showed me how to do great drop-down cascades in Power Apps ... but I'll get to that later.

So, like I say, normally, I'd do Look-Up columns to achieve a cascading menu set. But in this case, we're going to Single Line of Text columns to hold the cascade info. Bear with me, it'll become plain.

This is what my finished Content Type looked like when I added all the necessary Site Columns.

Once you've added all the new Site Columns to the Content Type, navigate to the Library Settings and add the Content Type to your Library.

This is what it looks like with your Content Type in place. Note that I've used two different Content Types to handle two different templates - you can only attach one Word Template to a Content Type.

Now we can create the Custom List (I called mine "metadata") that will power the cascading drop-downs. The columns I used were:

• Geography (single line of text)
• Dept (single line of text)
• Line of Business (using the Title Column, single line of text)
• Approver (name field)

This is how I set my metadata list up. Make sure you use the same column types.

The final list looks like this, though obviously, I'm not showing all the rows here.

Here's a snapshot of the populated metadata list. I used the Title column for the unique "Line of Business" values, but it doesn't matter if you want to create a new column.

We'll use Power Apps to make the Custom List and the Document Library talk to each other.

This is what the Properties input form (EditForm.aspx) looks like at this stage of the build. Quite a few of the fields/Content Types are missing.

Even though the Content Type has been added to the Library and the Site Columns should be available, when you try to amend the Properties of an uploaded document, you don't get all the fields in the default Properties window, so the next thing I did was to start up PowerApps to create a custom input form. 

It's pretty easy to fire up Power Apps from your main Library. You may prefer to work from a Canvas App, but that's up to you.

I chose Customise Forms so that I had a basis to work on. Even so, what's available is a bit sparse. All I had on the EditForm stage was "Title". 

A bit of a blank canvas, really ... 

Before we can go any further, we'll need to add the custom list to the Power Apps form so that any custom field we add can pick up the data from the list. This is essential to get our cascades working. So, click on the Data icon in the very far left of your screen

Now click on Connectors and select SharePoint. Sign in as yourself, if prompted.

Over on the right hand side, you'll now see a list of all the SharePoint sites available to you. Click on the appropriate site to select it. 

The view now switches to display all the Libraries and Lists in the selected site. Choose the List to connect to, in my case, I chose the list "metadata" that holds my cascade options.

So, that done, we can now begin adding some more fields to get the cascades working. This is how it's done.

If you can't see the Fields panel, then click on the text link Edit Fields in the right hand panel. (I spent ages looking for this!)

This was a tricky little sucker to find, if you're new to Power Apps. If you get lost, just highlight the SharePointForm1 heading in the left hand column first, then on the Properties link in the right-hand column.

Once the Fields panel displays, you can click on the Add Fields link to grab more columns from your Doc Library and place them on the EditForm stage.

Once the field is on the stage, you can manipulate it as required. For example, you can access the ellipsis in the Field's panel and select the Move Up action to change the field's position on the stage. I'll be removing the Title field at some point in the process, but for now, I'll just move the "Geography_txt" field up to sit above the Title field.

I'm not going to make you sit through the entire process, but I'll do enough so that you can see how the cascade works. So the first thing to do is to make the text field for Geography_txt a little smaller, as we're going to squeeze another custom "Drop down" input in this "card". To do that, highlight the card by clicking in it, then select Insert tab at the top left of the page. Click on Input and select Drop down from the drop-down menu.

The new Drop down input should appear within your card. If it doesn't, then you didn't have your card selected correctly. Delete the rogue Drop down and try again. A successful insert should look like this.

You can resize the new Drop down field so it doesn't overlap the existing text box. Before you can make any changes to the new field you first have to unlock the card in the Advanced panel on the right.

Now you can start making changes. First, rename the label of the card from "Geography_txt" to "Region". Power Apps is picking up the label "Geography_txt" from the list. To change the text, we simply over-write the "Parent.DisplayName" call in the fx field with "Region" (include the quotes).

Next - and this is where it starts to get a bit more complex, we're going to change the underlying programming of the card so that the custom Drop down picks up data from the custom list "Metadata" and sends it to the "Geography_txt" text field.

Highlight the custom drop-down field by clicking on it. Click on the Advanced tab. Add this code into the Items window:

Distinct(metadata,Geography)

Now add this code to the Default window:

ThisItem.Geography_txt

Finally, to complete the sequence, you need to click the entire card to highlight it and then add this code:

Dropdown2.SelectedText.Result

... to the Update field in the Advanced section (you may have to click the More options button to see the Update field):

I'll leave it entirely up to you whether you rename the field "Dropdown2" to something a bit more friendly and descriptive. I didn't do that when I built my original application, but in hindsight, I probably should have. For this example, I'm renaming the field to "ddGeog". Even if I do it at this stage, Power Apps automatically updates the above formula to "ddGeog.SelectedText.Result".

Next, we have to add the dependent drop-down, "Department". So, same method as before. Click on the Add Fields link, and select "Dept_txt".

Highlight the card by clicking in it, then select Insert tab at the top left of the page. Click on Input and select Drop down from the drop-down menu. Highlight the custom drop-down field by clicking on it. Click on the Advanced tab. Add this code into the Items window:

Distinct(Filter(metadata,Geography =ddGeog.Selected.Result),Dept)

And add this code to the Update window:

ddDept.SelectedText.Value

I found as I went through adding these formulae that the Default field usually seems to default to the appropriate formula. In this case it would be "ThisItem.Dept_txt". But if it doesn't, you'll have to add it manually

At this point, it'd be a good idea to test the form and make sure your cascade is working as expected.

Right, final cascading drop-down for this form. But before we do, let's just hide that Title field, as it's getting in the way:

Now add the final cascading drop down in the same way. I've called my "LOB" (Line of Business), and changed the name of the field to ddLOB. Use these formulae to bind the drop-down to the main library:

Items: Distinct(Filter(metadata,Dept =ddDept.Selected.Result),Title)

Default: ThisItem.LOB_txt (usually filled in by default)

Update: ddLOB.Selected.Result

So that's the cascade sorted out. What I did was add another field - which I'll make invisible - to automatically select the Manager responsible for approving each request according to the Line of Business they are responsible for. This is neat, because it prevents users making mistakes and sending Approval requests to the wrong manager.

So we add another field, for "Approver" and add the following formulae:

Items: Distinct(Filter(metadata,Title =ddLOB.Selected.Result),Approver.Email)

Default: ThisItem.Approver_txt

Update: ddApprover.Selected.Result

It's a little trickier if you have a choice of two or more approvers, but I didn't have that requirement and won't be covering it here.

Test and make sure it works. You could hide this field now, but I would leave it visible so you know it's working. Plenty of time to hide it at the end of the build.

It'd probably be a good idea to Save at this point.

The rest of the fields are pretty straight forward to add, and will work fine out-of-the-box. These are:

• DocType (renamed as "Brochure" in the form)
• Client
• Due Date
• Revenue

One useful tip I have is to add a currency sign to the Revenue field. Simply change the Hint Text.

OK ... so we're all done on the Input Form. Time to Publish the form and test it in the Library.

Ready for the next bit? Well, you'll have to wait a few days for that. Pop back soon and find out how to:

• create the Word Template
• attach it to the Content Type
• then insert the Quick Parts that will hold the data or text from the Library columns
• ... and lots more.

Next: Word shenanigans and building the Approval Flow in Power Automate