Top

Locked file checking pattern in Power Automate


In SharePoint Online, whenever you create a library and turn on “Require check-out” before editing documents option or not, users are able to open files for collaboration in browsers or in their local Office clients. When they do that, the file is getting locked from access that could lead to its metadata changes. If you create Power Automate flow which purpose is to update file’s metadata or approval status it will end up with failure when trying to do so.

The reason?

This situation can happen because of multiple scenarios:

  • Someone uploads a file and immediately opens it in browser;
  • Someone starts a workflow on the file and then someone else opens it to check contents;
  • Someone gets task to review the task, opens it but forgets to close before completing the task
  • And many more…

The general reason is – the file is locked for shared use at the time flow is trying to update it’s metadata and gets 400 (Bad request) error with details:

The file "file absolute url" is locked for shared use by admin@tenant.onmicrosoft.com.

There is yet no oob solution inside Power Automate, that could be use to help flow check if the file is locked or not. But, with the help from “Run after” configuration and a small trick, you are able to build a solution.

Locked file checking pattern in Power Automate

The solution I am using and would like to share with you is pretty simple. You need to build a do until loop and a variable:

  1. Initialize boolean variable and set its value to “false”
  2. Add “Do until” loop and set the condition to: @equals(variables('YOUR VARIABLE'), true). This means, the loop will run as long as the variable’s value is not set to “true”.
  3. Next add the action that will fail if the file is locked for shared use. Remember, you don’t need to repeat the same pattern if you have more actions updating metadata of the file in your flow. Do this just for the first one in a sequence of these actions.
  4. Next create parallel branch. In one branch add “Delay” action. Set delay time to eg. 5 minutes. Also, configure it’s “Run after” configuration, so that it is executed only if action from step 3 fails:
    1. A. Click the ellipsis icon
    2. B. Then “Configure run after”
    3. C. In the form that appears select only option “has failed” and “Done”.
  5. In the second branch add “Set variable” action and set value of your boolean variable to “true”. This action will only be executed, if setting properties in action no. 3 completes successfully.

And so, this is how it works. The loop will make turns as long as the file is locked for shared use, so as long as the action fails. Once the lock is released and action is able to update its metadata, boolean variable is set to true, loop ends and workflow continues.

If you have any questions regarding this pattern, please let me know in comments!

Extras

Instead of trying to write to the file and this way checking if it is locked for shared use, you can as well try the method described by Stefan Strube here: Create flows with Power Automate to update Title field in libraries of newly created SharePoint site automatically – There’s Something About Dynamics 365 (2die4it.com). Use the “lockedByUser” endpoint: _api/web/GetFileByServerRelativeUrl('URL')/lockedByUser to get details about the lock on the file:

You can then use “Parse JSON” action to get details such as: LoginName, UserPrincipalName, Mail, if user is guest or site admin etc… Having this information you can as well put the flow in loop, until lock is released, however knowing who created the lock you can as well email them with request to close the file on their side.


Tomasz Poszytek

Hi, I am Tomasz. I am expert in the field of process automation and business solutions' building using Power Platform. I am Microsoft MVP and Nintex vTE.

