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

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

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

“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 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’d like to keep tracking of the time I spend on various tasks, is there maybe a way to build such solution using Power Automate flow?”

---

One of the goals for using Power Automate is to make your life easier. To remove repetitive tasks, or at least make them as easy as possible. Instead of doing a series of steps you can just start a flow and let it do all the work for you. One example could be the tracking of your work time. Instead of checking the time, typing it down, doing the work, checking the time again, typing it down again, and calculating the duration you can build a simple flow that’ll do it on a click of a button.

Prepare the data storage

Since you must store the information somewhere, the first step is to prepare the data storage. You can use an Excel file or a SharePoint list, but in both cases it’ll need at least 4 columns – start time, end time, duration, and description.

In this example we’ll use SharePoint list, rename the Title column to “Description” and add two Date and Time columns “Start” and “End”, and a number column “Duration”.

Build the start flow

The starting flow is very straightforward as all you need is to create a new item in the list with the start date and time. Create it as a manually started flow with a single action ‘Create item’.

Build the end flow

The end flow is a bit more complicated as it must do a few more steps. It must find the last open item and update the end date. It must also calculate the task duration, the difference between the two dates/times, and the task description.

Start again from the manual trigger, but this time add a manual input “Description”.

Search for the last item using ‘Get items’ action with the Order By and Top Count fields.

That’s the item to update, the one that contains the start time. Use it to calculate the difference between the start time and the end time – utcNow(). Minutes will be a good unit for the result.

div(sub(ticks(utcNow()),ticks(first(outputs('Get_items')?['body']?['value'])?['Start'])),600000000)

Finish the flow by adding the ‘Update item’ action where you update the end time, duration, and the description you enter when you start the flow.

If you then follow the sequence “StartWork” and “EndWork” you’ll end up with an evidence of your work.

Summary

As you can see, you can use Power Automate to build even some applications, e.g. to keep tracking your time. Instead of doing some work you can just press a button, which is even better if you use the mobile application. Click one button to start tracking the time, click another one to stop it, get the duration, and log the work information.

---

Leave this field empty if you’re human:

The post Building a time tracking solution using Power Automate flow appeared first on Let's POWER Automate.

“I can’t run my Power Automate flow in the item context menu, could the reason be that it’s on a non-default environment?”

---

When you use the ‘For selected item’ trigger in your flow, you’re very limited by the location of the flow. It must be in the Default environment of your organisation, otherwise it won’t connect to SharePoint, yet it’s not wise to have all the flows in the default environment. Is there a way to bypass this limitation? To manually start flows on a selected item even from non-default environments?

You’ll have to use a different trigger

There’s no way to add such flow to the list context menu. But if you combine a few pieces of functionality you can achieve the same result.

Add a column and a button to the list

Firstly, add a new column to your list, e.g. Yes/No column called “RunFlow”. This column will tell the flow whether it should start or not.

Since you don’t want users to check this checkbox manually, you should allow them to do it with a button.

Not only can you trigger a flow, you can also update a column using the JSON column formatting. The piece of code to do that is as below.

  "customRowAction": {
    "action": "setValue",
    "actionInput": {
      "<columnInternalName>": "<value>"
    }
  }

In this example, when working with the Yes/No column “RunFlow”, it’ll be:

  "customRowAction": {
    "action": "setValue",
    "actionInput": {
      "RunFlow": 1
    }
  }

Putting the whole JSON code together that’ll also hide the button once the flow is started:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Run flow",
  "style": {
    "display": "=if(@currentField != true,'inherit', 'none')"
  },
  "customRowAction": {
    "action": "setValue",
    "actionInput": {
      "RunFlow": 1
    }
  }
}

Click on such button will set the value in the RunFlow column to Yes. But that’s only the first part, it won’t trigger the flow yet.

Use a different trigger with a trigger condition

The second step is to use the trigger “When an item is created or modified” instead of “For selected item”. Automatically started flows will work fine from any environment, you just have to block it from running on every update. The flow should start only when the “RunFlow” column was set to Yes – a job for a trigger condition.

@equals(triggerOutputs()?['body/RunFlow'], true)

Once implemented, the flow will start only when users click the button.

Summary

If you want to run a manually started Power Automate on a SharePoint item, but the flow is in a non-default environment, you’ll have to use a workaround. A combination of a new column in the list, JSON formatting on that column to create updating button, and a trigger condition will do the trick. From users perspective it won’t make any difference (if you use buttons to start flows), but the flow can be separated in its own environment.

