Posted on

How to create an automated, chat-based team availability system using Power Automate and Microsoft Teams

Efficiently managing and communicating team availability in a hybrid world is crucial for smooth operations in any organization. This guide will show you how to create an automated yet interactive system for tracking and communicating the availability of general staff members using Power Automate, Microsoft Teams, and SharePoint.

Step 1: Understanding the Business Process

  • Weekly Availability Surveys: Each Friday, staff members receive a personalized survey through a Teams chat message to submit their availability for the following week.
  • Confirmation and Preview: Upon submission, staff members get a confirmation message and a preview showing the team’s availability as of the time they submitted their own.
  • Comprehensive Monday Update: On Monday, everyone receives a complete overview of the team’s availability for that week, helping them make the most of everyone’s availability.

Video demonstration

The following video will demonstrate steps 2-3 (building the SharePoint list, and walking you through the Power Automate flow).

Step 2: Setting Up SharePoint

  • Create a SharePoint List: Establish a SharePoint list for logging staff availability. Ensure it’s accessible to all staff members who will be participating and/or reviewing availability of teammates. It needs:
    • A person column type for Employee
    • 5 single line of text columns – one for each weekday. In my demo, I’ve repurposed and renamed the default Title column for Monday.
The supporting SharePoint list upon creation | Click to enlarge
  • Manage Permissions (optional): Assign appropriate permissions to staff members to view and edit the availability list. In order for someone to submit their availability via the chat request, they’ll need Contribute or Edit access at least. If you added the list to a site accessible to all relevant team members, they already have access to edit the list by default.

Step 3: Automating with Power Automate

  • In-Office Status Survey Flow:
    • Trigger: Schedule to run every Friday at 10:00 AM.
    • It should first gather data from the SharePoint list (Get items) to know whom to ask for their availability
    • Before asking an employee for their availability, I use the following in my Update item step’s Monday, Tuesday, Wednesday, Thursday, and Friday fields to reset them each week:
⏳ Requested
  • Post an Adaptive Card : The chat message to each employee uses the ‘Post an adaptive card and wait for a response’ action. For the Message of the adaptive card step, utilize the provided JSON below. The only thing you’d want to update is the company logo URL to include your own. You could also adjust things like ‘Telework’ to ‘Work from home’ or any other verbiage you choose.
{
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "type": "AdaptiveCard",
    "version": "1.3",
    "msTeams": {
        "width": "full"
    },
    "body": [
        {
            "type": "ColumnSet",
            "columns": [
                {
                    "type": "Column",
                    "width": 5,
                    "items": [
                        {
                            "type": "TextBlock",
                            "text": "Good morning!",
                            "weight": "Bolder",
                            "size": "Medium",
                            "style": "heading"
                        },
                        {
                            "type": "TextBlock",
                            "text": "Let us know where you'll be working next week so we know how best to collaborate with you.",
                            "isSubtle": true,
                            "wrap": true
                        }
                    ]
                },
                {
                    "type": "Column",
                    "width": 1,
                    "items": [
                        {
                            "type": "Image",
                            "url": "https://i0.wp.com/natechamberlain.com/wp-content/uploads/2022/08/cropped-Nate-Chamberlain-logo-icon-e1661192398264.png",
                            "altText": "Company Logo",
                            "size": "auto"
                        }
                    ]
                }
            ]
        },
        {
            "type": "Container",
            "items": [
                {
                    "type": "ColumnSet",
                    "columns": [
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Monday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Monday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Tuesday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Tuesday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Wednesday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Wednesday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        }
                    ],
                    "bleed": true
                }
            ]
        },
        {
            "type": "Container",
            "items": [
                {
                    "type": "ColumnSet",
                    "columns": [
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Thursday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Thursday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33,
                            "items": [
                                {
                                    "type": "TextBlock",
                                    "text": "Friday",
                                    "wrap": true,
                                    "weight": "Bolder",
                                    "horizontalAlignment": "Center"
                                },
                                {
                                    "type": "Input.ChoiceSet",
                                    "choices": [
                                        {
                                            "title": "In office",
                                            "value": "🏢 In office"
                                        },
                                        {
                                            "title": "Telework",
                                            "value": "🏠 Telework"
                                        },
                                        {
                                            "title": "Leave",
                                            "value": "🌴 Out of office"
                                        }
                                    ],
                                    "placeholder": "Select",
                                    "style": "expanded",
                                    "id": "Friday",
                                    "isRequired": true,
                                    "errorMessage": "Please make a selection"
                                }
                            ]
                        },
                        {
                            "type": "Column",
                            "width": 33
                        }
                    ]
                }
            ]
        }
    ],
    "actions": [
        {
            "type": "Action.Submit",
            "title": "Submit"
        }
    ]
}
  • Get the updated list of availability (Get items 2) including the new response, making sure to use the following filter query to only get rows where people have responded for the coming week:
