The (currently) best way to update SharePoint document metadata using Flow

Send to Kindle

Hi all

<update>This method looks to be superseded. Please read this post and only use this method if you have trouble</update>

Anyone new to Flow, who has come from a SharePoint Designer Workflow background, will automatically assume that Flow has out of the box support for not only uploading documents to a SharePoint library, but to set metadata on it. Unfortunately this is not the case. Currently Flow has limitations with SharePoint integration that makes many common real-world scenarios difficult to achieve, including:

  • The SharePoint : Create File action provides no way to update library metadata when uploading a file, nor does it return the item ID of the newly created file
  • The SharePoint: Create Item action provides access to metadata but only works on lists
  • The SharePoint: Update Item action works on document libraries, but requires the item ID of the document to do so. Since Create File does not provide it, we have no reference to the newly created file

Basically this means it’s hard to upload a file to SharePoint via flow and then set metadata on it.

Given this is such a common scenario, I initially used a dodgy workaround. Basically the method was to:

  1. Use Create File action to upload a file
  2. Use the Get Items action to bring back the metadata for the most recently created file in the library
  3. Grab the ID from step 2
  4. Use the Update Item action to set the metadata on the recently uploaded file.

The big problem with this issue is there is no guarantee that step 2 will actually get the file from step 1. For example, if a lot of files were uploaded to a library at once, step 2 would easily grab the wrong file. The implication is this approach has data integrity concerns and does not scale.

This morning I finally found a method that is much better than my dodgy method. The basic approach is the same except step for 2. What we do now is to use the Get Items action to return the item matching a supplied file name, like so…


As you can see above, we are uploading a file to SharePoint and then passing the file name to a filter query of “FileLeafRef eq [Filename]” in the Get Items action.

Now behind the scenes, what blindsided me to this rather obvious approach was the web service that Flow uses to do this. You see, traditionally this would work fine with a REST call in SharePoint: eq ’somefile.jpg’

Note that “Name” can be used to filter results based on file name. But when you look at the debug output of the Get Items action in Flow, you see it hitting a very different endpoint.’Documents’,queryOptions=@q)?@q=%27%2524filter%3dFileLeafRef%2520eq%2520%252somefile.jpg%2527%26%25%27

(and decoded to make it a bit more readable…)’Documents’,queryOptions=@q)?@q=’$filter=Name eq ‘somefile.jpg’

Now if you try this in Flow, you will get an error that column ‘Name’ does not exist. So this web service behaves differently to the SharePoint list web service that I know. Luckily there is a workaround… changing Name to FileLeafRef works Smile

This back in the Get Items action, the filter “FileLeafRef eq ‘[File name]’ will work and always return the correct ID as shown in the sequence below that adds in step 4.


Now note the plural in the action: “Get Items”. That means that by design, it assumes more than 1 result will be returned. Even though we are matching on a unique ID, the implication is that the data will comes back as a JSON array. Flow will pick up on this and automagically add an “Apply to Each” section around your “Update Item” action.

By the way if you are thinking that file name is not unique if there are folders in the document library, fear not because this method also works… In this case we use FileRef (which includes folder path) and compare it to Path, which is returned by the Create Item action…

Hope this helps sometime…

Paul Culmsee

Print Friendly, PDF & Email
 Digg  Facebook  StumbleUpon  Technorati  Slashdot  Twitter  Sphinn  Mixx  Google  DZone 

No Tags

Send to Kindle
Bookmark the permalink.

6 Responses to The (currently) best way to update SharePoint document metadata using Flow

  1. Jo says:

    I have tried this approach but the flow is not working properly with the ‘FileLeafRef’ and throws below error:

    The expression “FileLeafRef eq Test1 – (5)” is not valid.

  2. admin says:

    Did you cut and paste from this blog? If so the quotes will be messed up. Best to retype it…

  3. If you have a library with more than the dread 5000 items, you’ll need to query on an indexed field first. In the case where I’m working right now, I’m doing:
    Modified gt datetime’@{variables(‘TimeStamp’)}’ and FileLeafRef eq ‘@{body(‘Create_file’)?[‘Name’]}’

    and I have:
    * Indexed Modified (we can’t index FileLeafRef (Name))
    * Set the variable TimeStamp to utcNow() just before Create File

    Nothing like 12 steps to do one step’s worth of work!

  4. admin says:

    Marc that needs to be blogged with screenshots… its a great enhancement and really valuable but completely not obvious to newbies

  5. I would love for this whole method for getting this common task done just go away altogether, but I agree it has general use – for now.

  6. admin says:

    Flow team have stated support for getting the ID back from a create file task is coming…

  7. Ryan Matthes says:

    Super helpful entry — thank you! Just wanted to point out that although FileLeafRef works for this purpose, using FileRef to filter by folder path + filename does *not* work, at least in SP Online.

    If anyone wants to test for themselves, I highly recommend exposing the FileLeafRef and FileRef columns in your document library’s view. These columns don’t appear as options when modifying the view from the browser, but you can still select them by adding a few dummy columns to the view, then opening the view in SharePoint Designer, then looking at its code and swapping out the dummy column names for “FileLeafRef” and “FileRef”. Save and refresh your page, and you’ll have the exact values you’ll need for testing. As I said, I found FileLeafRef works, but FileRef does not.

Leave a Reply

Your email address will not be published. Required fields are marked *