Update Sharepoint list item from Excel

[marioS]

Hello all, 

i am facing an issue with the Update Item action and unfortunately did not find an existing discussion which helped me finding a solution.

The use case is as follows:

- From time to time I get an Excel list of items (1000+ rows and 1-n columns) which I have to update on Sharepoint using Power Automate. I have used the "update item" action many times without an issue but now I am struggling.

- I could assing the needed value coming from the Excel list

- My flow seems to be working fine but the items are never updated

 

- The Sharepoint list has maybe 40+ columns and around 3500 rows. Could this maybe affect my flow?

any help is well appreciated.

Thanks

Mario

[v-LilyW-msft]

Hi @marioS 

When reading Excel files within Flow, by default it will return the first 256 rows , and by default Get items action will return the first 100 items from the list. You can increase this limit in the following way.

1. Click Seetings

2. Enable Pagination

3.Set the Threshold above 256 e.g. 5000.

You can set Get Items in the same way to increase this limit 100 to the list view threshold limit of 5,000.

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marioS]

Thanks for your reply. 

The treshold for List rows present in table was set to 2500 not yet to 5000, what I did now. 

Get items treshold has already been set to 5000.

Run a test after increasing the treshold for the List rows present in table action and still dont get the value updated in Sharepoint. 

Get items action (with filter query on ID) finds the values or IDs to be updated

and in the output of update item action I cannot see the column for which I provided a new value but as you can see in my initial post, the value coming from excel is properly assigned...

i guess it probably is related to limitations with or settings in Sharepoint as I rebuilt the flow and getting same results...

Best regards,
Mario

[v-LilyW-msft]

Hi @marioS 

Is 'Contract_x002d_In_x0020_ID' your column internal name? Please make sure this is the correct internal name.

You can confirm your column internal name via List settings -> Click on the column name -> It’s at the end of the URL on the Edit column page, after ‘Field=’.

Best Regards

[marioS]

I am well aware of the internal name. 

[v-LilyW-msft · Accepted solution]

Hi @marioS 

I found that your excel column name has special symbols '/', Please change ‘/’ to a space or ’_ ‘ and try again. 

After changing the Excel column name, please save the Flow, close it down and re-start it, and select the modified excel column name.

For more info about Invalid characters you can refer to:

https://support.microsoft.com/en-us/office/restrictions-and-limitations-in-onedrive-and-sharepoint-6...

Hope the content above may help you.

Best Regards

If my solution helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marioS · Accepted solution]

@v-LilyW-msft dear lord, the special character... thanks a ton for your help. Flow is running perfectly... 🙌

[Dina]

hi all, what if the data is not in a table formatting? I get an email with an excel document and was trying to automate the entire process. This excel is generated 2 times day. 

How to create a flow that fetches the email, grab data from excel and updates the spo list?  

Again my excel is not formatted as a table. Would I have to use a compose formula? versus "get items from table" ? Any help with building the compose function would be really appreciated.. 

data sample 

A| A1|Y 

B| B2|B

C| C3|D

[marioS]

@Dina Hi Dina, Power Automate is able to create a table from a pre defined or dynamic table range in an excel. There is a specific function named „create table“.

give it a try…

[dmclaughlin]

I'm also trying to use an excel to update sharepoint.  The 'list rows present in table' seems to be fine because the next step 'get items' in the filter query sees the excel fields.  What I cant get to work is the excel field eq the list field.  I know for a fact the field ITEM_NUMBER is in the list.  But it fails saying it is not.  Any ideas?