Title ne '⏳ Requested' and Title ne null
  • After someone responds, they’ll get a chat preview of the whole team’s availability for the week.
  • In-Office Status Results Flow:
    • Trigger: Schedule to run every Monday at 9:00 AM.
    • This flow compiles survey responses into an HTML table (similar to how we did the first flow) and distributes it to the team via a Teams group chat message where they can ask follow-up questions, provide additional context, coordinate meetings, etc.
    • Important – Exclude the Sender: The sender/owner of the flow should not be included in the Monday communication to recipients (assuming they’re using their credential to connect for that step in the flow). Since they’re the one connecting to Teams via Power Automate, they’re already included in the eventual group chat. Attempting to include them will result in an error.

Conclusion

By following these steps, you can set up a streamlined and automated process to manage the availability of your team. This system not only saves time but also ensures clear and consistent communication among all staff members.

Posted on

Use Power Automate to create a Planner task whenever an item is added to a SharePoint list

My newest video demonstrates how you can create a task in Planner whenever an item is added to a SharePoint list. In my example, I’m assigning Planner tasks whenever a new IT Support Request comes in.

For this to work, you’ll need:

  • A SharePoint list
  • A Planner plan for your team to share

How to automate Planner task creation based on SharePoint list additions

  1. Prepare your SharePoint list (any columns you’re tracking)
  2. Prepare your Planner plan (set up buckets, labels, etc. as you wish)
  3. Create the Power Automate flow following steps in my video.
    • Type and trigger: Automated when an item is created (SharePoint)
    • Second/last step: Create a task (Planner)
      • Expression used to set due date a week after creation: addDays(triggerOutputs()?[‘body/Created’],7)
The flow I build in the video | Click to enlarge
Posted on

How to Alternate Request Assignment to Responsible Persons for various categories

animals birds feathers flamingos

I had a unique challenge recently to alternate assignment of requests (tracked in a SharePoint list) to individuals as they were created. User #1 would get requests 1, 3, 5, etc. and User #2 would get requests 2, 4, 6, etc. To make it more interesting, different pairs of experts might be assigned to different request topics, and sometimes a single individual would be responsible for a particular topic. The overall idea, though, was a somewhat evenly dispersed workload given quickly and efficiently to those who were responsible for those request areas.

One (less than ideal) option to accomplish this is to use a Get Items (SharePoint) step on the requests list with a Top Count of 1 and Order By OData query (Created desc) for the most recently created request, see who the assignee is, and then assign to the other person in that category’s assignee field (when applicable). But I needed something that was better-performing, sophisticated, simpler in design, and not dependent on any historical items in case they were deleted, re-assigned, etc.

So, to determine the assignment as requests randomly trickle in over time, I decided to have Power Automate determine whether a new request’s ID number (auto-generated by SharePoint) is odd or even. Odd requests would go to the first assignee of a subcategory, and even would go to the last. If there was only one assignee in a category, they’re both the first and the last so it still works.

A general good practice when building flows is to make sure you don’t put a particular assignee’s identity explicitly in a variable, task, or email step. For example, you wouldn’t want to put user #1’s email directly in an Outlook email step’s To line. This makes sure that if an employee leaves the company, that the flow won’t need a revision. Instead, I created a list in the team’s SharePoint site with request categories, sub-categories, and responsible persons. My flow refers to this list each time it needs to make an assignment, making sure it has up-to-date contacts for each subcategory.

Example of a request categories list | Click to enlarge

The managers of the process keep this updated with no more than two assignees per subcategory. This way, when new employees join, the list gets an update for what subcategories they’ll be handling, and the flow continues without a hiccup.

What you’ll need to build this solution

Overall, for this process to work, you’ll need:

  • A list of request categories with these columns:
    • Category (choice)
    • Subcategory (text or choice)
    • Assignees/Responsible (person with multi-select enabled)
  • A list of requests (submitted via a SharePoint form, Microsoft Forms form via Power Automate, or Power Apps app via Power Automate)
    • I’d recommend Power Apps if you have the time and resources – this way your choices (categories and subcategories) in the form can be dynamic (only have to be managed in one place) and reference your categories list without granting access to the main request list, or even the SharePoint site, itself
    • Consider adding additional fields for your assignees like Notes, Status, Priority, etc. for them to add additional context, reminders, and status updates as they work the requests
  • The Power Automate flow for assignment determination

