cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
babz88
Level: Powered On

Create SharePoint Item from Online Excel table

Hi,  

 

I'm trying to create a flow where when an online Excel file is updated then a SharePoint List item is created for each row from that Excel file. The excel file is hosted in a SharePoint folder.   I have the following flow: 

Capture.PNG

When i test the flow it fails at the create items stage.

 

I've done a similar workflow succesffuly using the Get Rows actions however this only appears to connect to an Excel file on your OneDrive. 

 

Any help/suggestions would be greatly appreciated.

4 REPLIES 4
Super User
Super User

Re: Create SharePoint Item from Online Excel table

Can you show the run history with the error messages included?

babz88
Level: Powered On

Re: Create SharePoint Item from Online Excel table

I get the following error message:

 

{
  "status"400,
  "message""String was not recognized as a valid DateTime.\r\nclientRequestId: dfc2d7dc-cab9-4dac-8432-f2225f6b895e\r\nserviceRequestId: 71819d9e-60d4-7000-f1ed-b95466ba84b4"
}

 

Looking into the details i have two date fields within the Excel table. They are written in DD/MM/YYYY format but it appears when they get transfered to SharePoint as the 5 digit excel number format. 

 

i tried running the flow without the date columns and then got the following error message:

 

{
  "status"400,
  "message""The data returned from the tagging UI was not formatted correctly\r\nclientRequestId: 34390113-d5e6-410f-b765-5a7f2579387a\r\nserviceRequestId: ca819d9e-a0b8-7000-f8c9-b73477330fcb"
}

 

Any ideas?

Super User
Super User

Re: Create SharePoint Item from Online Excel table

I would use the date formats of yyyy-MM-dd

 

Maybe first try to create the items manually by just hardcoding the text etc. Then once you managed creating an item add the varibale instead of the hard coded values.

 

Also it helps to know which type of fields you are trying to set. Text fields are easy, but lookups, choices and multi value fields can be harder.

 

 

babz88
Level: Powered On

Re: Create SharePoint Item from Online Excel table

So found the problem. 

Regarding the Dates, it appears the Excel Online function extracts data as an integer even though the date field is formatted correctly within the excel file. Found another thread on this:
https://powerusers.microsoft.com/t5/Building-Flows/Excel-dates-turned-into-integers/td-p/130364

 

Appears Microsoft hasn't fixed this bug.

 

Regarding the other issues I found one of the field columns i was using (Country) wasn't aligning with the metadata field on SharePoint.

Helpful resources

Announcements
firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (5,938)