Posted on

How to reset a SharePoint column value to blank using Power Automate

In Power Automate, setting an already-populated SharePoint list or library field to blank isn’t as simple as leaving the field blank in your flow. Luckily, it’s just a couple extra steps to make it possible.

In this post I’ll cover multiple column types as they’re a bit different.

  • How to set text, date, number, and yes/no column values to blank
  • How to set single choice column values to blank
  • How to reset multi-choice column values to blank
  • How to reset person column values to blank

How to set SharePoint text, date, number, and yes/no column values to blank

The steps in this section will work for these column types:

  • Single line of text
  • Multiline text
  • Date
  • Number
  • Yes/No
  1. Click into the field you want to reset to blank
  2. Select Expression
  3. Type Null in the expression box (not case-sensitive)
  4. Click OK
Adding a Null expression in Power Automate | Click to enlarge

Test your flow and it should reset the field value(s) to blank wherever you used the Null expression.

Showing the fields that were reset using Power Automate | Click to enlarge

How to set SharePoint single choice field values to blank

To set a choice field back to blank (no selection) follow these steps:

  1. Add a step before updating the item that initializes a variable. The step is called Initialize Variable.
  2. Set the variable name to Blank, and the type to String. Do not set a value.
  3. In your update action, set the choice field value to the new variable from dynamic content.
Creating a blank variable in Power Automate | Click to enlarge

Test your flow, and you should see your single choice field reset to no choice.

Result of a Power Automate flow resetting a choice field | Click to enlarge

How to reset SharePoint multi-choice column values to blank

A normal choice column in SharePoint has more options that allow it to be set to multiple choice.

Multiple selection option for a choice field in SharePoint | Click to enlarge

Changing a single-select choice column to a multi-choice field alters how Power Automate resets the field, but it’s still similar to the steps involved for a single choice field.

To set a multiple-choice field back to blank (no selections) follow these steps:

  1. Add a step before updating the item that initializes a variable. The step is called Initialize Variable.
  2. Set the variable name to BlankArray, and the type to Array. Do not set a value.
  3. In your update action, select the T icon next to your multiple-choice field.
  1. Set the multi-choice field value to the new variable from dynamic content.
Power Automate actions resetting a multiple-choice field to blank | Click to enlarge

Test your flow and your multi-choice fields should now reset to blank.

Result of a Power Automate flow resetting multiple-choice fields to blank | Click to enlarge

How to reset SharePoint person column values to blank

Perhaps one of the more complicated column types to reset, a person field takes a bit more work to reset. To accomplish this, we can use an HTTP request. Follow these steps to empty a SharePoint person column’s value.

  1. Add the Send an HTTP request to SharePoint step
  2. Following the screenshot below, set the fields as follows (you must replace all red items with your own values):
    • Site Address (select your site)
    • Method: POST
    • Uri: _api/web/lists/getbytitle(‘YOUR LIST TITLE‘)/items(YOUR ITEM ID FROM DYNAMIC CONTENT)
    • Headers
      • IF-MATCH | *
      • X-HTTP-Method | MERGE
      • Content-Type | application/json;odata=verbose
      • Accept | application/json;odata=verbose
    • Body:
      {
      “__metadata”:{
      “type”:”SP.Data.YOUR_x0020_LIST_x0020_TITLEListItem”
      },
      “YOURPERSONFIELDId”:”-1″
      }

Important notes for the Body field

If your list name has a space, replace it with _x0020_ such as Null_x0020_demo.

Field names are different than display names. Even if your field displays with a space (Person field) it is likely Personfield on the backend. You can verify your column’s actual name by going to List settings (gear icon) and selecting your column from the Columns section. Your field’s actual name will be displayed in your browser’s URL.

How your HTTP request should look when finished | Click to enlarge

Test your flow and you’ll find that your person column has been reset.

Posted on

How to get Planner task Completed By dynamic content in Power Automate

You can use a template in Power Automate to send an email when a Planner task is completed. However, this template returns the Completed by field as a user ID, and not as a display name. And the dynamic content available for a completed task does not include Completed by. We can, however, get this data using an expression. Here’s how to do it (video at bottom of post):

  1. After your trigger (When a task is completed), insert the Get user profile (V2) step.
  2. Click inside the User (UPN) field, then select Expression from the dynamic content panel
Click to enlarge
  1. Paste the following expression in the box and click OK
triggerOutputs()?['body/completedBy/user/id']
  1. Now, in your next step (email, Teams post, etc. – however you’re sharing the completion message), use the dynamic content from the Get user profile (V2) step to insert Display Name.
Click to enlarge
  1. Save and test your flow.
Posted on

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(replace(body('Create_HTML_table'),'&lt;','<'),'&gt;','>'),'&amp;','&'),'<table>','<table border="3" bgcolor="ffffff">'),'&quot;','"')
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.

Posted 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

Posted on

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
Posted on

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.

Posted on

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
Posted on

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

Posted on

Use Microsoft Power Automate (Flow) to automatically convert RTF files to DOC

I once ran 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 convert from RTF?

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 Power Automate (Flow)

Update 12/3/2020 (big thanks to Dawid Ziolkowski for the tip on trigger condition)

Create the flow (.rtf to .doc)

  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. Click the ellipsis and Settings.

3. Add a Trigger Condition as @endsWith(triggerOutputs()?[‘body/{FilenameWithExtension}’],’rtf’) and click Done.

Now the flow will only run if the file’s extension is RTF.

Next, configure each next step as pictured below (beginning with the blue-outlined Get File content using path step), careful with file paths not to include the whole URL – just the relative path beginning something like /Shared Documents/…

  1. Trigger step for when file is created (we already configured this with the trigger condition)
  2. Get file content using path (gets contents of the .rtf file – use File name with extension dynamic data from step 1/red)
  3. Create file (creates new .doc file using the .rtf contents – use dynamic data from step 2/blue)
  4. Delete file (deletes original .rtf file to avoid confusion – use Identifier from step 1/red)
Click to enlarge

That’s it! Now when an RTF file is added, it’ll be replaced with a DOC version and the RTF file will be deleted. Any non-RTF file won’t be touched, and won’t trigger/count as a flow run.

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

Users can easily convert .doc to .docx in two clicks in the browser. But it leaves the original file (.doc) behind which can be confusing for users. So we can create a separate flow to delete a .doc file IF a .docx by the same name is created.

It starts out the same as our first flow, with a “when a file is added” step paired with a trigger condition but looking for .docx this time.

Create the second (optional) flow (delete .doc version if .docx version with same name exists)

  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. Click the ellipsis and Settings.

3. Add a Trigger Condition as @endsWith(triggerOutputs()?[‘body/{FilenameWithExtension}’],’docx’) and click Done.

Then use a “Get file metadata using path” step to get any file by the same name from that library (use the “Name” dynamic content from the trigger step and add a .doc). Then in your “delete file” step, simply use the ID from the get file 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 if there’s a match. Note that this runs on ANY .docx file in the library when added/modified, and will fail if no .doc name match is found. There’s probably a cleaner way to do this.

While these are certainly not ideal workarounds, 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!

Posted on

“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.