Just don’t forget to set the column value back to No in the flow if users should be able to start the flow repeatedly.

---

Leave this field empty if you’re human:

The post Run ‘For selected item’ flow from non-default environment (Power Automate) appeared first on Let's POWER Automate.

“I added the button to start a Power Automate flow, but users keep pressing it over and over again, how can I hide it after the flow was started?”

---

The previous post explained how to add a button to SharePoint list/library to manually trigger a flow. But such button will be visible all the time and users can click on it over and over again. Multiple clicks = multiple flow runs. That’s why you should extend the JSON code a bit – hide the button after the first click, after the flow was started. And this post will show you how.

It’s also worth mentioning that the solution is not limited to hiding a button. You can use the same approach to hide (not disable) any column value based on another column. But for now let’s continue with the button example.

Hiding a column value

Hiding is done by adding a “style” and “display” properties to the JSON code as in the example with hyperlinks.

  "style": {
    "display": "=if(@currentField == '', 'none', 'inherit')"
  }

There’s a condition checking value of a column, and depending on the result it’ll set the “display” property to ‘none’ (hide) or ‘inherit’ (show).

That means you’ll need an extra column with the information whether a user already started a flow. If you’re building an approval flow you can use a status column, e.g. if ApprovalStatus = ‘Not started’.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Start flow",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\": \"2c5dfcb2-aa4e-4cfd-9baf-2485225b1fa4\"}"
  },
  "style": {
    "display": "=if([$ApprovalStatus] == 'Not started', 'inherit', 'none')"
  }
}

Note: You must always use the column internal name in the JSON code!

If there’s no status to check you can use also a checkbox. With a small adjustment show the button only if the checkbox is not ‘true’.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Start flow",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\": \"2c5dfcb2-aa4e-4cfd-9baf-2485225b1fa4\"}"
  },
  "style": {
    "display": "=if([$FlowDidRun] != true, 'inherit', 'none')"
  }
}

IMPORTANT: you must always check the checkbox or change the status as the first action in the flow! JSON button can do only 1 action, and this one will trigger the flow…

Summary

If you use a button to start Power Automate flow, you should always hide it once the flow is started. You shouldn’t let user run the flow multiple times, not sure whether the flow already runs. Show them the button only when needed and hide it during the first flow run by updating the ‘control’ column.

---

Leave this field empty if you’re human:

The post Hide button in SharePoint list after Power Automate flow started appeared first on Let's POWER Automate.

“I’d like to make triggering a Power Automate flow as simple as possible for the users, is there a way to add e.g. a button to start a flow directly from the list view?”

---

If you use manually started flows, they’re probably started on an item or a document. Users select the item/document, click on the 3 dots -> Automate -> run a flow. But having even these 3 clicks might be confusing for some users, especially if they don’t use flows very often. There must be an easier, more direct way to start a flow!

And there is one, by adding a button directly to the view!

Use JSON formatting to add the button

There’re many possibilities when using JSON to format a SharePoint column. The older posts showed you how to calculate with today’s date or build a hyperlink, this time it’ll show you how to build a button to start a flow.

Since the button will be in a column, it’s a good start to create one, e.g. a Single line of text column ‘StartFlow’.

Click on the column name and navigate through ‘Column settings’ to ‘Format this column’…

…into the ‘Advanced mode’. That’s where all the magic happens.

The JSON below will add the most basic button in that column.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "button",
  "txtContent": "Start flow",
  "customRowAction": {
    "action": "executeFlow",
    "actionParams": "{\"id\": \"2c5dfcb2-aa4e-4cfd-9baf-2485225b1fa4\"}"
  }
}

But there’re two parts to modify, one that’s optional and one that’s mandatory to change.

The optional one is the wording on the button in the “txtContent” parameter. You can replace it with a few words on the specific flow, or keep just “Start flow” in there.

The second part, the one that’s mandatory to change, is the flow id. You must tell the button what flow it should start, and that’s done by the flow unique id.

Open the flow details page and check the url, this id is between the /flows/ and /details part.

Copy/paste it into the JSON and save it.

And that’s it, you just created a button that’ll start a flow on a single click.

Summary

When using manually started flows, you might want to make the process as simple as possible for the users. While it’s possible to start Power Automate flow from the item/document context menu, it’s much easier to add a button to the list. Users go to the list, click a single button, and the flow will do the rest. And since it’s done using JSON formatting, you can extend it by other functionality, e.g. hide it once the flow is started, but that’s a topic for another post.

---

Leave this field empty if you’re human:

The post Add button to start Power Automate flow from SharePoint list appeared first on Let's POWER Automate.

“Can I restrict users from restoring deleted files from the recycle bin, to delete them also from there within Power Automate flow?”

---

If you use SharePoint as a document management system, you get a lot of functionality out of the box. Files are versioned, there’s support for draft/published versions, and nothing is deleted permanently. And that’s great in many situations. But in some specific situations it’s not beneficial, more specifically if you delete files using a flow and users keep restoring them from the recycle bin.

How do you fully delete a file to avoid it?

Delete file from a recycle bin with an HTTP request

As Power Automate doesn’t have any action related to the SharePoint recycle bin, you must use a “workaround”. That means another HTTP requests, or two…

To delete a file from a recycle bin you need the file ID. Not the file name, not the identifier, but the unique id in the recycle bin. This id will be among the outputs of the HTTP request below…

Method:
GET

Uri:
_api/web/RecycleBin?$filter=LeafName eq '<fileNameWithExtension>'&$select=Id

…and you can access it directly with this expression:

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

Once you have the id you can do the actual removal from the recycle bin.

Method:
DELETE

Uri:
_api/web/RecycleBin('<fileUniqueId>')

That’s it, the file is completely gone.

Summary

Recycle bin is another part of SharePoint that doesn’t have a direct support from Power Automate, yet if you delete any file it’ll always end there. If you want to remove it also from the recycle bin, you’ll need two HTTP requests – one to get the file ID and another one to delete the file. Just be careful with the solution as it’s bypassing one of the safety SharePoint features. Once you delete the file it’ll be deleted completely.

---

Leave this field empty if you’re human:

The post Delete document also from Recycle bin with Power Automate appeared first on Let's POWER Automate.

“I’m trying to create a SharePoint item in a dynamic list with Power Automate, but the HTTP request always returns an error message.”

“A type named ‘SP.Data.xxxListItem’ could not be resolved by the model. When a model is available, each type name must resolve to a valid type.”

---

If you use Power Automate to create SharePoint items in various SP sites or lists, e.g. using some configuration list, you probably use an HTTP request. You don’t have to know the site url or the list name in advance, it can be different for each flow run, but it has also a downside. While the ‘Create item’ action makes things very easy as it’s just a bunch of dropdowns and input fields, the HTTP request is a bit more complex. Not only you must define all the fields manually, you must provide also the list schema information. And this post will tell you how.

Where to get SP.Data.<THIS PART>ListItem

As explained in the linked article, before you define the columns, you must define what columns can the item have. That’s done in the “type” part.

{
  "__metadata": {
    "type": "SP.Data.<ListName>ListItem"
  },
  "Column1": "Value1",
  "Column2": "Value2",
  "Column3": "Value3"
}

If you use single words without special characters for the list name, you can just use the name. But if you use more complex names, you’ll have to do some extra work.

The reason is that this name can’t contain any special characters nor spaces (it’s similar as column internal names). But unlike the column names, you can’t take it from the list settings. Yet there’s an HTTP request to use!

Get it manually using a browser

If the list name is always the same, you can take it manually. Just open a new tab in your browser and build an url from the HTTP request.

Replace the placeholders so it’ll have the format as the example below.

https://xxx.sharepoint.com/sites/playground/_api/web/lists/GetByTitle('Cloud%20Funding%20Staging')

Use it as the address and press Enter. You’ll get an output with all the information about the list.

Search for the ‘ListItemEntityTypeFullName’, that’s the whole “type” to use in the HTTP request.

Get it with an HTTP request

If it’s not the same, but can have a different value for each list, you can add another HTTP request to get it in your flow.

Method: 
GET

Uri:
_api/web/lists/GetByTitle('<ListName>')

The expression below will then give you the list entity name…

body('Send_an_HTTP_request_to_SharePoint_2')?['d']?['ListItemEntityTypeFullName']

… that you can use in the HTTP request to create the item.

Summary

If you use complicated list names (anything with spaces or special characters), you’ll almost always have to do a bit more work. The HTTP request to create list item needs the “type” of the “item to be created” which can be a bit different from the list name, but that’s not a blocker. If the list is always the same you can take it only once via browser and save some API calls, or you can use another HTTP request. Once you have the right “type” you can create the item.

---

Leave this field empty if you’re human:

The post List schema for HTTP request to create item in Power Automate appeared first on Let's POWER Automate.

“My organisation still uses SharePoint groups to manage permissions, can I automatically remove user from a group with Power Automate flow?”

