Send a weekly email of upcoming events with “Add to calendar” .ics download links using Power Automate (Flow)

Let’s create a Flow in Power Automate that accomplishes the following:

  • Sends an email every Monday morning
  • Includes a table of upcoming training opportunities from a SharePoint calendar
  • Table has a column with “Add to calendar” download links

1. Create a new flow of type Scheduled – from blank

2. Name the flow and set the schedule to whatever frequency and times are appropriate for your needs.

Click to enlarge

3. Choose your site and list name. In my case, my calendar isn’t listed so I’ll type its name, Events, after select Enter custom value.

Click to enlarge

4. To filter to just the events coming up in the next week, we’re going to expand “Show advanced options” and enter filter criteria.

5. If you, like me, are going to filter based on “Start Time” you’ll need to know its internal name which is EventDate. For our filter, we can only use internal field names. To get started, copy and paste the following filter query into your Filter query box:

EventDate gt '' and EventDate lt ''

6. Now click between each set of apostrophe ( ‘) marks and change the dynamic content panel to Expression as seen below.

  • First expression: utcNow()
  • Second expression: addDays(utcNow(),8)
Click to enlarge

7. Now add a Create HTML table step. Use the dynamic content panel to set “From” to value from the Get items step (1). Then Show advanced options (2) and set Columns to Custom (3).

Click to enlarge

8. Under Header, type each column header you want in your table in the order you want it to appear horizontally. For Event I’ve mapped its Value to the “Title” field on my SharePoint calendar by selecting Title from dynamic content. You may also wish to add columns like Location and Category. Get your headers in the order you like, but leave Date and Save/Add to Calendar blank for now.

Click to enlarge

9. Once you’ve gotten all but Date and Save mapped to a SharePoint calendar field, it’s time to use Expression (on the dynamic content panel) to set up the remaining two. You can copy and paste Date’s formula as long as you’re using Start Time (EventDate) for the date. Save, however, you’ll need to update with your own site collection’s URL (where the calendar lives) and replace the GUID with your own calendar’s GUID. Follow steps 1-2 in this post to get your calendar’s GUID.

  • Date: formatDateTime(item()?[‘EventDate’],’ddd MMM d’)
