Add an Unknown Number of Email Attachments to an Approval

 

Last time out we looked at how to add an unknown number of email attachments to a Planner task. We accomplished this by looping through all of the attachments, adding them to an array, and then adding the array to the Planner task.

What if we want to do the same thing for an Approval?

The process is very similar but with one key difference: Planner requires you to store the file and then attach a link, whereas an Approval allows you to attach an actual file.

This means that whilst the general process is the same - 1) loop through the attachments, 2) add them to an array, 3) add the array to the Approval - the specifics are slightly different.

For Planner we used a Create file step and then appended the link from that step to the array. For Approvals we're going to do an extra step to Get file content so we can attach that content to the Approval instead:

The array is in JSON format as usual, and for Approvals this is the syntax:

{

  "name": "[file name]"

  "content": "[file content]"

}

We will use the Name of the attachment for the name and the Body of the file from the Get file content step for the content in our Append step:

Every loop of the Apply to each loop will add an additional attachment to the array. 

After the loop has finished, we create the Approval and then we click the Switch to input entire array option and use the Approval Attachments variable:

Now your Approval will have multiple attachments, regardless of whether the email has 1, 3, 5, or more attachments.

Unlike the Planner solution, the files created by the Create a file step in the Apply to each loop are not needed once you've you've attached them to the Approval. This means you can add in a step to delete the folder of attachments after you've created the Approval if you want. 

