Scenario: Fill SharePoint Metadata with Excel File Values on Upload with Power Automate

Scenario: Users want to upload an Excel file to a SharePoint Library, but want to fill in the metadata required for the document in the library with values inside the Excel file itself.

For example, when a business group wants users to fill out an Excel-based form and want them to upload the file to a SharePoint document library, and have the cell values the user filled in inputted as SharePoint library metadata for the file. This is not an uncommon story for users who have relied on older, legacy forms that were never converted and have been in use in field work or did not have an opportunity to convert the experience to InfoPath, Forms, or another online form technology.

Design Logic:

Because the document is being created, SharePoint needs time to “register” the file in the content database. This means if you run a flow on a freshly-uploaded document and the Flow runs too quickly, it might run into a “checked out” or “shared document is in use” error.

To get around this, you will have to use a couple of variables – one to tell the Flow if the file update was successful, and one to increment a “delay” period if SharePoint isn’t done “registering” the file yet.

How to build it:

The whole Flow looks like this:

Other Notes, Thoughts and Considerations

  • You can probably get away without using the incremental value (and reduce a couple of actions), but the incremental minutes will allow less attempts to update the item and work with content databases that may be operating slower. Try it yourself and see what works – as long as it works for you!
  • You don’t need to have the “Parse JSON” step to refer to the values from the Excel table later in the Flow. You can write them in via expressions. Use the “Parse JSON” action if you want to be able to pick the values from the Dynamic data GUI.
  • Consider only using this scenario in a “One item, one table” Excel file. For example, when the file is used as a “Form”. Otherwise you’ll have to use the action filters and possible incrementing ID’s in the “Update Item” step to align each table row with different documents.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s