---

If you’re working with SharePoint groups, Power Automate won’t give you much help. All the actions expect that you’ll use M365 groups, that the SharePoint groups are a thing of the past. But it’s not that easy to change the whole SharePoint concept to fit such expectation. You’ve been using SP groups for some time and you’ll keep doing so. The only question is… how?

You’ll need HTTP request

If there’s no dedicated action, you need an HTTP request. It was already explained in the previous articles on adding permissions to a SharePoint group and adding user to a SharePoint group, but that was only addition. Now it’s time to do also the opposite – remove users from a group.

To remove user from a group you’ll need two pieces of information – the SharePoint group id (from the first linked article), and the user id. Once you have both the ids, you can add them into an HTTP request sent to SharePoint.

Method: POST

Uri:
_api/web/siteGroups/getById(<groupId>)/users/getById(<userId>)

Header:
{
    "X-HTTP-Method": "DELETE"
}

And that’s it, that’s one less user in the SharePoint group.

Summary

Every Power Automate automation involving SharePoint group must be done via HTTP request, no matter if you want to add, list or remove user(s). In this case it’ll be a combination of three HTTP requests. Firstly, get the SharePoint group id using the group name. Secondly, get the user id using his email. Once you have this information put the final HTTP request together and send it to the SharePoint.

---

Leave this field empty if you’re human:

The post Remove user from a SharePoint group with Power Automate flow appeared first on Let's POWER Automate.

“I want to update just a single Yes/No SharePoint column, can I do it in Power Automate without updating all the mandatory fields?”

---

While finalising the cheat sheet with HTTP requests to SharePoint, I noticed one column type missing its post – the Yes/No field. It’s one of the fields that’s very often updated alone, used as a flag that something happened. For example to let a flow run only once – set the field value to Yes and it’s done, the task was finished, there’s nothing else to do. But how do you do it, how do you update only this one “checkbox”?

Send an HTTP request to SharePoint

It’s the same HTTP request to SharePoint as for the other columns, the only difference being again in the FieldValue.

Method: POST

Uri: _api/web/lists/GetByTitle('<ListName>')/items(<ItemID>)/validateUpdateListItem

Body:
{
    "formValues":[
	{
	    "FieldName": "<FieldToUpdate>",
	    "FieldValue": "<ValueToUpdate>"
	}
    ]
}

While you might be tempted to update it to Yes/No or true/false values, you won’t succeed. It wants numeric values as when filtering by that column – 1 for Yes, 0 for No.

And that’s it, the request above will update the YesNo field to value ‘Yes’.

Summary

When you update a Yes/No SharePoint column using Power Automate, you must always use the right format. In this situation it’s not true/false or yes/no, it’s numeric 1 or 0.

---

Leave this field empty if you’re human:

The post Update single Yes/No SharePoint column with Power Automate appeared first on Let's POWER Automate.

“Can I use Power Automate for daily checks of SharePoint group members, to see if users added somebody who shouldn’t be there?”

---

Even though SharePoint groups should be a thing of the past, many users still use them. Instead of using an AD group with managed access they add users directly to SP groups. Which can be a problem – there might be users who have access to data without you even knowing.

That’s not an ideal situation, yet there’s always a flow to help you!

Get the group members

Since there’s no dedicated action, you’ll need another HTTP request to SharePoint. Similarly as when adding users to a SP group, you must find the group first. But don’t stop after the group name, you want to take it one step further – to the users in that group.

Method: GET

Uri:
_api/web/siteGroups/getByName('<groupName>')/Users

Such request will return list of all members of the group – AD groups and members alike. Since AD groups are fine and you’re interested only in the users, filter them out.

Navigate in the JSON to get an array with the users to use it in the ‘Filter array’ action.

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

While checking the JSON, you might also notice that there’s a difference between AD groups and users – users have a value in the ‘UserId’ parameter. Use it in the filter to remove all groups (where the ‘UserId’ is null).

item()?['UserId'] <is not equal to> null

The result will be only users who were added directly into the SharePoint group. Now it’s up to you how to handle them.

Summary

To keep control over sensitive data you should know who can access them. If they’re stored in SharePoint it means knowing who’re the members of related SharePoint group, and Power Automate can help you with that. Get the users in the SharePoint group, filter out the AD groups as they’re often managed, and keep only the members added directly. In an ideal situation it should never find such users.

---

Leave this field empty if you’re human:

The post List members of a SharePoint group with Power Automate appeared first on Let's POWER Automate.