How to build a flow that assigns tasks evenly and by category

The following video will walk you through the necessary components to build this flow in Power Automate. The result will dynamically assign tasks to responsible persons based on categories they’re responsible for, as well as alternating tasks to help distribute workloads more evenly within each category.

Ideas to enhance your categories list with additional fields for different assignment scenarios

Consider taking this concept further by adding additional columns to your categories list. Things like:

  • Sequential assignment (Yes/No) – Assign to the first person in the multi-select person field and if they don’t mark it complete within 2-3 days, add the second person as an assignee.
  • Assign to all (Yes/No) – Maybe some subcategories should have all assignees involved in the resolution. A simple checkbox would have the flow assign to both (when applicable).
  • Request specific assignment (Yes/No) – Not everything can be randomly or evenly assigned. You may wish to choose on a case-by-case basis. Set your flow up to send an adaptive card to the process manager(s) asking for selection of an individual for each request that comes in with this Yes/No checked.
Posted on

How to use the Mod function in a Power Automate expression to determine if a number is odd or even

Using the Mod function in an expression in Power Automate, you can quickly determine whether any numeric value is odd or even. In this post’s examples, I’ll be using a SharePoint item (or document) ID field as my numeric value, and I will determine if it is odd or even.

Did you know?
Every item and document in SharePoint is automatically assigned a unique ID number within its list or library. The field is called ID.

What is the Mod Function?

The Mod function takes two arguments: the number you want to divide and the divisor. It returns the remainder of the division. For example, Mod(5,2) returns 1, because 5 divided by 2 has a remainder of 1.

To check if a number is odd or even, you can use the Mod function with 2 as the divisor and compare the result (using the equals function) with 1. If the result is 1, then the number is odd. If the result is 0, then the number is even.

You can use the if and equals functions together to write this logic as a conditional expression. Here is an example using a SharePoint item ID as the number:

if(equals(Mod(outputs('Get_item')?['body/ID'],2),1),'Odd','Even')

This expression says: if the SharePoint item ID divided by 2 has a remainder of 1, then return ‘Odd’, otherwise return ‘Even.’

You can use any numeric field instead of the SharePoint item ID, as long as it is available in your flow.

You can learn more on the Mod function here.

And explore a real scenario in which I used this idea in my other blog post.

How to Add the Expression to Your Power Automate Flow

Tip: I have a video of these steps if you’d prefer.

First, to begin adding your expression, click within a step’s field where you want Odd or Even to be returned, such as a variable or condition statement. Then, in the dynamic content panel that pops out, select Expression and you’ll be provided with a formula bar.

How to add an expression to a Power Automate flow | Click to enlarge

In your expression formula bar, use the following formula (modifying the field reference to your own numeric field):

if(equals(Mod(outputs('Get_item')?['body/ID'],2),1),'Odd','Even')

This says if my numeric field divided by 2 has a remainder of 1 (making it an uneven quotient) then it’s odd, otherwise it’s even.

You can then use the result of this expression (Odd or Even) in further conditional expressions or condition blocks like this:

A condition block based on the result of odd/even expression | Click to enlarge

How to Insert a Dynamic Content Field into the Expression

If you are not sure how to find the reference for your numeric field, you can use the dynamic content panel to insert it into your expression. Here are the steps:

  1. Write your expression up until the point you need to enter the field name
Beginning of conditional Mod expression in Power Automate | Click to enlarge
  1. With your cursor active where you want to enter the field name (right after Mod( in my example), select Dynamic content, then select the numeric field you are using. In my case, it’s the ID field from a SharePoint list item.
Insertion of dynamic content in a Power Automate expression | Click to enlarge
  1. The selected field reference will automatically appear in your formula where your cursor was at the time of insert.
  2. Finish your expression. It might resemble this if you’re referencing a SharePoint item’s ID from a trigger like When an item is created:
if(equals(Mod(triggerOutputs()?['body/ID'],2),1),'Odd','Even')

Video: How to determine if a number is odd or even in Power Automate using the Mod function

That’s it! You have learned how to use the Mod function in Power Automate to determine if a numeric value is odd or even. I hope this helps you create more powerful and sophisticated flows. Good luck!

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.