“I’m creating custom id for each new SP item, how do I get the latest one so I can increment it within Power Automate flow?”

---

When creating basically any data (not only in SharePoint), you should always have a unique ID. It’s so much easier to reference a specific entry / request / anything if you can share just a single, unique value!

While SharePoint assigns their own ID to everything, it’s often not in the format you might like. It’s just a number, nothing else, and it starts from 1 in each list you use. No fixed length, no letters referencing the solution, only a number. Yet an ID like REQ-0001 might look so much better than just 1.

Utilise the SharePoint ID

One way to create a unique ID is to start from the SharePoint ID and extend it. The benefit of this approach is that the item ID is always unique. Even if you create 10 items at the same time, they won’t compete about the ID, they’ll all get their own.

The downside is that this solution always needs two steps – to get the SharePoint ID you must create the item. ‘Create item’ the item, take the item ID output of that action, use it to create the actual ID and update it.

You must also keep in mind that once a SharePoint ID is used it’s not available anymore in the list. For example, if you delete items from the list the IDs of those deleted items won’t be available anymore. You can easily create a hole in the ID sequence if you’re not careful.

With that in mind I’d say this is still the best solution. If you have just one custom ID sequence, you can use the IDs directly in the list. If you need more than one sequence you can create multiple ‘side’ lists, each of them used only to track the specific sequence. Create item in the side list, take its ID, and use it to create item in the main list.

Continue from the latest custom ID

The other approach, in case you use various custom IDs in a single list, is to search for the last ID so that you can increment it. Use the ‘Get items’ action with a filter and sorting to find the last item as already explained. Add 1 to the ID number and use it to create the item.

There’re a few things to be careful about as well though. Firstly, you’ll have to also check whether such ID was even used before. Secondly, you must extract the number from the ID to be able to increment it and turn it back into ID. And thirdly, the most serious issue – there’s a risk that the ID won’t be unique. If you create 10 items at once, they’ll all get the last item, increment its ID, and use it. For this reason you should always run only one such flow at a time.

That’s a bit too many potential problems and a reason why I’d avoid this approach.

Summary

When you need a unique, custom ID that’ll be created within your Power Automate flow, it’s a good idea to use the standard SP functionality. SharePoint will take care of any parallel requests for you, all you need is to create an item in some list, take the ID, and use it to create your custom ID. Unique number to which you just add the remaining characters.

---

Leave this field empty if you’re human:

The post How to assign the next custom ID using SP and Power Automate appeared first on Let's POWER Automate.

“I’m trying to turn my Excel file into a csv, but the Power Automate flow exports only the first 256 rows, how do I get the rest…?”

---

Microsoft Excel is still one of the commonly used data storage when using Power Automate. You have a file with many rows, lots of data, and a flow that’ll do some operations over it. But with a lot of rows comes also a small limitation – the ‘List rows…’ action will return only 256 of them. What if it’s not enough? If there’re more rows to process and Power Automate just ignores them?

Use filter to reduce the number of rows…

One solution might be to reduce the number of rows. If you don’t necessarily need all of them, you can filter only the ones you want using a filter.

… or turn on pagination

If you need all of them then you’ll have to turn on the pagination. It’s not enough to increase the ‘Top Count’ as when getting only 100 SharePoint items. For the ‘List rows…’ action you must go into the action settings…

…turn on the ‘Pagination’ and set a threshold to a higher number.

Click the ‘Done’ button and you’re done. The ‘List rows…’ action will now return all the rows you want.

Summary

Returning only 256 rows from an Excel file is very often a problem for Power Automate users. While you might think that it’s enough to increase the ‘Top Count’ as in the ‘Get items’ action it’s not, and it can cause your flow to not work properly in the future. That’s why you should always consider whether there’s a possibility of more than 256 rows in your Excel file and deal with that in the way explained above.

Turn on the pagination in the action settings and increase the number of rows, and don’t forget to use a filter whenever possible.

---

Leave this field empty if you’re human:

The post Get more than 256 rows from an Excel file in Power Automate appeared first on Let's POWER Automate.

“On each error I must restart the whole flow, is it possible to build a ‘restartable’ approval flow that’ll continue from the failed step in Power Automate?”

---

When building approval flows in Power Automate, you probably put all the steps in a single flow in a sequence. Assign an approval task to a user and once completed assign another one to the next approver. The same steps over and over again until everyone completes their approval task. But what if such flow fails? What if 2 approvers already approved and it failed during the 3rd approval? How do you restart this flow without creating the tasks for the first two approvers again?

Use a ‘state machine’ in your flow

Since you don’t want to create the same approval tasks for the users, you’ll need two things. Firstly, you must keep the information about the current approval step. Secondly, your flow must act based on this information when started.

The first prerequisite is simple – this article is about an approval flow, and each approval flow should utilise some ‘Status’ column with such information. If you don’t have a unique status for each approval step, you should add it now.

The second part of the solution is a bit more complicated. As already mentioned, the flow must act differently based on the stored information, in this case the approval status. Once it’s started it must check the current status to know where to continue – a use case for a state machine!

