How to Populate SharePoint Library Columns using Power Automate
Question details
The user needs to create a Power Automate flow that automatically populates specific columns in a SharePoint document library by looking up a matching document number in a separate SharePoint list.
- Product
- SharePoint and Power Automate
- Device & OS
- not provided
- Scenario
- Automating document metadata entry by retrieving corresponding details, such as expiration date and document owner, from a reference SharePoint list whenever a document number is entered or updated in the library.
- Observed behavior
- Metadata needs to be accurately copied and formatted from the SharePoint list to the document library without manual data entry.
Ensure you have the necessary permissions to create and edit Power Automate flows, as well as contribute access to both the target SharePoint document library and the reference SharePoint list.
Create a Power Automate Flow to Update Document Metadata
Set up an automated cloud flow that triggers upon document modification, retrieves matching data from a SharePoint list, and updates the document library columns.
By using the 'Get items' action with an OData filter, you can efficiently locate the exact SharePoint list item that corresponds to your document library file. This prevents the flow from pulling unnecessary data and ensures accurate metadata mapping.
In Power Automate, create an automated cloud flow. Choose the SharePoint trigger 'When a file is created or modified (properties only)' and link it to your target document library.
Add a 'Get items' action pointing to your reference SharePoint list. In the 'Filter Query' field, use an OData query to match the library's document number to the list's document number (e.g., `DocumentID eq '@{triggerOutputs()?['body/DocumentNumber']}'`).
Add an 'Update file properties' action. Power Automate will automatically wrap this in an 'Apply to each' loop because 'Get items' returns an array. Populate the required library columns (like Document Owner) with the dynamic content from the 'Get items' action.
For date fields like Expiration Date, use an expression to ensure the correct format. Click into the field, select 'Expression', and enter `formatDateTime(items('Apply_to_each')?['ExpirationDate'],'MM/dd/yyyy')`.
Edit Your SharePoint Documents with WPS Office
While utilizing SharePoint and Power Automate to manage your document metadata, you can use WPS Office as a free, lightweight alternative to create, edit, and view your Word, Excel, and PowerPoint files natively.
- 1. Download WPS Office: Visit the official WPS website to download and install the free office suite.
- 2. Open your documents: Download your files from SharePoint and open them directly in WPS Office.
- 3. Edit and save: Make your edits with full formatting compatibility and upload the updated files back to your SharePoint library.
Frequently Asked Questions
Why is my Power Automate flow running an infinite loop when updating file properties?
This happens if the 'Update file properties' action triggers the 'When a file is modified' trigger again. To fix this, go to the trigger settings in Power Automate and add Trigger Conditions so the flow only runs when specific fields (like the Document Number) are changed.
How do I filter the SharePoint list to only get one specific matching item?
In the 'Get items' action, use the 'Filter Query' field. Write an OData query such as `ListColumnName eq 'DynamicContent'`. This ensures Power Automate only retrieves the exact list item matching your document library number.
Can I update a person or choice column in my document library using Power Automate?
Yes, but they require specific formats. For a Choice column, provide the exact string value. For a Person or Group column, you typically need to provide the user's email address or Claims identity dynamically retrieved from the SharePoint list.