10 Comments
  • StefanS365

    Hi Tomasz,
    It’s possible to call the SharePoint REST API to check whether a file is locked or checked-out by a user.
    See Addendum at my blog post:
    https://2die4it.com/2020/06/11/create-flows-with-power-automate-to-update-title-field-in-libraries-of-newly-created-sharepoint-site-automatically/
    REST API endpoints: lockedByUser, checkedOutByUser
    Greetings,
    Stefan

    February 20, 2021 at 3:21 pm Reply
    • Tomasz Poszytek

      Thanks Stefan. It’s indeed valuable. I will update the post to include that information too. I decided however to use that approach because this way I do one call to SPO instead of two. So if update doesn’t work, I know file is locked. That was my idea 🙂

      February 21, 2021 at 4:00 pm Reply
  • Beth Beck

    Hi there. I’m trying to use this on a document in a document library that keeps getting locked. My Flow trigger is “For a selected file.” I then follow that with “Get file properties” actions. In my SharePoint http request, I’m having trouble formatting the Uri. I’ve tried multiple values from the dymanic content in my Flow actions. Here’s what I intitially tried, but I didn’t get much from the ‘body’ for the Parse JSON.

    _api/web/GetFileByServerRelativeUrl(‘/sites//sourcepolicies/@{triggerBody()?[‘entity’]?[‘fileName’]}’)/$lockedByUser
    triggerBody > entity > fileName is fileName property under “For a selected item”

    Also tried:
    _api/web/GetFileByServerRelativeUrl(‘/sites//sourcepolicies/NAME”)/$lockedByUser
    Name property under “Get file properties”

    I’ll freely admit I’m not proficient with REST. I keep getting this error no matter what I’ve tried.

    The file /sites//sourcepolicies/1201 Contract AdministrationTEST does not exist.
    clientRequestId: 33c8b677-51f0-457f-81f9-b9db94aa5456
    serviceRequestId: b914b19f-a086-b000-be27-de550e82e266

    March 4, 2021 at 10:32 pm Reply
    • Tomasz Poszytek

      Hi, you need to very carefully build that ServerRelativeURL. There are at least two issues I see – double slashes and no file extension. Instead of: “/sites//sourcepolicies/1201 Contract AdministrationTEST” you should use: “/sites/sourcepolicies/1201 Contract AdministrationTEST.docx” for example. Look at what’s within the header of the trigger action, not the body. There should be a property with name+extension.

      March 5, 2021 at 9:59 am Reply
  • Beth Beck

    Hello,
    Thank you for responding so quickly. You’re correct in that I had a double slashes and no file extension. I think the double slash was a copy/paste mistake I made when composing my reply. This morning, I checked the tigger action header: ‘For a Selected file’. It doesn’t provide much for details, but the next action “Get File Properties” gives me more. I used “{FilenameWithExtension}”: “1201 Contract AdministrationTEST.docx”, which gives me what I need. I am almost certain I had tried that before writing my original reply to your post as I was getting errors like this one below. but I could be wrong. Or maybe something was cached in the Flow or browser.

    The file “https:///sites//sourcepolicies/1201 Contract AdministrationTEST.docx” is locked for shared use by beth_beck@.com.
    clientRequestId: 2e2f3645-2c90-42f9-82e2-1326df7527eb
    serviceRequestId: 2e2f3645-2c90-42f9-82e2-1326df7527eb

    March 8, 2021 at 5:04 pm Reply
  • Stephan Jola

    Hi Tomasz, this feature is especially interesting when using the Power Automate Excel Online (Business) https://docs.microsoft.com/en-us/Connectors/excelonlinebusiness/#known-issues-and-limitations where the Excel File can be locked up to 6 Minutes… For example when add many rows to an Excel Table and the Add Rows is running asynchronously.

    If performance is the key in Excel Automation, use MS Graph APO, but this is a different story 😉

    May 4, 2021 at 8:00 pm Reply
    • Tomasz Poszytek

      Or in approval workflows, when file is uploaded and then reviewer/ approver tries to complete their tasks while having the file opened. Yes, there are many use cases. All the best Stephan 🙂

      May 12, 2021 at 8:18 am Reply
  • Lionel

    “lockedByUser” endpoint: _api/web/GetFileByServerRelativeUrl(‘URL’)/lockedByUser is not returning the correct user when the document is edited by multiple users. The worst case , the request is returning always the first user lock even the user closed the document and the others still editing the document.

    February 16, 2022 at 6:47 pm Reply
    • Tomasz Poszytek

      Oh, I didn’t know that. I thought it is returning an array of objects. Sorry to hear that.

      February 18, 2022 at 2:35 pm Reply

Post a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.