Following the state machine principle, you can build a flow that’ll trigger on a change in the status column. Depending on the approval status value a ‘Switch’ will store the current approval role in a variable.

Use it to find the current approver in a configuration list and assign the task. Once completed store all the information back to the item itself and set the next approval status.

A new instance of the flow will trigger, checking the new status and creating a new approval task for the next approver.

Summary

When building a restartable approval flow in Power Automate, you must somehow deal with the steps that were already completed. In this article it was done using a state machine, a repeated flow that’ll trigger multiple times during an approval process, each time creating task for a different user.

Just remember that the flows don’t share any information. If there’s any information that should be shared between the approval steps, e.g. the approval history, you must store it in the item itself as part of the flow.

---

Leave this field empty if you’re human:

The post Build a ‘restartable’ approval flow in Power Automate appeared first on Let's POWER Automate.

“I’d like to restore original permissions on all documents in a SharePoint library, how can I list files with unique permissions in a Power Automate flow?”

---

When restoring library permissions, it’ll remove all unique permissions on the library itself and use the site permissions instead. But that doesn’t mean it’ll restore permissions also on all the files in that library. If the permission inheritance is broken, the library settings doesn’t have any effect on such files.

Yet often you want to restore all permissions, on all the folders and all the files. How do you do that? How do you list all the files/folders with unique permissions to inherit them again?

Get the files with an HTTP request

The good news is that each file in the document library has a property called ‘HasUniqueRoleAssignments’ that’ll give you such information. The bad news is that this property is not accessible using the ‘Get files (properties only)’ action. As such, you’ll need an HTTP request to SharePoint where you’ll ask for this property specifically with the $select parameter. Since you’ll need also the id of the item to restore the permissions, take also the id.

Method: GET

Uri: _api/web/lists/getByTitle('<libraryName>')/items?$select=HasUniqueRoleAssignments,id

Notes: <…> are placeholders, replace them including the < and >. If you’d like also the file name/path/type you can add the corresponding properties to the $select.

This action will return all files in the library, not only the ones with unique permissions…

You’ll have to filter the results using the ‘Filter array’ action to keep only files whose ‘HasUniqueRoleAssignments’ equals true.

Navigate in the HTTP request output to get the array with the results…

body('Send_an_HTTP_request_to_SharePoint')?['d']?['results']

…and keep only the files whose HasUniqueRoleAssignments is true.

item()?['HasUniqueRoleAssignments']

The result will be only files with unique permissions. As you have their id, you can restore them to the original permissions – file by file, folder by folder.

Summary

To restore permissions on all files and folders in a SharePoint library, you must know ids of these files. And even though Power Automate doesn’t have a dedicated action that would list those documents with unique permissions, there’s still an HTTP request to save the day. List all the files including the ‘HasUniqueRoleAssignments’ property, filter only the ones where it’s true, and restore the permissions with another HTTP request.

---

Leave this field empty if you’re human:

The post Get list of all documents with unique permissions (Power Automate) appeared first on Let's POWER Automate.

“I’d like to send the request back and forth, to return it back to previous approval steps etc, how can I do some state machine in Power Automate?”

---

Some flows are not a simple, straightforward approval process. Sometimes you need more than just approve or reject, but also the ‘return to previous step’ option. How do you build such flow in Power Automate? A flow that doesn’t move only forward, but can occasionally move also backwards? Something like a state machine that can move between different states?

Utilise the infinite trigger loop

While the infinite trigger loop is often a painful issue, when building a state machine it’s quite useful. Since Power Automate doesn’t have a dedicated state machine logic, you’ll have to build it yourself. And for this you can benefit from the infinite trigger loop.

Instead of a real state machine where you can skip between branches, you can build a flow that’ll be started repeatedly. A flow that’ll contain e.g. a switch, and depending on the request status it’ll go through one of the branches. Once the branch is finished, update the item with the new status. The infinite trigger loop will start another instance of the flow, this time going through a different path.

Since it’ll be multiple flows instead of one, you’ll have to store the ‘shared’ information somewhere in the list. But that’s a small downside for being able to go back and forth in your flow.

Summary

When building a state machine in Power Automate, you can benefit from the infinite trigger loop – once you update an item, it’ll start another instance of the flow. And since it’s a new instance, it can go any of the available paths, depending on some value. Start the flow, do what’s necessary, and change the status. The flow will run again and do the tasks for this new status.

---

Leave this field empty if you’re human:

The post Building a state machine in Power Automate flow appeared first on Let's POWER Automate.

“There’s no unique ID in my Excel file making it hard to use in Power Automate flow, is there an easy way to add such ID?”

---

When you work with data in rows, let it be SharePoint, Dataverse, or Excel rows, you always need some unique identifier. While Dataverse and SharePoint have such ID by default, Excel does not. And that can be a problem as without ID there’s no way to identify a specific row to select, update, or remove it.

Is there a way to add such unique ID also to Excel rows? Some solution without entering it manually for each new row?

