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

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:

https://culmsee.sharepoint.com/_vti_bin/ListData.svc/Documents?filter=Name 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.

https://culmsee.sharepoint.com/_api/SP.APIHubConnector.GetListItems(listName=’Documents’,queryOptions=@q)?@q=%27%2524filter%3dFileLeafRef%2520eq%2520%252somefile.jpg%2527%26%25%27

(and decoded to make it a bit more readable…)

https://culmsee.sharepoint.com/_api/SP.APIHubConnector.GetListItems(listName=’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

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

www.hereticsguidebooks.com