We can’t avoid circumstances where we need to add items on the list dynamically. Most of the time, we get the data from excel especially, when these are from other apps. So to lessen the time rendered in adding it manually, it is best to add it dynamically using Microsoft Power Automate Flow. So this blog will be all about copying excel records to a list.
Preparing the excel file
To start, ready the excel file. The file should contain an organized data to add the items efficiently.
Add the items to a table. Then label the columns well and make sure the items listed are what you expected them to be. The above image is an example of the correct way on how the record should look. After preparing the excel file, upload it to a document library. We want direct access to the file, so we need it inside a document library.
Building the flow
The excel record is ready, so we can now start building our flow. First, let’s create a manually triggered flow. Note: You can always choose the trigger that you want based on the scenario that you are on.
Add the action List rows present in a table. This action will get all the records inside the excel table. Add the Location of the document library where you uploaded the excel file.
Then select the document library and the file. For the Table field, select Table 1 since we’re only using one table. Note: ‘List rows present in a table‘ action only gets the first 256 items of the table. If you have more than 256 items on your table, you can use the Skip Count field to skip the items that you already have copied to the list.
Next, use the Apply to each action to iterate the items retrieved from the last flow action we added.
There are times that the Date field is not retrieved correctly.
For this one, we’ll need to convert the retrieved data to the correct format. Use this formula to convert the date:
I added a Compose action to have a temporary holder of the value.
Then add the items to the list using the Create Item action. Add the Site Address and the List Name. Also, enter the values to the fields you want to fill out. For the Date field, add the Output of the Compose action we set up.
The flow is now ready. Run the flow to start adding the items to the list. Below is the example record that I copied to the list.
That’s it for the copying of excel records to a list. If you have any questions about the topic, please let me know at the comment box below or email us directly. Have a nice day!
AAD UserId Add-SPOTheme Apply Theme Content Type contentTypeHub CopyExcelRecordToList Create List Item Create list item using rest Current User Permission Custom Theme DenyAddAndCustomizePages ExcelPowerAutomate Expand External Users Flows isExternalGuestUser Library Views List Templates List Views Metadata Microsoft Flow O365 PageContext People People Metadata Permission PnP Power Automate PowerAutomate PowerShell React Rest API Rest Endpoint Save as Template Send an HTTP Request to SharePoint SharePoint SharePoint Color Theme SharePointList SharePoint Modern SharePoint Online SharePoint OOTB SharePoint PageContext SPFX SPO UserIsExternal