Add a column with a simple formula

One of the functions available in Excel is ROW(): if you use it in a cell it’ll return the row number. That’s the function you can use in your Excel table. Add a new column and set its value to:

=ROW()

Since the tables used in Power Automate must have headers, remove the first rows from the numbering.

=ROW()-1

Expand the formula to the all existing rows to assign the unique ID, the new rows added to the table will get the next ID automatically. Just make sure that you don’t rewrite the value in this cell when creating/updating a row!

Once you have the id you can use it to find/update/delete the row.

Summary

Having a unique ID for each Excel row will make your Power Automate life so much easier. And as you can see, it’s very easy to add such ID, all you need is a simple function in one of the columns. Since all data processed by Power Automate must be in a table, you don’t have to worry about updating the column. Just use the function and each new row will continue with the numbering.

---

Leave this field empty if you’re human:

The post Add unique ID to rows in Excel file for your Power Automate flow appeared first on Let's POWER Automate.

“I have a Power Automate flow that’s adding rows from Excel into SharePoint on regular basis, but it’s so slow, is there a quicker way to find the missing rows?”

---

While Excel file is still a common way to store data, it’s often not the best tool to work with. Once you start building a flow based on the rows, it might get slower and slower until it’s unbearable. That’s why it might be a good idea to move some of the data into SharePoint and do the automation there. But it’s not easy to synchronise Excel with SharePoint list. How do you find the new rows? The rows that were added since the last synchronisation job? Do you compare all the rows with the SharePoint items, one by one?

Don’t use an ‘Apply to each’

While you might be tempted to take all the rows and check if there’s already a SharePoint item, don’t do that. You don’t want a loop over all the rows, it could run for ages.

Use ‘Select’ and ‘Filter array’ instead

It’s much better (and faster) to use the ‘Filter array’ action as when comparing two arrays. The Excel rows and SharePoint items are just two arrays after all.

List all rows in the Excel file and all items in the SharePoint list first.

‘Select’ only the unique values that connect the SharePoint items with the rows.

Add ‘Filter array’ that’ll take all the Excel rows, and filter only the those where the ‘Select’ output (SharePoint unique IDs) does not contain the unique values from the Excel file. Here you might need an expression to get to the column.

And that’s it, the output of ‘Filter array’ will be only the rows that don’t exist in the SharePoint list yet. Loop through these remaining rows and create them in the SharePoint list.

Summary

Comparing two arrays is one of the most time consuming operation if done wrong (using a loop). But if you use my favourite Power Automate actions ‘Select’ and ‘Filter array’ it doesn’t have to be so, and finding Excel rows missing in a SharePoint list is a matter of seconds!

---

Leave this field empty if you’re human:

The post How to find Excel rows missing in SharePoint list (Power Automate) appeared first on Let's POWER Automate.

“I’d like to check whether a user with specific email address exists in our company’s Active Directory, can I use Power Automate to do that?”

---

When you’re working with email addresses, there’s always a difference whether it’s an internal or an external address. If it belongs to a user from your company, you can retrieve all his information from the Active Directory. On the other side, external email address is all you get, there’s no place to learn more about the user. But how do you check whether the user belongs to your company or not? Whether you can get this extra information?

Use ‘Run after’ after the ‘Get user profile’ action

If you know the user email address or his id, you can use the ‘Get user profile’ action. The small problem with this action is that it’ll fail for non-existing users.

But you can take it as a valid result (as when updating files) using the ‘Configure run after’ settings on the following actions.

Set the action to run after the ‘Get user profile’ has failed and define what to do when the user doesn’t exist.

Search for the user with an HTTP request

The second option, if you don’t want to deal with the ‘Run after’ settings, is to use an HTTP request. As explained in the previous article, you can search for a user using more properties than just his email or id. The HTTP request won’t fail for non-existing users, it’ll return either the user or an empty array.

Method: GET

Uri:
https://graph.microsoft.com/v1.0/users?$filter=<property> eq '<value>'

Check whether the output is empty (user doesn’t exist) or not (user exists), and continue accordingly.

empty(body('Send_an_HTTP_request')?['value'])

Summary

You’ve got two options how to check whether a user exists using Power Automate – a dedicated action or an http request. If you use the dedicated action, you’ll have all the information available as dynamic content, but you must use the not so common ‘run after’ settings. The HTTP request evaluation is simpler with a condition, on the other side you must extract the user information from the response JSON.

---

Leave this field empty if you’re human:

The post Check whether user exists in AAD with Power Automate appeared first on Let's POWER Automate.

“Is it possible to not only change colour in a specific cell, but to highlight the whole SharePoint row when using calculated date field?”

---

There’s an older article explaining how to use JSON formatting to do calculations with today’s date and a post on formatting the column based on the result. But what if you don’t want to highlight only the column, but the whole row? To show the whole item in red for low numbers and green for high numbers? How do you achieve such formatting?

Start with the View format

SharePoint allows you not only to format column, but you can format the whole view. Start from any column in the list to get to the view formatting.

