cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
marioS
Frequent Visitor

Update Sharepoint list item from Excel

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

marioS_2-1631023870800.png

 

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

 marioS_1-1631023258146.png

 

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

 

any help is well appreciated.

 

Thanks

Mario

 

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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.

vLilyWmsft_2-1631090351264.png

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.

View solution in original post

marioS
Frequent Visitor

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

View solution in original post

9 REPLIES 9
v-LilyW-msft
Community Support
Community Support

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

vLilyWmsft_1-1631066415231.png

2. Enable Pagination

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

vLilyWmsft_2-1631066464996.png

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.

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

marioS_0-1631086749037.png

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...

marioS_1-1631087135034.png

 

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

 

Hi @marioS 

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

vLilyWmsft_0-1631088473964.png

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=’.

vLilyWmsft_1-1631088996275.png

Best Regards

 

I am well aware of the internal name. 

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.

vLilyWmsft_2-1631090351264.png

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
Frequent Visitor

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

Dina
Helper II
Helper II

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
Frequent Visitor

@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
Post Patron
Post Patron

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?

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,812)