Click to enlarge
  • Save: concat(‘<a href=”https://YourSiteURL/_vti_bin/owssvr.dll?CS=109&Cmd=Display&List=%7B1EA8795A%2D3B0D%2D43D7%2DA48E%2DB3CCD4BFE950%7D&CacheControl=1&ID=’,item()?[‘ID’],’&Using=event.ics”>Add to my calendar</a>’)
Click to enlarge

10. Now add a Compose step. This will make sure our links are clickable in the email (helps decode the HTML so that it parses and renders correctly). We’ll populate this, again, by using an Expression.

(replace(replace(replace(replace(body('Create_HTML_table'),'&lt;','<'),'&gt;','>'),'&amp;','&'),'<table>','<table border="3" bgcolor="ffffff">'),'"','"')
Cilck to enlarge

11. Lastly, add a Send an email step. I preferred using the html markup option and added an image to my header. You can use either the default rich text or html editor. To insert your table, make sure you insert Outputs (from dynamic content > Compose step).

Click to enlarge

That’s it! In 11 steps, you’ve scheduled a weekly digest that will email recipients a table of clickable “Add to calendar” links for upcoming opportunities.

To test your flow, rather than wait for the start of your schedule, use “Test.”

To elevate this concept to the next level, you can adjust your HTML filter to only get a certain category and send multiple emails to subscribers of different categories (say, only PowerApps subscribers getting PowerApps courses). You could also create multiple HTML tables and show “SharePoint courses” in one table, “OneNote courses” in the next and so on.

Update a hyperlink or picture column in SharePoint using Microsoft Power Automate (Flow)

Setting a hyperlink column’s value using Power Automate is a bit different than setting other column types’ values. In SharePoint, a hyperlink column has two components – address and description. If you update this column type using Power Automate’s “Update item” action, your address and description are both set to the same value.

In SharePoint Designer workflows, we could do this easily with the usual “Set field” action:

But in Power Automate, we only get one field which maps to the URL/address part of the hyperlink field, and is duplicated as the description as well in SharePoint.

And if you’re thinking it could work with a comma, as some other field types might, it won’t. You’ll get “Enter a valid uri.”

To get this to work so we can set both address AND description as separate values, we have to use an HTTP request action instead of (or in addition to) the Update item action. This isn’t as complicated as it may sound. Here are the steps:

1. Add the Send an HTTP request to SharePoint action

2. Choose or enter your site for Site Address

3. Set Method to POST

4. Set the Uri to the following, changing List Name to your list’s name, and replacing [ID] with the ID field from dynamic content

_api/web/lists/GetByTitle('List Name')/items([ID])

5. The easiest way to set your headers is to copy and paste this text into the “Text mode” option as demonstrated in the GIF that follows.

{
   "Content-Type": "application/json;odata=verbose",
   "X-HTTP-Method": "MERGE",
   "IF-MATCH": "*"
 }
Click to enlarge

6. Lastly, in Body, you can copy and paste this as well, but:

  • replace List_x0020_Name with your own list’s name. _x0020_ should replace any spaces in your list name.
  • replace Hyperlink with the name of the hyperlink column’s name
  • replace Google with the label/description/clickable text you want for the hyperlink
  • replace http://www.google.com with the URL/address part of the hyperlink
{'__metadata': {'type':'SP.Data.List_x0020_NameListItem'},'Hyperlink':
 {'Description': 'Google',
 'Url': 'http://www.google.com'}
 }

Your final result should resemble this:

Click to enlarge

Test that out. When it’s working and you’re ready to take it to the next level, you can replace the static url (like Google’s in my example) with dynamic content. For example, you could create convenient one-click links to an item’s version history:

Click to enlarge
Click to enlarge

Restrict who can install on-premises data gateways for the Power Platform

Data gateways allow users to connect online services, such as Power BI service, Power Automate, and Power Apps to on-prem data sources such as SQL databases, SharePoint server lists and libraries, and network shares.

As you can imagine, you wouldn’t want everyone installing their own individual gateways throughout your organization. Managing and sharing those centrally is much more efficient (and secure). You can manage who is allowed via the Power Platform admin center at admin.powerplatform.microsoft.com.

Note: You must be one of these roles to restrict gateway installers:

  • Azure AD Global administrator
  • Office 365 Global admin
  • Power BI service administrator

Restricting installations does not impact gateway administration. You can assign and re-assign users to administer and use gateways at any time. The following steps are strictly to manage who is able to install an enterprise gateway on a machine.

1. Go to the Power Platform admin center

2. Click “Data gateways”

3. Click “Manage gateway installers”

4. Toggle “On” the Restrict users in your organization from installing gateways

Click to enlarge

5. Add authorized users.

In just a few clicks, you’ve enabled better management of enterprise access to on-premises data sources for scheduled data refreshes, apps, and flows.

Click to enlarge

Create a direct link/URL to version history for a SharePoint list item or file

Imagine getting an email with a direct link to review the version history of an item or file. Or being able to have a single click from a list view to an item’s version history without going through menus.

The link itself is easy to structure manually. You could also structure it automatically using SharePoint Designer workflows or Power Automate flows.

No matter your method, you’ll need to be able to get two pieces of information: the list GUID and the item/file ID.

Getting required info

Get the list GUID

The list GUID is easy to get. Just go to the list –> List Settings and copy everything in the URL after List=.

Click to enlarge

Example:

http://SiteURL/_layouts/15/listedit.aspx?List=%7BAC2DE34F%5GH6IJ%KL789M%N01OP2%QR3STUV4WX4YZ56%7D

Get the item/file ID

In SharePoint Designer or Power Automate, you can just use the ID field provided as a lookup option. This way it’s always dynamic.

But to get the ID manually without workflow, view/open the item and check the end of the URL: for the number following “ID=”. Example:

http://SiteURL/ListName/DispForm.aspx?ID=532

For document libraries, or if you’re using modal dialogs (pop-ups), your URL won’t show ID. In this case, you can add the default ID column to your list view to get it.

Tip: For list items (not documents) you can also hover your pointer over the link to the item to see the URL preview at the bottom of your browser before clicking it.

Building the link

Manually construct it

The basic structure, no matter which method you use, is as follows:

https://SiteURL/_layouts/15/Versions.aspx?list=GUID&ID=ID

Just replace the SiteURL, list GUID, and item/file ID.

Tip: The easiest way may be to copy the entire URL when getting the list GUID, change listedit to versions, and add &ID=ID to the end.

Use SharePoint Designer

In SharePoint Designer, you can use String Builder when creating hyperlinks in emails, or when populating fields (such as a hyperlink column).

Click to enlarge

Use Power Automate

In Power Automate, you can initialize a variable to structure the version history URL so that you can use it repeatedly in different steps/conditions without having to structure it every time. We do this similarly to how we did for SharePoint Designer above. Type almost the entire URL which doesn’t change, then use the dynamic content panel to add ID to the end.

Click to enlarge

Then you can send an email and use a little code to hyperlink text with the variable used as the URL. If you’re not comfortable with code, you can just put the entire URL in the body of the message. The following shows an Outlook step in “Code View.”

First, click “Code View”

Alternative ideas

Calculated column

Note: You could use a calculated column for this, but the [ID] field needed will only populate once, then remove itself on the next property edit. The only way around this is creating another column like ID1 as single line of text and using a workflow (SPD or Power Automate) to set that field to the ID number. Then you can use [ID1] in your calculated column with a formula such as this:

="https://site/_layouts/15/Versions.aspx?list=GUID&ID="&[ID1]

Hyperlink column

Consider using SharePoint Designer or Power Automate to set a “hyperlink” type field to the URL. This could then be used in a list view to have a one-click link to version history.

GCC solution: “Your org doesn’t allow Microsoft Flow. You’ll need to use your personal email to sign up and get access.”

I’m currently on an adventure into my first GCC (government) tenant. When I went to https://flow.microsoft.com in this new GCC tenant, I got the following error:

“That didn’t work. Your org doesn’t allow Microsoft Flow. You’ll need to use your personal email to sign up and get access.”

I was in the habit of using that URL for quick access. Turns out the GCC tenant uses a different Flow. If you use the app launcher in your tenant to get there, you’ll notice the correct URL to memorize is https://gov.flow.microsoft.us (or your locality).

If that doesn’t work for you, and you’ve tried to get to Flow through the App Launcher (waffle icon in the upper left of Office online or SharePoint), it could very well be that your organization doesn’t allow Flow.

One major difference I’ve noticed so far is that Flow for government O365 tenants only has around ~85 connectors currently where as Flow for commercial O365 tenants has around ~275 connectors.

The following are the available connectors currently in GCC tenants:

Click to enlarge

Alertus integration in SharePoint via Microsoft Flow

I’m by no means an HTTP request expert, but the requirement of being able to integrate business applications is common and, in this case, important. I took some time to figure out how we could implement Alertus integration and am sharing what worked here.

Background

Alertus is a program we use at LMH Health to push mass-notification desktop and mobile alerts to the organization during times of severe weather, evacuation procedures, or just software downtime.

Image from alertus.com

We use SharePoint as a supplement for “breaking news” by adding a banner to the top of our site during times of urgency. We wanted to integrate Alertus with SharePoint so that when an alert fired, it created a breaking news banner in SharePoint as well. This makes sure users who aren’t on a managed device, such as working from a home computer, are still able to get important news if they happen to be on the intranet.

In times of emergency, it’s best to utilize as many channels as possible to inform your users. Integration of these two services meant there’d be minimal delay in broadcasting a single message through all available channels.

How it works

To send the Alertus alert data to SharePoint, we needed something capable of receiving an HTTP request so Flow came to mind first. Once configured, your Alertus administrators will just send alerts as usual, being sure to include SharePoint in the alert profile.

  1. Create and send an alert in Alertus
  2. Flow receives HTTP request with alert data
  3. Flow creates item in SharePoint list used for breaking news

And, of course, with Flow we could then do any number of actions including additional HTTP actions, sending to email or phone, etc.

Note: This works for SharePoint server and SharePoint online. If using server, you’ll need the on-premises data gateway so that Flow can connect to your environment.

Setting it up

We need to do a few things to get this working:

1. Start creating a Flow (to get the HTTP POST URL)
2. Create the Alertus Service for “SharePoint”
3. Add the Alertus Service “SharePoint” to any Alert profiles for which you want to include SharePoint for distribution
4. Finish the Flow
5. Test

Start creating a Flow

1. Create a new Flow with the When a HTTP request is received trigger

2. Paste the following into the Request Body JSON Schema

{
    "type": "object",
    "properties": {
        "Content-Type": {
            "type": "application/json"
        },
        "id": {
            "type": "string"
        },
        "clientName": {
            "type": "string"
        },
        "clientVersion": {
            "type": "string"
        },
        "sender": {
            "type": "string"
        },
        "message": {
            "type": "string"
        },
        "sentDateTime": {
            "type": "string"
        },
        "expires": {
            "type": "string"
        }
    }
}

3. Expand Show advanced options and make sure method is POST

4. Copy the HTTP POST URL at the top of the step using the copy icon

Create an Alertus alert service for SharePoint

1. Go to Alertus, then click Configure System > Alert Services

2. Click +Add Alert Service

3. Select HTTP Request for Service Type, then name and describe the service.

4. Configure the rest of the alert service as follows, pasting in the content provided below

Click to enlarge

Activation Headers

Accept: application/json, text/plain
Content-Type: application/com.alertus-v1.0+json

Activation Body

{ "id":"${alert.originId}",
 "clientName":"${alert.clientName}",
 "sender":"${alert.sender}",
 "message":"${alert.text}",
 "sentDateTime":"${alert.sentDate.yyyy-MM-dd'T'HH:mm}",
"expires":"${alert.expirationDate.yyyy-MM-dd'T'HH:mm}"
}

5. Click Save Alert Service

Add the service to alert profiles and/or preset alerts (optional)

Your service is now available to be added to alert profiles (pre-selected sets of services to notify under certain circumstances, such as an evacuation) and preset alerts (customized alerts and notification groups).

Alert profile example of adding the new SharePoint service we created
Preset alert example of adding the new SharePoint service as a delivery method for the alert

You could, alternatively, choose not to include it in anything preset and only use it manually upon creation/configuration of an alert.

Finish the Flow

1. Go back to the Flow you started.

2. Add a HTTP Response step and make sure the Status Code is set to 200. This lets Alertus know the request was received and prevents an error from occurring on the Alertus side.

3. Add a SharePoint: Create item step and connect to the site and list for which you want to create an item from the alert details.

Test

From Alertus, do a Custom Activation of a test message just for the SharePoint service/delivery method.

In the example below, I also threw in an email alert as a step but you can see the whole process in Flow’s run history still only took 2 seconds.

Check in SharePoint that the item was added. In my case, we use custom script to display the most recent list item as an active alert.

Additional Configuration (optional)

Now that you’ve done the basics, you may wish to make adjustments.

  • Check out Alertus’ Knowledgebase (must be a customer with login credentials) at https://my.alertus.com/ .
    • Search for HTTP Request (Documentation) where you’ll find additional placeholder variables you could use in the Activation Body and JSON Schema fields to send additional details.
  • Add additional steps in Flow
    • An approval process before posting the details
    • An email or text message to a person or group
    • Update a spreadsheet/log
    • Post a message to Teams

Use Microsoft Flow to automatically convert RTF files to DOC

I currently work in healthcare and have run into situations with users where some data files are exported from software solutions in .rtf or .txt format only. To improve the user experience once these files are dropped into SharePoint, we can convert any .rtf file automatically to .doc.

Why?

When RTF files are opened in the browser, the option to “Edit in browser” is grayed out since the file type isn’t compatible with that functionality.

Click to enlarge

Okay, but why .doc and not .docx?

Unfortunately, we can’t convert from RTF to DOCX without the use of Azure functions (thanks, Pieter Veenstra for that info). But if we can at least convert it to DOC for users, they’ll get the “Edit in Browser” option which will then prompt them to convert the file to DOCX in two clicks (Convert –> Edit). Then we finally have a .docx file.

Click to enlarge

How to auto-convert .rtf to .doc using Flow

Prepping the document library

First, since we don’t want anything to happen to files that aren’t .rtf, we’ll need to create a column in our list to display “extension.” Then we’ll use SharePoint Designer to populate the extension and Title fields whenever an item is added or changed. Flow can’t use file name fields, so we’ll set title to match name.

  1. Library settings –> Add column –> Single line of text (name it extension and save)
  2. Create a SharePoint Designer workflow (2010 or 2013 – doesn’t matter for this purpose) that triggers on creation or change
  3. Set extension to file type, and title to name

Publish the workflow. Now when a .rtf file is dropped into the library, it will set “rtf” in the extension column and set the blank Title field to the same as “Name” so that we can use it in Flow.

Create the flow (.rtf to .doc)

Trigger and condition

  1. Create a new flow with the trigger “When a file is created or modified (properties only)” and select (or enter) your site and library name.
  2. Create a condition step that checks if “extension” is equal to rtf

“If yes” box

Now we’ll create four steps in our “if yes” box. Configure each as pictured below, careful with file paths not to include the whole URL – just the relative path beginning something like /Shared Documents/…

  1. Get file content using path (gets contents of the .rtf file)
  2. Create file (creates new .doc file using the .rtf contents)
  3. Get file metadata using path (gets original .rtf file)
  4. Delete file (deletes original .rtf file to avoid confusion)

Expand the flow (optional – delete .doc if .docx created)

If no box (optional)

Users can now easily convert .doc to .docx in two clicks. But it leaves the original file (.doc) behind which can be confusing. So we can create steps in our Flow to delete the .doc file IF a .docx by the same name is created.

In the no box, we’re going to create another condition step to check if the extension equals .docx.

If yes, use a “Get file metadata using path” step to get any file by the same name from that library (use the “Title” dynamic content from the trigger step and add a .doc). Then in your “delete file” step, simply use the ID from the get metadata step.

Now when a user converts a .doc to a .docx file in the browser, your Flow will delete the .doc that’s left behind. Note that there will be a delay.

  1. SharePoint Designer has to wait until you’re out of the editor to reassign the “extension” field to .docx
  2. Flow has to wait for SharePoint Designer to make that change so that it triggers appropriately

While this is certainly not an ideal workaround, it may be the best you’re able to do with the resources on hand. Not everyone has Azure, or developers, premium add-ins/vendors or the budgets for such things. Best of luck!

Here’s a view of the full flow, highlighting which steps I’m pulling dynamic content from. Click to enlarge.

Click to enlarge

“The selected file doesn’t contain template elements” error in Microsoft Flow

I recently tried to use the “Populate a Microsoft Word template” step in Microsoft Flow (currently in preview) to insert text into content controls, but ran into the error above.

The selected file doesn’t contain template elements.

The issue was that my content controls in the template were of rich text format and date. This preview step currently only supports plain text, combo box, and dropdown content controls.

Plain text, combo box, and drop down are the only supported content controls as of the publishing of this post.

So for all of your text fields, make sure you use the correct (plain text) control:

Once I replaced my rich text content controls with plain text, the content controls showed up in Flow as options for populating:

To keep this organized, I recommend giving each content control a title (in its properties) so you can easily identify each field when in Flow (select content control, then “Properties” from developer tab).

Remember, you’ll only see supported content type fields in Flow. Even if you have a date content control, you won’t be able to populate it using Flow.

Use the Flow recurrence trigger to run flows only on weekdays

Running a flow on every weekday or certain weekdays

Rather than using Flow’s recurrence trigger with a frequency of “days” combined with switch cases/conditions, you can actually just use the “Week” frequency time unit and select days from a drop-down with no further effort required.

For weekdays, just select Monday-Friday. Or if you just want MWF, you could do that as well.

A techie way to do it

The alternative is to initialize a variable such as

@and(greater(dayOfWeek(utcNow()),0),less(dayOfWeek(utcNow()),7))

with a switch case to determine if today’s date is, in fact, between 0 (Sunday) and 7 (Saturday).

Compare today’s date with holiday calendar

So that being said, the limitation of using the “Week” frequency for weekdays might be if you want to prevent it from running on holidays when nobody is in the office even if it is, in fact, a Monday. In that case, you could add a condition that checks to see if @utcNow() matches items from a SharePoint list (holiday calendar?).

  1. Initialize variable (integer) value 0
  2. Get items from SP list (calendar)
    1. You could add an ODATA filter in the Get items step to only filter to items with Start times greater than today
  3. Apply to each –> IF utcNow()=SP item, THEN increment variable by 1, ELSE nothing
    1. Use the expression builder to formatDateTime both dates to be sure they match when being compared
  4. IF variable is greater than 0, do nothing (don’t run), else run the rest of the Flow

Flow now supports multiple condition controls for advanced if/then scenarios within a single step

Say goodbye to nested if/then statements in Flow taking up fourteen monitor widths. Flow now supports nested if/then statements all in a single, vertical step. For example, the following requires that WeekDayNum is not 0 or 7 AND requires that either Bob or Nate created the item. And I didn’t have to scroll horizontally at all to see it!

The next time you use a condition control, enjoy rethinking how you might structure your various requirements for conditions to be met.

To get started, just add a condition control as you normally would:

  1. Add an action
  2. Condition control


Enjoy!