It’ll show you two options – ‘Alternating row styles’ and ‘Conditional formatting’. Use the latter one, the conditional formatting and click on ‘Manage rules’.

Prepare a dummy rule for every option you want to use. For example, if you want 3 different colours based on the number of days, create 3 rules using some dummy conditions.

Once you have a condition for all the colours you want to use, switch to the ‘Advanced mode’ and get the JSON formatting.

Modify the conditions in the JSON

Copy paste it into some code editor (I always recommend Visual Studio Code), it’ll look as below. You can see that it contains always the condition (red) and the format it should apply (green).

Since all the conditions were “dummy” conditions, it’s now up to you to replace them. Take the calculation from the original article and use it to calculate the date difference.

=floor((Number([$<dateColumnName>])-Number(@now))/(1000*60*60*24)

Note: <…> is a placeholder, replace it including the < and >.

Replace also the operands and the value to compare it with, e.g.

Use the updated JSON

Copy/paste the updated code back into the View formatting Advanced mode. Click ‘Save’ and you’re done, you just formatted the whole SharePoint row based on the calculated date difference.

Summary

It’s possible to highlight the whole SharePoint row based on a date in a calculated column, and it’s not even that complicated. If you use the SharePoint formatting designer it’ll do most of the work for you, all that’s left is to replace the conditions with the calculation.

---

Leave this field empty if you’re human:

The post How to highlight whole SharePoint row based on calculated date appeared first on Let's POWER Automate.

“The flow takes tens of minutes, even hours to complete, why is Power Automate so terribly slow? Can I do something about it?”

---

When you run a flow, it always takes a while until the flow completes. It can be a few seconds for the simple flows or many days for flows including some manual task. And then there’re all the flows in between, running tens of seconds, minutes, hours… some of them running much longer than you’d wish/expect. Why does that happen? What’re the most common causes of a slow flow?

It’s mainly about the ‘Apply to each’

If your flow is running really slow, take a look on what loops it contains. The more ‘Apply to each’ actions, especially ‘Apply to each’ inside another ‘Apply to each’, the slower the flow.

Loop to select a specific value

One example I’ve recently seen is a flow designed as below. The goal is to take all items, and from each item select a specific value and store it in a variable.

The more items there is, the more loops it’ll contain, and the longer will the flow run. It’s always better and many times faster to use the ‘Select’ action instead of such ‘Apply to each’ and ‘Condition’.

Loop to process only some of the items

Another example of a useless loop is to process only specific items. For example, send an email for items where some date is today.

If you use a ‘Condition’ inside ‘Apply to each’ to filter the items, you’ll end up processing all the items. All items in the specific data source just to find a few of them: always use filters instead of these loops.

It can be a filter directly in an action, e.g. in ‘Get items’ or ‘List rows…’ or any other action with such possibility.

If there’s no such option there’s still the ‘Filter array’ action to filter the items/rows/anything later.

Loop to create / delete many items

The last type of loop that can be improved is when it just has a lot of work to do. It’s not selecting anything, it’s not possible to filter, but it needs to repeat some action many times. For example, create or delete thousands of items.

Here I’ll redirect you to my friend Paul’s blog where he published nice articles on fast creation of SharePoint items and fast deletion of items from SharePoint or Dataverse.

This is a bit more complicated than the two points before, but at the same time it’s not that common. I’m mentioning it mainly to show you that there’re also other options to reduce the number of loops.

Summary

If you feel that your Power Automate flow is running too slow, it’s very often due to some ‘Apply to each’. The more items you send in a loop, the longer it’ll take to process them, in some cases even hours. Most of such flows can be improved following just the first two concepts – ‘Select’ to select some value(s) for further processing and filter whenever possible. You’ll still have some ‘Apply to each’ in the flow, but that’s fine as long as you don’t send more items than necessary inside. A few ‘Select’s and a few ‘Filter array’s and your flow can run 8 minutes instead of 20 hours…

---

Leave this field empty if you’re human:

The post Why is your Power Automate flow running so slow? appeared first on Let's POWER Automate.

“The Power Automate approval action gives me only one field for a link, what if I need to add multiple links to the task?”

---

If you use the standard Approvals feature in Power Automate to handle approval tasks, you’ll notice that it has some specifics. The task attachments have a different format than email attachments, you can’t use html in the description, and the link to item field will allow you to add just a single link. But what if there’re more links related to the task? If you want to provide more information to the approvers?

Add the other links to the description

Since the action won’t let you add multiple links in a field, you’ll have to add them to the task description. Yet again, you can’t use html code for the hyperlink as it doesn’t support html, you must use the markdown language.

As you can see in the linked documentation, hyperlinks use the following format.

[<linkText>](<linkUrl>)

Note: <…> are placeholders, replace them including the < and >.

It’s a plain text, no expression, and it can look as below, one link one line:

[LPA Blog](https://tomriha.com)

That’s it, you just added another hyperlink in the approval task. It’ll be in a different place than the standard link, but it’ll be there. And since it’s in the description there’s no limit on how many links you can add.

Summary

The standard approvals functionality in Power Automate has some specifics given its different language, but once you learn the basics you can add so much more in the task description, even multiple links.

If you use approval tasks I’d recommend checking the whole Microsoft documentation to learn more about all the possibilities.

---

Leave this field empty if you’re human:

The post How to add multiple links to the Power Automate approval task appeared first on Let's POWER Automate.

“I’d like to organise the SharePoint navigation a bit, can I use Power Automate to create a sublink under a main link?”

---

When you use Power Automate to create new SharePoint lists, you want to make them easily accessible to the users. That often means adding them to the left side navigation. But in the original article it’s always creating the top level link. What if there’re too many lists making the navigation confusing? Or if there’re some related lists that would be nice to keep together? The solution might be creation of a sublink, and this article will show you how.

Get the main link Id first

Before you can create a sublink, you must first know where to place it, under which main link. Use the ‘Send an HTTP request to SharePoint’ action to list all existing links.

Method: GET

Uri:
_api/Web/Navigation/QuickLaunch

Such request will return a JSON with all the navigation links on the specific site. Extract the array with the results from the JSON and put it into ‘Filter array’. You don’t need all the links, you want only the one that should serve as the “parent” for your sublink, e.g. with a specific title.

From: body('Send_an_HTTP_request_to_SharePoint')?['d']?['results']

Filter: item()?['Title']

If the link name is unique (and it should be unique if you don’t want to confuse users), it’ll return exactly 1 result. Use the expression below to extract the navigation node id…

first(body('Filter_array'))?['Id']

…to put it in another HTTP request to SharePoint. Now, when you have the main link id, you can add a sublink.

Method: POST

Uri:
_api/Web/Navigation/GetNodeById(navigationNodeId>)/Children

Headers:
{
  "Accept": "application/json;odata=verbose",
  "Content-Type": "application/json;odata=verbose"
}

Body:
{
    "__metadata": {
        "type": "SP.NavigationNode"
    },
    "Title": "<linkTitle>",
    "Url": "<url>"
}

Note: <…> are placeholders, replace them including the < and >.

Summary

If you’d like to add a sublink to a SharePoint navigation using Power Automate, you must tell it where to place it first. Get the id of the parent link, and use it to create the new one. Just the two HTTP requests and a ‘Filter array’ as shown above and you’re done.

---

Leave this field empty if you’re human:

The post Add sublink to SharePoint navigation with Power Automate appeared first on Let's POWER Automate.

“Can I use Power Automate to remove user from all private channels in a Team, even if I don’t know whether he’s a member?”

---

Teams private channels are not the easiest thing to manage. Unlike the Office 365 groups connected to Teams, members of private channel are much more hidden. If you want to remove them, you must go channel by channel. Or you can build a flow that’ll do it for you. A single flow to go through all channels in a team and remove the specific user (if found).

You’ll need a registered app in Azure

Since such operation is not possible using the standard actions, you’ll need an HTTP request. A request sent by the premium HTTP action to Graph API that’ll do the work for you. But to make it a bit more complicated, you’ll need a registered app in Azure first as explained in the previous article.

Follow the same steps as in the previous article until you reach the ‘List channels’ action.

For this article you’re interested only in the private channels – filter them out using the ‘Filter array’ action.

@equals(item()?['membershipType'], 'private')

Now, with only the private channels, you can start adding the HTTP requests. Firstly, authenticate using the registered application (the url and content are in the previous article).

Loop through all the private channels and get their members.

Method: GET

Uri:
https://graph.microsoft.com/v1.0/teams/@{items('Apply_to_each')?['id']}/channels/@{items('Apply_to_each_2')?['id']}/members

Headers:
{
  "Content-Type": "application/json",
  "Authorization": "Bearer @{body('HTTP_-_Authenticate')?['access_token']}"
}

Using the ‘Filter array’ check whether the user is among the team members – filter only users with the specific email. That should give you 0 to 1 results.

Add another ‘Apply to each’ and use the filter outcome as the input. The benefit of such solution is that you don’t need to check whether there’re any results. If the user is among the members, the loop will run once. If he’s not in there, the loop will skip all the actions inside and do nothing.

All that’s let if to remove the user from the channels.

Method: DELETE

Uri:
https://graph.microsoft.com/v1.0/teams/@{items('Apply_to_each')?['id']}/channels/@{items('Apply_to_each_2')?['id']}/members/@{item()?['id']}

Headers:
{
  "Content-Type": "application/json",
  "Authorization": "Bearer @{body('HTTP_-_Authenticate')?['access_token']}"
}

The whole flow diagram

Summary

As you can see, the Power Automate flow that’ll go through all private channels and remove a specific member isn’t that complicated. Get the team, list the channels, check if the user is among the members and if is, remove him. The most complicated part are the HTTP requests to Graph API and the app registration, but if you follow the steps in this and the previous article you should be fine.

---

Leave this field empty if you’re human:

The post Remove member from Teams private channels in Power Automate appeared first on Let's POWER Automate.

“I’d like to manage Teams private channels from a single place, can I build a Power Automate flow that’ll add a new member to such channel?”

---

Most of the time when you deal with members of a Teams channel, you work with members of an Office 365 group. It you add a user to the group, they’ll have access to the team and all the channels. If you remove the user, he’ll lose it. But what about private channels that are not connected to any group? How do you add a new member to a private channel when Power Automate doesn’t give you any dedicated action?

Important note: the solution uses generic HTTP request that’s part of the premium Power Automate license!

Register an application in Azure

Since there’s no dedicated action, and the existing HTTP actions don’t have the necessary permissions, you’ll have to register an application in Azure that’ll provide them.

Go to the Azure portal and register a new application.

Let’s call it for example “ManagePrivateChannelMembers” and click the ‘Register’ button to create it.

Once created, go to ‘API permissions’ and ‘Add a permission’. This is the step where you define what you’ll be able to do when using this application for authentication.

The permissions you need to manage members of private Teams channels are Microsoft Graph -> Delegated permissions -> ChannelMember.ReadWrite.All. Check the checkbox and add the permissions.

Grant the admin consent to allow use of the permission level in the flow.

Now the application knows that it can use this permission level. Go to the ‘Certificates & secrets’ next to get the “password” to the application.

Add some description (not that important) and create the secret. Make sure to copy the Value somewhere safe, it’ll be accessible only right after the creation.

Additionally to the secret you’ll need also the ‘Application (client) ID’ and ‘Directory (tenant) ID’. Take also these two values and store them with the secret, you’ll need them all later in the flow.

The application is ready, now you can move on to building the flow.

Prepare the flow

Use the trigger of your choice, in my case it’s a manually started flow. In the first step add a ‘Compose’ action and store all the configuration from the application you just registered – tenantId, applicationId, applicationSecret, plus userName of the user running the flow, and his password.

Use a JSON object format to make it easy to reference the values later.

{
  "tenantId": "xx",
  "applicationId": "xx",
  "applicationSecret": "xx",
  "userName": "xx",
  "userPassword": "xx"
}

Since you’re adding user to a Teams channel, you’ll need the Team name and the Channel name…

…and the ID of the user you’re trying to add.

List all Teams and filter only the one with the desired name.

Since there should be only 1 Team with the name, you don’t care about the ‘Apply to each’ added while listing all the team channels.

Again, filter only the channel that you’re interested in to get its ID.

Once you have the ID of the Team, ID of the private channel, and the registered application, you can do the actual addition.

Send the HTTP requests

Firstly, you must authenticate to the application you created with an HTTP request. This first request will give you an access token, a validation that you’re allowed to use the registered application.

Method: POST

Uri:
https://login.microsoftonline.com/<tenantId>/oauth2/token

Headers:
{
  "Content-Type": "application/x-www-form-urlencoded"
}

Body:
grant_type=password&resource=https://graph.microsoft.com&client_id=<applicationId>&username=<userEmail>&password=<userPassword>&client_secret=<applicationSecret>

Note: everything inside <…> is a placeholder, it must be replaced including the < and >.

In the example code below I’m using the configuration in the ‘Compose – config’ action created at the beginning.

Method: POST

Uri:
https://login.microsoftonline.com/@{outputs('Compose_-_config')?['tenantId']}/oauth2/token

Headers:
{
  "Content-Type": "application/x-www-form-urlencoded"
}

Body:
grant_type=password&resource=https://graph.microsoft.com&client_id=@{outputs('Compose_-_config')?['applicationId']}&username=@{outputs('Compose_-_config')?['userName']}&password=@{outputs('Compose_-_config')?['userPassword']}&client_secret=@{outputs('Compose_-_config')?['applicationSecret']}

Using the access token you can finally add the new member to the private channel with another HTTP request.

Method: POST

Uri:
https://graph.microsoft.com/v1.0/teams/<teamId>/channels/<channelId>/members

Headers:
{
  "Content-Type": "application/json",
  "Authorization": "Bearer <authenticationToken>"
}

Body:
{
  "@odata.type": "#microsoft.graph.aadUserConversationMember",
  "roles": [
    "<userRole>"
  ],
  "user@odata.bind": "https://graph.microsoft.com/v1.0/users('<userId>')"
}

Note: everything inside <…> is a placeholder, it must be replaced including the < and >.

Replacing all the placeholders with the dynamic contents from the flow it might look like this:

Method: POST

Uri:
https://graph.microsoft.com/v1.0/teams/@{items('Apply_to_each')?['id']}/channels/@{items('Apply_to_each_2')?['id']}/members

Headers:
{
  "Content-Type": "application/json",
  "Authorization": "Bearer @{body('HTTP_-_Authenticate')?['access_token']}"
}

Body:
{
  "@odata.type": "#microsoft.graph.aadUserConversationMember",
  "roles": [
    "member"
  ],
  "user@odata.bind": "https://graph.microsoft.com/v1.0/users('@{outputs('Get_user_profile_(V2)')?['body/id']}')"
}

Note: you can use the values member, owner, or guest for the user role.

And that’s it, you just added a new member/owner/guest to a Teams private channel. Just don’t forget that you should add the user also to the related Office 365 group to give him access to the team itself.

The whole flow diagram

Summary

This post turned out to be much more complicated than I originally expected. On the other side it’s packed with new information…

Registering an Azure application is something you might need to do from time to time, not only to add member to a Teams private channel, but for a lot of the more complicated stuff in Power Automate. Once you learn how to register them and how to authenticate towards them, your possibilities will move to a different level with the Graph API.

---

Leave this field empty if you’re human:

The post Add member to Teams private channel with Power Automate appeared first on Let's POWER Automate.

“I’d like to send a link to the whole folder with the newly created file, how can I get such link with Power Automate?”

---

If you trigger flow on a new file in a SharePoint library, you probably send a link to this file in some message, whether it’s a Teams message or an email. The link is hidden under the ‘Link to item’ dynamic content and that’s all you need. Just one dynamic content. But what if it’s not just about the single file? If the users should know about all the other files in the same folder? How do you build a link like that?

Start with the Folder path

There’re two dynamic contents related to the file location – Full Path and Folder path.

Full Path will give you the path to the file on the specific site, including the file name, e.g.

Shared Documents/PDF files/Subfolder/file.pdf

Folder path will give you the same, excluding the file name, e.g.

Shared Documents/PDF files/Subfolder/

…and that’s where you want to start. But it’s only the start as you can see that it’s missing the url of the SharePoint site.

Add the site url

Since all flows are bound to a SharePoint site, you can hardcode that site in the url. Use the concat(…) expression to combine it with the folder path.

concat('https://xxx.sharepoint.com/sites/siteName/', triggerOutputs()?['body/{Path}'])

Replace the spaces

Depending on your usage of the link there might be one more thing to do. If you send it as a plain link, it’ll end the url with the first space.

To turn it into a whole link you must get rid of the spaces. Add one more expression around the concat(…) to replace(…) each space with the %20 character (an url representation of a space).

replace(concat('https://xxx.sharepoint.com/sites/siteName/', triggerOutputs()?['body/{Path}']),' ', '%20')

Once you replace the spaces you’ll get a fully working (and clickable) url.

Summary

While Power Automate doesn’t give you a direct link to a folder with a specific file, you can build it yourself. Take the SharePoint site url that’s fixed, add the folder path, and replace the spaces. That’s it, you just built a direct link to a folder with that specific file.

---

Leave this field empty if you’re human:

The post Get link to a folder of a newly created file with Power Automate appeared first on Let's POWER Automate.

“I’d like to use the if(…) expression but I don’t know how to create the condition, is there some trick in the Power Automate designer?”

---

Recently I published an article on replacing the ‘Condition’ actions with if(…) expressions to simplify a flow. Instead of a bunch of conditions and repeating actions you can have just a single action with if(…) expressions. But creating the right condition inside if(…) is a bit more complicated than using the standard action. How do you “rewrite” the condition? What should you “type in” as the first parameter of the expression?

Use the ‘Filter array’ action to create the condition

As with the trigger conditions, you can use a small trick and let the designer create the condition for you. Add the ‘Filter array’ action into your flow and recreate the condition you want to replace with the if(…) expression.

Click on ‘Edit in advanced mode’ to transform the condition inside ‘Filter array’ into text.

Remove the @ at the beginning and take the rest, that’s the condition to use as the first parameter in if(…).

In this example to check whether a value is less than 3 it’d look as below.

if(less(outputs('Compose_-_Value'), 3), <ifTrue>, <ifFalse>)

Repeat the same process for all the ‘Condition’ actions you want to replace and build the whole expression step by step.

Summary

It might be a bit confusing when trying to replace the Power Automate ‘Condition’ with the if(…) expression, but it’s not that hard. If you use the workaround with the ‘Filter array’ action you don’t need to write the whole condition manually. Recreate the condition 1:1 in that action, switch to the text mode, remove the @, and the condition is ready. You can even combine multiple conditions together using this approach, just build it slowly, condition after condition.

---

Leave this field empty if you’re human:

The post Create easily the condition for Power Automate if(…) expression appeared first on Let's POWER Automate.

“I need to find a user by his job title but Power Automate keeps asking for email or id, how can I use his role property instead?”

---

When you’re looking for more information about a user, you probably use the ‘Get user profile’ action. You input the user email or his id, and it’ll return his whole user profile. But what if you don’t have his email nor user id? What if you’ve got e.g. his job title, or a unique user code in your organisation? How do you find such user?

Use an HTTP request via Graph API

Since the default action won’t help (unless you want to get all users and then filter with the ‘Filter array’ action) you’ll have to look elsewhere. And the place to look is an HTTP request.

You’re looking for a user, meaning the endpoint to call is /users.

Method: GET

Uri:
https://graph.microsoft.com/v1.0/users

But such request would give you all the users, you don’t want that. You want only the users that fit a condition, a filter in the format below:

https://graph.microsoft.com/v1.0/users?$filter=<property> eq '<value>'

While you might be tempted to use this request in the Office 365 Users action ‘Send an HTTP request’, don’t. At the time of this article it’s not working.

Use the ‘Send an HTTP request’ action from the Office 365 Groups instead.

The response will be a JSON with all the users fitting the condition where you can extract the information directly with an expression.

Summary

When an action won’t help, use an HTTP request. While Power Automate has an action to get user profile, you can’t use it to find users using a different property than email or id. Luckily there’s a workaround by adding the filter directly into a request to Graph API. Add the $filter parameter, pick which property it should search in, and the value to look for.

---

Leave this field empty if you’re human:

The post Find user by other property than email or id with Power Automate appeared first on Let's POWER Automate.

“I’d like to use a different colour for states of requests in a SharePoint column – green/red/blue for the Approved/Rejected/In progress value.”

---

You could say that SharePoint has everything a solution needs by default as you can define the data format, what the forms will look like, or how should the data be displayed to users. While that can be enough, there’s always a bit more you can do.

For example adding a small piece of JSON code to improve the user experience, to make the whole solution more appealing. A button to allow users start a flow on a single click. A history of your approval flow in a summary or in a separate list. Or just use a different colour depending on a value in a column.

Take the format from a choice column

If you’d like to apply the formatting from scratch, there’s already an article on that topic. But for this one let’s make it easier (and prettier).

You probably noticed that the SharePoint modern UI automatically offers you some graphic options for choice columns. When creating such column, you can define the values and assign a specific colour to each of them.

What it does on the background is a creation of a JSON code responsible for the formatting you selected. You can see it for yourself if you ‘Format the column’ again and switch to the ‘Advanced mode’.

Since it’s just a JSON formatting on the background, you can take it from there and apply it to any other column. All you have to do is to take the JSON and replace the values used in the “donor” choice column (if needed). Copy/paste the JSON code into another column formatting and it’ll take it for its own.

Summary

As you can see you don’t have to always start from scratch, you can take what’s already in there and just reuse it. In this case it is the SharePoint column formatting – choice column already has it, why not borrow it and change value colour also in the single line of text field…?

---

Leave this field empty if you’re human:

The post Change colour of a SharePoint column depending on its value appeared first on Let's POWER Automate.

“I already built a bunch of flows and now it’s hard to remember what they’re doing, what’s the best way to document a Power Automate flow?”

---

When you build a flow, it’s a good idea to also document it. It’s much easier to remember what each action is doing if it’s not just a bunch of the default actions with a number at the end. Especially if you’re using some complex filters or expressions. And it’s not only for you, it’s also for the others who might use or modify it in the future. How do you make your flows more “future-proof”? What’re the options for documenting a flow?

Rename the action

The most easily visible approach is to rename the actions by adding a few more words about what’s going on.

Does the ‘Compose’ action calculate a difference between times? Put it in the name. Does the ‘Get items’ filter only items that were due yesterday? Add a few words in the name.

Note: When renaming I’d recommend always keeping also the original action name! Not everyone will recognise what the action icon means and what action with the icon did you really use!

While it’s the easiest way to read a flow later, it has also a few limitations. Firstly, you should keep the description in a few words and some characters are forbidden from using. Secondly, you should decide for this way of description at the beginning of building the flow. While it’ll automatically reconnect the action outputs when used later, it won’t reconnect if you use them in an expression. For example, if you’re using expressions to extract value from a JSON or to format a date, renaming the action will break them.

Add a note to the action

Another option is to add notes to the action.

It’s less visible than renaming the action as you must expand it to see the notes, but on the other side it has a lot less limitations than the action name. You can write the description in more detail and also store the expressions directly in the note for backup/easier read.

If, for some reason, somebody deletes or modifies the expression, you still have a backup in the note (unless you change it).

Add a comment to the action

The last option for this article are comments. It’s the same functionality you use in the Office applications – you add a comment, somebody reacts, etc.

The comments have a nice feature – if you add a comment to an action, it’ll expand the flow to the given action upon click. For example, if I add a comment the ‘Send an email’ action deep in the flow…

…once I open the flow again and click on the comment…

… it’ll expand the flow up to the action.

The problem with comments is that they’re not stored in the flow. If you create a copy of a flow, or move it to another environment, you’ll lose the comments. They seem to be dependent on the environment so don’t bother with them if you’re not in the flow’s final destination…

Summary

When you build a Power Automate flow, you should always use some way to document it. While it might be clear to you what you’re doing, it might be different in a year from today and a few notes will help you to remember. The more notes you create now, the happier you’ll be later.

This article showed you three approaches to flow documentation, where I personally use the combination of the action renaming and notes. The only time I used comments was to make it easier for other users to find the email actions for potential changes in the wording.

---

Leave this field empty if you’re human:

The post How to document your Power Automate flow appeared first on Let's POWER Automate.