(Technically you could add a delete step in the loop so that the file is deleted as soon as it has been added to the array but this makes debugging more difficult if there is a problem so I wouldn't recommend this.)

Add an Unknown Number of Email Attachments to a Planner task

A common way to triage a shared inbox is to create Planner tasks for each email that comes in, using a Power Automate flow.

The method for doing that is straightforward because there is a Create a task step for Planner. Drop in the Subject and the Received Time for the Title and Start Date Time respectively, add a couple of days to the Received Time to make a a Due Date Time, and you're done:

But there is no option to add an attachment using the Create a task step so if the email has an attachment you need to add to your Planner task, how do you do it? 

Planner doesn't allow you to upload attachments to a task directly, you need to provide a URL for it, which means first storing it to SharePoint or OneDrive.

Now, if there is a single attachment this is quite simple to do in Power Automate once you know how.  You can run an Apply to each loop using the Attachments from the trigger as the value, then use a Get Attachment and a Create file step inside the loop to save the file:

The Get Attachment step:

The Create file step:

Then, to add that URL to the Planner task, use an Update task details step which allows you to add attachments or checklists elements:

(This is not the same as an Update a task step which - somewhat confusingly - allows you to update other elements of the task but not attachments or checklists. No, I've got no idea why they're separate steps either. Just make sure you use the Update task details step for this.)

You need to provide a name for the attachment (the Reference Alias, which you can take from the name of the attachment if you want), a URL for the attachment (the References Resources link, which is a link to the file you created in Create a file) and select a file type (from the Reference Type dropdown, which you can infer from the name of the attachment). 

But what happens if there is more than one attachment? 

The Update task details step allows you to add multiple attachments but that only works if you know how many there are. 

If you don't know how many attachments might be in an email, you need to create an array of attachments and add that array to the task as one item instead.

Adding an array of attachments

The good news is that we already have most of what we need for this solution. We just need to create an array variable at the top of the flow:

And then add the attachments to that variable in the Apply to each loop:

As usual with arrays we need to append the attachment in JSON notation. That takes the form:

{
  "alias": "[attachment name]"
  "resourceLink": "[URL]"
}

We will use the Name of the attachment for the alias and the Path of the file from the Create file step for the resourceLink in our Append step:

Every loop of the Apply to each loop will add an additional attachment to the array. 

After the loop has finished, we create the Planner task as before and then in the Update task details step we click the Switch to input entire array option:

And use the Planner Attachments variable as the value:

Now your Planner task will have multiple attachments, regardless of whether the email has 1, 3, 5, or more attachments.

Note that at the time of writing the Planner limits state that the "Maximum references on a task" - i.e. attachments, for the purposes of this discussion - is 15, so if you try to add more than that you'll get an error of some sort. 

If that might be a realistic problem you'll need to implement a different strategy such as storing files in a SharePoint folder and referencing that folder, or alerting you to deal with the problem, or whatever solution is right for you.

Add a button to a SharePoint list item to start a Power Automate workflow

 

Triggering a Power Automate workflow from a SharePoint list item is a common requirement.

It's easy enough to trigger a workflow when an item is modified using a trigger condition so it only fires when a specific field changes - we'll cover that below - but adding a button makes the process more user friendly and gives you the button text to tell the user what the button does. This is preferable to just a column header that may not make it clear to the user that changing the value will trigger a flow. 

We're going to create a button which will change the value of a Yes/No column, and that will trigger a workflow. In my use case I want the workflow to generate a document so that's what the column header and button refer to, but you can modify it for your needs.

1. Create a Yes/No field called Run Flow.
2. Create a single line text field called Generate. This will become the button.
3. Click the arrow next to the Generate column header, go to Column settings > Format this column and set the JSON to:
   
   {
     "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json", 
     "elmType": "button",
     "txtContent": "Generate document",
     "customRowAction": {
       "action": "setValue",
       "actionInput": {
         "RunFlow": 1
       }
     }
   }
   
   
   
   
   This JSON sets the element type to button, gives the button a label of "Generate document", and when the button is clicked it changes the Run Flow Yes/No column to Yes.
   
   To change the:
    - button text, modify the txtContent value from "Generate document"
    - column, modify the actionInput value from "RunFlow"
   
   Note that the column I created was called "Run Flow" but the actionInput is changing "RunFlow". That's because in line with best practise I removed the space when I created the column and added it back in afterwards so the column header is more human readable. The JSON needs the original column name (RunFlow) not the name that was modified after the column was created (Run Flow).
   
   
4. Create a flow which triggers when an item is modified. Set the trigger condition to be:
   
   @equals(triggerOutputs()?['body/RunFlow'], true)
   
   
5. This workflow will now only trigger when the RunFlow field is set to 1. When you click the Generate document button, RunFlow field is set to 1 so the workflow will run. 

That's all there is to it. 

If you don't want the workflow to run every time someone changes the item from now on, make sure your workflow changes the value of RunFlow back to 0.

For workflows that might take a little time to run, such as a document generation or approval flow, I like to add a "Flow Running" Yes/No column that is switched on by the workflow and then switched off once the work is compete. Combined with some colour formatting of the row for a nice visual cue, this provides an easy way for the user to see when the flow has finished.

Pull attributes from a People column into another SharePoint column

(This post was inspired by this deep dive on Annabel Treshansky's excellent blog)

Sometimes in a SharePoint list you want to display a person's title, department, email address, or other attribute which you can find in their Entra record.

If you want to be able to filter, group or sort using these attributes then you'll need to use a Power Automate flow to pull the data from their user record and populate another column.

But if you just need a read-only field with the data you can extract the attributes and render them in a text field using JSON.

Here is an example list with a Person column (My Colleague) and a single line text field (Department). We want to automatically show the Department information for whoever is selected in the My Colleague column:

Click the arrow next to the Department column header and select Column settings > Format this column:

In the Format view click Advanced mode:

In the JSON text box paste the following JSON and click Save:

{

   "$schema": https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json,

   "elmType": "div",

   "txtContent": "[$MyColleague.department]"

}

The Department value will now be shown in the column:

The key part in the JSON is this line:

"txtContent": "[$MyColleague.department]"

Note the $ sign at the start of the "MyColleague" column name - without that the JSON won't find the right column and therefore won't find the attribute. You can pull out a lot of data using this, including email, title, department, and lots more. 

Because this information is displayed when the screen is rendered it's effectively formatting rather than data, so it can't be downloaded as part of an export, but it's very useful for display purposes and you can also show these attributes in the SharePoint form if you're formatting the header or body with JSON.

Reliably embed an image from SharePoint into a Power Automate email

If you're sending out emails using Power Automate and you want to embed an image from SharePoint, you need a clean link.

The basic HTML to embed the image is nice and simple:

<img src="image-url">

But getting a link from SharePoint can be tricky. You can't just copy the URL or click Copy Link because that comes with a whole bunch of possible tokens which can make the URL very long and unwieldy. 
More than that though, the tokens can include permissions and that can prevent your workflow from accessing the file. What you want is a simple URL that points directly to the file so that Power Automate can use it. 
It turns out there is a simple way to get that URL, it's just slightly hidden away. 
Select your image file in the library and click ... > Details to open up the Details side bar on the right hand side. Scroll all the way down to the bottom to find the Path information.
Click the Copy icon to get a clean URL directly to the file::

Use that URL in your HTML. Remember the basic HTML from earlier?
<img src="image-url">
That was taken from the Microsoft Learn page on this subject and they have forgotten something very important: Adding ALT text.
Making your emails accessible for people with sight issues should be something you do as standard. It's a tiny tweak that looks like this:
<img src="image-url" alt="ALT TEXT">
It's also worth adding in sizing if you need to make sure that an an image always renders as you want it to, and that is equally simple:
<img src="image-url" width="x" height="y" alt="ALT TEXT">
And there you go: a simple method for getting a simple URL for embedding images in emails. 

Round Up Numbers from Power Automate to SharePoint

 

This problem came up for me when I was using Power Automate to calculate an average of 3 numbers that had been entered into an MS Form.

The requirement was for the average to be shown as a whole number in a SharePoint list column, but with rounding down for decimals less than 0.5 and rounding up for decimals of 0.5 or higher as per normal mathematical conventions. 

This seems simple enough but when you set a Number column in SharePoint to show 0 decimal places it doesn't round numbers or down. Instead it just truncates the decimal values. So whether you have 3.1 or 3.7, if you show those figures to 0 decimal places SharePoint will show both of them as 3. That's fine for decimals of .4 or lower but what about decimals of 0.5 or above you need to round up?

Here's how to do it in Power Automate:

1. (I was calculating my number so I'll show that step but you can skip this if you've already got your number through some other means.) Calculate your average using an expression, for example:
   
   div(add(add(outputs('First Number'), outputs('Second Number')), outputs('Third Number')), 3)
   
   
2. Convert it to a decimal number with 1 decimal place using Format Number with a custom value of 0.0 for the Format:
   
   
   

   
   (if you have a whole number like 3, this will convert it to 3.0 and the below technique will still work perfectly)
   
   
3. This is where the magic happens: Use an expression to add 0.5 to the output of the Format step, if required:

if(greaterOrEquals(int(substring(body('Format'), add(length(body('Format')), -1), 1)), 5), add(float(body('Format')), 0.5), body('Format')) 

What is this expression doing? This part of the expression:

 int(substring(body('Format'), add(length(body('Format')), -1), 1)

is getting the last digit of your number.  Let's assume your number is 3.7, which means this part of the expression will return the number 7.

If we swap in the number 7, we get this expression: 

if(greaterOrEquals(7, 5), add(float(body('Format')), 0.5), body('Format'))

greaterOrEquals takes 2 arguments: The number to check, and the number to check against. 7 is greater than 5 so this will return true. 

If we swap in true we get this expression: 

if(true, add(float(body('Format')), 0.5), body('Format'))

If takes 3 inputs: A logical argument, the thing to do if the argument is true, and the thing to do if the argument is false. In this case if the argument is true we add 0.5 to the average and return that value, and if it false we just return the average. 

Because 7 is greater than 5 the If statement returns true, so it adds 0.5 to the average and returns 3.7 + 0.5 = 4.2.  If the decimal was less than 5 , such as 3.1, then the If statement would return false and just return the average.

Remember, SharePoint doesn't round up so when SharePoint formats your number to 0 decimal places both 3.1 and 3.7 will be shown as 3. 

So by adding 0.5 to any number where the last digit is 5 or greater we'll end up with the next whole number. In this case, that will be 4.2, so when SharePoint truncates it to 0 decimal places, you get 4. 

This meets the mathematical convention of "rounding up" 0.5 and above to the next integer even if we're not technical rounding up, we're just amending the number to fool SharePoint into looking like its rounded up.