It’s no secret at Regroove that I’m very enthusiastic about Microsoft Power Platform. I love building business solutions and optimizing processes, and Power Platform lets me build custom low-code and no-code apps and flows that are seamlessly integrated with the Microsoft 365 environment. We hope this article gets you interested in the possibilities of Power Platform solutions (and in turn, that I get more Power Platform projects to work on, ha!).
I was recently tasked with a project in Power Platform. Sean, Chief Troublemaker at Regroove, wanted to press a button and create and email a PDF invoice with updated information based on his band’s most recent Rockstar Nights performance.
This was an extremely fun project to work on, and I certainly got my fix of automation problem solving in the process! Here’s how I built the automation from start to finish.
Step one is to set up the SharePoint library where the document is going to live. Take the list of things you want to modify in your document and create a column for each one. This will hold the metadata for your document. In this case, I made a column for the date of the event, the date the invoice is sent, and the invoice number. I also have columns set up with the invoice description and the vendor info – unnecessary for right now, but useful for possible future scenarios (e.g., a different vendor).
The SharePoint metadata is directly linked to the document properties. This means you can enter anything into your SharePoint metadata, and whatever you enter will become a document property. Inside a document, you can create quick parts or fields that autofill with the associated document properties. This means you can have your flow edit the metadata for a SharePoint library item, and the document’s contents will reflect whatever modifications you make.
The next step is to make the invoice document. This was a quick task: From inside the SharePoint library, I created a new Word document, opened it in the desktop app, picked out an invoice template, changed the layout and text, modified the colour scheme to match Rockstar Nights’ palette, and added in Rockstar Nights’ logo and information.
Make sure none of the required metadata fields are blank BEFORE you start editing your document, otherwise SharePoint will get cantankerous about saving the changes you made and letting you edit your own file.
Now to make use of the document properties! (Side note: in the desktop app, you can click on ‘File’ and then ‘Info’, and at the right side of the window there will be a list of document properties. You might need to click ‘More properties’ to see all of them. In SharePoint, they’ll just be the metadata, or you can look at the file details.)
Go into the file in the desktop app (unfortunately you can’t do this part in the web version), go to the Insert tab, then click Quick Parts, mouse over Document Properties, and select the property you want from the menu. It will be added as a field to your document, and it will be filled with whatever information is in the associated metadata field!
I know there’s a convenient Power Automate action to Populate a Word Template instead of updating the document properties, but that’s a premium action and costs more. Plus, you’d still have to set up the document template with all the fields, so it’s not saving you much effort.
Caveat: If your document needs to have tables autofilled and updated, you might be stuck with the premium action. I cannot figure out how to make this quick part method function with tables. If you go into the Developer tab you can mess with content controls and I know there’s a repeating section option, but I can’t make it work with document properties. Content controls/quick parts also don’t seem to be able to convert JSON, CSV, or HTML inputs into a table. It feels like there has to be a way, but I haven’t found it yet.
Now that the document is set up, the next step is to make the flow and set it up to modify the document properties based on the inputs. In this case, we only really need to modify the date, since the vendor and description are going to be the same every week. If the document properties are already there and filled with the relevant info, and are in the document itself as quick parts, it would be quick to add in an input field or dropdown selection to the flow.
The goal of this exercise was to make it a single button press, so I added actions to:
- Grab the current time (which comes out as UTC);
- Convert the output to PST;
- Calculate the date of the Thursday of the current week (because this particular invoice is for an event that always happens on a Thursday).
To calculate the Thursday, I used an “Add to time” step. This asks for a base time, a number of intervals to add to it, and what the intervals should be (e.g., days or weeks).
For the base time, I just wrote an expression:
Let body(‘Current_time_(PST)’) refer to the output of the step where I converted the current time to PST – this variable will just be whatever that step is named (although any spaces will be replaced with underscores), which out of the box will be Convert_time_zone.
This expression retrives a past time through getPastTime(). getPastTime() wants two pieces of information:
- Number of intervals to go back – in my expression, this would be dayOfWeek(body(‘Current_time_(PST)’)). dayOfWeek is taking the output of body(‘Current_time_(PST)’) and converting it into a number. Power Automate thinks of days of the week in terms of 0 to 6 – Sunday is 0, Monday is 1 and so on. If you run the automation on Friday, this number would be 5, so that would be the number of intervals to go back.
- What the intervals are – this part is ‘Day’.
So, if you run it on a Friday, the expression is basically saying getPastTime(5, ‘Day’) – this is effectively saying ‘(Current Day Of Week) minus (Current Day Of Week)’, which gives an output of 0, which is Sunday (of the current week), which is the base time.
Now that we have a base time of Sunday of this week, we just specify that we want 4 intervals added to it, and that we want those intervals to be days. This gets us an output of Thursday of this week.
The only thing is, if you wait until Sunday to send it, you’ll get the upcoming Thursday instead of the previous Thursday. My solution was to add a date input to the trigger (but make the field optional), initialize a variable for the date that defaults to the calculated Thursday, and have a condition step that sets the variable to the inputted date if the date was inputted.
The only problem with this is that Power Automate doesn’t consider the input field to exist in the outputs if it’s left blank. It doesn’t even return a null value; it just doesn’t include the field in the outputs. This means that if you try to reference the field when the input is left blank, the flow will break.
To get around this, in the condition step, instead of using the field as a dynamic content value, I wrote an expression:
Replace ‘property’ with whatever you were trying to reference (to find what it should be, you can use the field as a dynamic content value somewhere, then mousing over it will give you triggerBody()?[‘something’] and the ‘something’ is what you’ll use as your ‘property’) – in this case it was ‘date’, so the expression was if(contains(triggerBody(),’date’),triggerBody()?[‘date’],null).
This will return the field you were trying to reference if it exists, and null if it doesn’t – like it should have done in the first place instead of saying the property doesn’t exist.
Now that the field can be referenced without breaking the flow, I used the above expression as the value in a Condition step and checked if it was NOT equal to null. If it was NOT equal to null, meaning an input was entered, it would go through the ‘If yes’ branch, where I added a step to set the variable to the inputted date, overwriting the default calculated date.
With all this done, I added steps to:
- Create fields to reference EventDate in different formats (unnecessary, I just wanted it to say Thursday, November 18, 2021 in some places and 2021-11-18 in others).
- SharePoint – Get file metadata – Site Address will be the SharePoint site where the invoice document lives, and the File Identifier will be the document itself.
- SharePoint – Update file properties – I updated the Send Date (referencing the Current Time (PST)), Event Date, and Invoice Number (both referencing the Event Date variable – technically, referencing the Convert steps where I changed the date format). This is the step that modifies the content of the document too!
- SharePoint – Get file content – get the content of the invoice document, now it’s been updated).
- Compose – File Title – this isn’t necessary, I just wanted the title to include the invoice number.
- OneDrive for Business – Create file – I set the file name to be [Compose outputs].docx, and the File Content of course is the outputs of the Get file content action.
- OneDrive for Business – Convert file – select the file ID as dynamic content from the previous Create file action, and choose PDF as the target type.
- Outlook – Send an email – enter the email addresses to send it to, the message to send with it. etc., then under Attachment Name, put [File title].pdf, and for Attachments, choose File Content from the Convert file action.
- OneDrive for Business – Delete file – delete the file you created. Not strictly necessary, just declutters and avoids duplicate file names scenarios. I set this to run after the previous action regardless if it succeeds or fails – this means even if there’s a problem with a previous step, you won’t have the PDF lying around in OneDrive.
And that’s it! Super easy to set up once you know what you’re doing, and the automation possibilities are endless. Get in touch if you or your organization want Regroove’s help setting up cool flows like this one!