Add or Update Rows in Excel from SharePoint with Microsoft Flow

Learn how to update a row in Excel whenever the corresponding data in a SharePoint list is modified or add a row in Excel when a new list item is added into SharePoint. 👇
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
DOWNLOAD the example workbook here
📖 www.howtoexcel.org/downloads/
SUBSCRIBE & get my 3 FREE eBooks
📧 www.howtoexcel.org/newsletter/
CONNECT with me on social
Facebook: / howtoexcelblog
Twitter: / howtoexcelblog
LinkedIn: / john-macdougall
Thanks for all your support!

[@HowToExcelBlog]

Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/

[@vaibhavsns]

This video was really helpful. I am new to MS- Flow so I was wondering how I can filter the list and find the item that I need and your video really helped me.

[@anisshaikh2057]

Thank you John for this wonderful session.

[@HowToExcelBlog]

No problem Anis!

[@annmarchant556]

Great guide, easy to follow and works like a charm, thank you!!

[@bouseuxlatache4140]

wonderful flow which actually can be used in many situations. thanks.

[@WilliamZamboneli]

one of the best explanations,

[@davidwhite4923]

Nice and easy video to watch. explained each step very well thank you

[@HowToExcelBlog]

Thanks 🙂

[@JosueGonzalezCRC506]

Es justo lo que necesito. Gracias.... excelente video.

[@K8Tech]

Your video really helped out a lot of people.. Keep it up!

[@HowToExcelBlog]

Thanks K8T. My plan is to keep posting vids. 🙂

[@zanissaulewicz8821]

Great explanation! You´ve helped me out a lot :)

[@HowToExcelBlog]

You're welcome Zanis!

[@wildenv]

Thank you..Great tutorial...

[@HowToExcelBlog]

Thanks!

[@danielfclp]

Thank you very much! Your vid helped me create tasks on Planner based on a excel sheet. I was trying the get row (in flow) function but could never figure out what was the Key Value of the rows I needed. After watching you, I've tried the list rows then applied each to create the tasks.

[@HowToExcelBlog]

Good to hear! I found it tricky the first time as well.

[@HowToExcelBlog]

Actually, a better way is to use the Get row action instead of list rows, then use a condition instead of filtering. If the table is too big, list rows might not work (I don't know but I imagine at some point there will be too many rows to list). After get row, test if a value in the row is null. Then add or update the row based on that test. Hope that makes sense.

[@flopezsa15]

Great post, I had the same issue with the comparison, thanks!!

[@HowToExcelBlog]

🙂👍

[@andresfriss919]

thanks for posting, i've been looking for this for a long time, but there was a doubt, does it also work to eliminate lines? greetings from Paraguay

[@mburt0n]

Thanks, this is helpful. I am looking to go from Excel to SPO and I think the steps here would be similar.

[@HowToExcelBlog]

You can export data from Excel to a SharePoint list. Turn data into a table the Table tab then Export.

[@abhinandanbatra1466]

Hi Thank you for the great presentation. This was really helpful. I am trying to use this flow with a picture library. In picture library we have graphs and analyzer have to assign them usable or not usable. The flow works fine if we have name of the graph already in excel sheet. On adding a new graph to the picture library, flow adds a new row to the excel sheet. But, if i assign usable or not usable to graph later, rathe rthan editing the previous row created, it creates a new row. Not sure why this is happening. Will appreciate any help.

[@Asif_Shaik_]

Thank you so much. I want to create some reports out of this Excel sheet data and show that report it in SharePoint page. Will that work? Anyway I am going to try this.

[@vivianc3118]

Thank you so much, really helpful! Add and modify works, but when I remove values from SharePoint, it doesn't become empty in excel. Did I do something wrong? Thanks.

[@kyilei4158]

Could you also make a video about how to move attachments from sharepoint list to excel? Thanks !

[@joaofigueiredo2327]

Hi There, I´m from Portugal and newbe in sharepoint, Thanks for the vídeo, it´s very good and well explained!!, i´m tryng with a list of 3K itens, and the flow show error on time limit. By the way, it´s possíbel sync both way?

[@stevenjanssen7148]

Great Tutorial ! one issue, when i make a change the FLOW registers the change (it shows in the flow result) but doesnt update the actual spreadsheet?

[@anjalisingal6202]

This was really helpful. Thanks for posting!! Here is one issue I am facing: I created a column (type: Multiple lines of text) and I have entries which are both Single line multiple lines as well. Also they are in bold. It returns a value something like this: ABC What should be done?

[@kayfax672]

Hello, thank you for the video. One question though - I have seen similar solutions and they all use the "when an item is created or modified" trigger. I have a flow that worked fine like this, but recently sometimes many people at the same time added or edited items in the source list triggering endless loops and causing the flow to time out or - worse - mix things up in the Excelfile like adding rows double/triple, confusing the attached rows etc. So I am looking into simply scheduling the flow - i.e. once a week somewhen around midnight would be enough. Get rows, check if they exist, if not, add, if yes, update. Sounds simple but the Flow I created for that also timed out. Any suggestions how to solve this? The list is not huge, it will mostly be around 200 rows/items. Thank you! (oh, condition is, only get and update the SP List items that do not have a empty value in a specific date column (currently, I use a condition with empty(date) does not equal true.) If you have an idea, please share it. Thank you!

[@shilpaswami3548]

Hi, Thanks for the video. But my Filter array condition is showing empty even if i update an existing item. So if i edit already present row in excel instead of editing it duplicate row with same ID is created

[@paulaperrotta]

Thanks for the tutorial.. But I am getting a "No row was found with Id " error on the update row command.... I'm doing exactly what is shown in the video. Please help!

[@farazmohammed2003]

Following are observations: 1. File has to be selected manually from the folder icon. If the file path is passed dynamically, it throws error saying invalid file. Just doesn't work dynamically. 2. By default, table name in the file will be 'Table1'. Someone has to manually update the file name and make no one changes it. Hence, making the whole idea of automation worthless. 3. Under Conditional action, you can use [body 'is equal to' fx(null)]. There is a null function that can be used in right side of the conditional statement.

[@HowToExcelBlog]

1. Yep 😢 2. I don't think that makes it worthless. It's just something to be aware of. Most users don't know how to change a table name though.

[@junbecks]

Hi, as some may have experienced this, when you share your sharepoint with others, they can see the data and you can set so that they edit it. Can you make a flow, to send that data to a list only you can see, and then delete what’s in sharepoint? I cant imagine any organisation would want all its employees to see all the data that may or may not be confidential.

[@yeyunhuang143]

Pretty helpful!! Thanks for sharing! BTW, if my table has arrays, so that when I choose sharepoint value, it will generate " apply to each", and I find I cannot drag it out and delete the "apply to each", it alerts" this item cannot be drag out". What can I do for it? Looking forward to hearing from you!

[@DiemTran-nk7bo]

You can drag out "add a row into table" out down of "apply to each" instead of move it up, then you move "apply to each" down to "add a row into table", then delete "apply to each"

[@sexyberlam]

This is great work! Thanks for uploading! Can you also give an idea or say which steps I need for how to compare or look for the items which I wanna to delete from my Sharepoint. I can successfully add or update or rows. But I do wann delete a row from Sharepoınt list. Then that row should be also deleted from my integrated Excel. I tried to do it with these steps: 1) When an item is deleted (SharePoınt) 2) List rows in a table(Excel Business) 3) Filter array(I dont know here how to compare or look for the same ID from Sharepoınt and Excel) 4) Delete row in a table(And I dont know how to know that specific ID to delete) Can you help?

[@savy00713]

Nice video, I really like it, I have a question: It fails to update the excel if it's checked out by someone else, any workaround?

[@HowToExcelBlog]

No, that's the point of checking out a document so no one else can edit it while you're working on it.

[@joshuafung8618]

Does it works same way if i want to have live update the date from another excel file update that from other sharepoint folder? As list look just quite same as excel

[@MrNoppont]

Great guide, but I have problem in step to drag Add a row into the table outside the Apply to each area ,How to do that?

[@madhurohinivadlamani2021]

I have 2 excel sheets that have data. Based on the ID field(which is text) present in both excel files, I want to update a column in the 2nd excel sheet with a column value for a matching ID from the first excel sheet. How do I replace your first step where you showed using a Sharepoint?

[@abhinandanbatra1466]

Hi, thanks for wonderful summary. I need to add another option to delete a row in excel if item is deleted from list. Can you let me know how to do that?

[@HowToExcelBlog]

Try building a flow with the sharepoint trigger when an item is deleted.

[@geralddahl9159]

I'm working from CDS not Sharepoint, but, when I delete a CDS item all my Excel items get deleted.

[@WilliamZamboneli]

Please sir i have a question, i need some like this, but with one more step, itens created on excel just need to be uploaded to sharepoint list too

[@jessjimenez7039]

Thanks for this video! What if you’re trying to do the opposite.. have the sharepoint list update from changes made in excel; is that possible?

[@HowToExcelBlog]

Good question. One I don't have an immediate answer for. I'll investigate and make a video if I can get it working. Watch the channel for it.

[@svet1anaa]

​@@HowToExcelBlog I am looking forward to seeing your new video, too. I have been following the steps at this video (kzbin.info/www/bejne/p6nYhYmghsd4edE) from @JamieMcAllister, and it works great for a small set of data. The flow successfully completes after the first 256 items, regardless the rest of the items (I have 30000 lines, but I tried with smaller set of 2000). I assume your filtering method would be a workaround that.

[@sethconner3854]

@@HowToExcelBlog Has there been any update to this questions yet?

[@robertwanthouse3596]

Great video! Does this work when you are trying to use textual values in your filter array? For some reason, my flow won't update my excel spreadsheet when text is involved. Thanks!

[@HowToExcelBlog]

Maybe you are missing single quotes around your text values?

[@HowToExcelBlog]

Single quotes in Flow are needed for any text string.

[@robertwanthouse3596]

Hi, My filter array expression looks like this: @contains(item()?['Audio Title'], triggerBody()?['Title']) I think the single quotes were already added ("audio title" and "title" are the two values I am checking to see if they are equal)

[@TheNakkytakky]

This video was a great help, but I had to leave out the filter array & condition, because it got stuck in condition yes, but the ID was already listed in the table. Any idea how come?

[@HowToExcelBlog]

Since making this video, I realised à better way is to use the Get row action instead of list rows, then use a condition instead of filtering. If the table is too big, list rows might not work (I don't know but I imagine at some point there will be too many rows to list). After get row, test if a value in the row is null. Then add or update the row based on that test. Get row is a single row, so won't need the apply to each step. Hope that makes sense.

[@DaxCezare]

@@HowToExcelBlog Any plans of making a video using the 'Get row action' instead? Looking forward to it. Great help and good job!

[@goldcarps]

Hi, the ID in excel is Number, the string function changes the ID in SharePoint List to text. So we are comparing Number with Text? Regards, goldcarps

[@MegaDon89]

Thanks for this Tutorial! Helped a lot. However I have a issue. I've followed the same tutorial and built my flow but it seems to add rows in the spreadsheet at the bottom of the spreadsheet table. Which I have defined as more than 5000 rows. So the first item is added in the 5000 row and not the 1st row

[@HowToExcelBlog]

Yes, new rows are added to the bottom.

[@MrArtZeh]

You could add a separate column for time entered and then sort by that to have newest at the top

[@HowToExcelBlog]

Yes, recently they've added run a script action in power automate. You can record a script in Excel online (like sort your table) then run it after adding your row to the table.

[@user-hf1dc7tk4s]

Hello , It' worked well but it's creation multiple entries. How can I able to resolve this?

[@NabranAboubacar]

Thx , really helpfull, but keep your mouse away from code since your desktop res is high it's difficult to see what ur writing since it's not in description. Thx Sir this help me.

[@HowToExcelBlog]

Good point. I've also upped the transparency of the yellow cursor effect so you can see through it if it does get in the way (for future videos). 🙂

[@NabranAboubacar]

@@HowToExcelBlog This trick to hover elements to get the expression is a master kill, really was having hard time, but with your vid I know solve the problem. Thumbs up & sub Thx

[@powerwizard953]

This is amazing, But I am having one issue, In my case, it always returns length as 0 and create an item even though it already exists.

[@DuncanFairweather]

Is there any way to do this so that the excel file that is being updated is NOT in sharepoint (or Excel online)? I need a file in the file system to be updated so I can import into SQL Server.

[@nivinilvlogs]

Hello, What if the item is deleted from SharePoint list? How to delete the item from excel too? Nilanjan

[@sayalirodge8538]

How table property is coming with "Quote" option, I have created excel with one sheet and few rows, any specific steps to follow while creating excel.

[@HowToExcelBlog]

Sorry, I don't understand the question.

[@ShagoulHameed]

How to highlight the modified column values into Excel file?

[@divya7555]

How can we update bulk info from SharePoint to excel...may be 75k line items..any idea?

[@leslyserrano4005]

HI, I'm trying to add a row every time a new item is created in my sharepoint list, but I have problems with a with a multiple choice field. How can I export that type of data to the corresponding cell in excell? Please help

[@HowToExcelBlog]

There should be a dynamic content labelled "status value" for a choice field called "status" as an example.

[@HopeRemaiins]

Is there a way to make the date that gets filled in a future date like 2 years from the current day?

[@HowToExcelBlog]

docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#getFutureTime Try the getFutureTime function!

[@mohamedessamish]

Hello, just one question please sir. I used to trigger a value in a table and based on that an Email should be Sent to a responsible which i am Till now satisfied With. Now we want automate the process to higher Level so the responsible that gets that Mail should Provide me With Data. Is it possible to let this responsible Person reply on this Mail With the required Data und this Data should be directly inserted in a specific filed in the Table. Looking forward for your answer and thanks alot in advance

[@HowToExcelBlog]

It might be possible, but definitely trickier to describe than a KZbin comment would allow for. You might be best off to email a link to a form for data entry as a lot could go wrong with someone attaching a file.

[@mariorgutierrezleal]

Great video, what happens when the filter gives you several rows not only one, thanks!!

[@HowToExcelBlog]

That's why you need a unique ID like the SharePoint ID column.

[@mariorgutierrezleal]

@@HowToExcelBlog thanks

[@HowToExcelBlog]

Updating a row in Excel needs a unique ID column.

[@robertobirrer9976]

When i choose The doc. library, File (where ist the excel loacated) il get no selection for the table. Has everyone an idea why i can choose the table?

[@HowToExcelBlog]

Sometimes there are bugs like this 🙁 I'll see if I get the same.

[@scottdoscher5607]

Great Video. Just what I was looking for. Flow won't allow me to slide the "Add Row into a Table" up so I can't delete the "Apply to Each". Any suggestions? What could I be doing wrong?

[@HowToExcelBlog]

Since making this video, I realised à better way is to use the Get row action instead of list rows, then use a condition instead of filtering. If the table is too big, list rows might not work (I don't know but I imagine at some point there will be too many rows to list). After get row, test if a value in the row is null. Then add or update the row based on that test. Get row is a single row, so won't need the apply to each step. Hope that makes sense.

[@scottdoscher5607]

Hello. I am new to this, so I am afraid it doesn't :( Is there any chance you have some scr shots you could share of the updated strategy? My main issue was that I couldn't pull the "add a row" action up out of the "apply to each" and then delete it like you do in the video. But it seems like you have a better architecture to the whole strategy.

[@avidspider]

@@scottdoscher5607 Little late, but I was able to do it. I just added another step outside of the Apply to Each, recreated the add row just like the previous one, then deleted the Apply to Each version. This seems to have worked for me, if you couldn't get his method to work. Hope that helps!

[@kylekalicharan6676]

You can drag it down instead of up it will come out of the 'apply to each' then you can delete it.

[@K14n70P07h1k]

Great video. What if empty(body('Filter_array')) is "False" but instead of updating a row, i want to create a row with the same ID? Example: i have an item with ID 3, I want to create a new row with new information with same ID 3 I have used "Add a row into a table" it creates new row but with different ID like 4 instead of 3. In SharePoint I have the row with same ID as 3 but in excel I get 4 instead of 3 how to fix it?

[@HowToExcelBlog]

Don't use a condition and only use the add row action.

[@HowToExcelBlog]

You wouldn't need to list rows either. Just trigger when SharePoint is created or modified then add row to Excel.

[@K14n70P07h1k]

@@HowToExcelBlog Thank you

[@HowToExcelBlog]

👍

[@patrickoyekanmi6946]

How about a trigger for if an item is deleted from the parent list?

[@Doc131282]

Patrick Oyekanmi that’s a good point, sir. I would need that, too 😂

[@sexyberlam]

I would like to know it too. That would be really helpfull

[@kamacete]

hi, I have been tried to update an excel file in localhost with an excel stored in Sharepoint using this example but for some reason don't work, can you please have any idea?

[@HowToExcelBlog]

I had a hard time with the SharePoint list ID, so try it both with the string function and without.

[@CarpeNoctem42]

@@HowToExcelBlog Isn't it possible to select the ID and put it between single quotes? That did the trick for me as that makes it txt. Also: If you select "Body" for condition and select is equal to "null" instead of empty (where null is an expression of course) you also get the same result.

[@HowToExcelBlog]

Will try that next time. Thanks for the tips!

[@DuncanCunningham]

This is what I've been looking for. Sharepoint to Excel. But as I follow along with you mine differs as I am unable to select the 'Table' in the file, so I just put that in Manually. but now that I'm at the 'Filter array' I don't see any columns to compare to the sharepoint one.. so I'm stuck. This is what I find so frustrating about Microsoft Power Automate. It seems to be quite buggy. They change things so they improve but also it can break a method of creating flows.. I tried to change the file names so they had no hypthens and then no spaces and moved the file to other folders. it just will not pick up on the excel sheets, sheet names. I also added a step before this to 'get tables' thinking that it needs more info to process. didn't help. this is my only option to automate internally at my company as they block out outside tools for security reasons. I think I just need to learn more but this does seems to be broken for me.

[@yeyunhuang143]

You should make a copy for your excel file, because if it export from sharepoint list, it has sharepoint list features, so when you copy another editable excel file, which do not have sharepoint features, you can work on it

[@alianatasha465]

Hi! I tried using this method but the row output in excel online shows "@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference" Any solution for this?

[@lopezsolisalexa4406]

HELLO , IN MY CASE , I DONT HAVE THE OPTION DYNAMIC CONTENT WHAT CAN I DO?

[@HowToExcelBlog]

They probably changed the user interface. It'll be there somewhere.

[@Zebah08]

Good day! Thank you for this tutorial. I have been using this for several months now and it has been working like a charm. Recently, however, it seems that when I update an item on Sharepoint (via PowerApps) that item gets duplicated in the excel table (even though the unique identifiers are set). The duplicate does NOT appear in the Sharepoint list and ONLY the new item created (duplicate ID and other fields) shows the updated portion; i.e. updated price. EX: Item Name "Zed" Item Retail "$59.00" Item ID "215" -> I update the price to "$64.00"via Sharepoint and then both show up in Excel. One has the original info including ID, the new one has the same id but with the updated price. - "Zed $59.00 #215" AND "Zeb $64.00 #215" - which ends up breaking all my VLOOKUP or Table Index formulas 🤦‍♂️ Is there a limit to the number of items the Power Automate FILTER function can pull? I have over 350 items listed. Anyone else experiencing this?

[@walterwego1396]

Hi Chaz, I encountered the same issue. All worked fine until it didnt and now simply creates the duplicate line for the updated row. Did you manage to resolve this? If so how. Cheers

[@Zebah08]

@@walterwego1396 yes, I had to change the number of rows power automate would search for. It was set to a limited amount around 250 and anything beyond row 250 would get added as a new item. The settings were within the flow parameters. I'll give specific menus shortly...

[@Zebah08]

@@walterwego1396 Its under Pagination settings. Within your Power Automate (Flow) action for "List Rows Present in a Table" click the 3 dot menu and go to "Settings." Increase the pagination size to accommodate your needs.

[@walterwego1396]

@@Zebah08 thanks so much for this. I had been playing around with odata filters to try to achieve a reduction in assessed lines but i think this will allow for larger tables. Thanks again.

[@Zebah08]

@@walterwego1396 let me know how it turns out.

[@nelsonalvarez1013]

Hi, I seem to have an issue with just the list rows command. For some reason it has stopped working like it before, 6 days ago my flow worked how I wanted it to however now it says it's failing because of this error: Unable to process template language expressions in action 'List_rows_present_in_a_table' inputs at line '1' and column '2630': 'The template language expression 'json(decodeBase64(triggerOutputs().headers['X-MS-APIM-Tokens']))['$connections']['shared_excelonlinebusiness']['connectionId']' cannot be evaluated because property 'shared_excelonlinebusiness' doesn't exist, available properties are 'shared_sharepointonline, shared_office365'. Please see aka.ms/logicexpressions for usage details.'. Can you help explain this to me, I am very confused on how flow has automatically made what worked before as something that's an error.

[@HowToExcelBlog]

Not sure off hand. Maybe you can post in the MS Flow forum. powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[@Ollieunited97]

i seem to receive a true result in the condition when the same ID comes up, i have Lots of duplicate values now, can anyone help?

[@unbiasedUBR]

How to delete multiple rows in a table

[@anchu326]

I followed this video,while updating rows in excel, the entire row is being duplicated again in the excel sheet. Any turn around? Where am i going wrong ?

[@anchu326]

Hi, Can you please provide assistance? My filter array with quote ID isn't working.

[@debram8829]

I got exactly the same issue - did you find a solution?

[@sininv1]

hi I don't have the quoteID?

[@HowToExcelBlog]

Use something else that is unique!

[@gregoryleite7816]

Mine had been working for 13 days, now the flow is skipping everything I create in the library

[@HowToExcelBlog]

What is the error and on which action?

[@reecefranklin5543]

Hi! I followed the tutorial to the letter however when I modify an existing item in my share point list its adding a new row instead of updating it. Any idea why?

[@reecefranklin5543]

Appears to be a limit on the list size - I have a list of 2000+ lines so will need to rethink the solution

[@HowToExcelBlog]

There's a better way to do this. Read my previous comments.

[@harrajmarway]

@@HowToExcelBlog Hi, great tutorial, it's the only one like it on the internet. Unfortunately, I've also got the same problem. The Flow wasn't updating the spreadsheet, so I tried using 'Add a Row' and testing to see if its null. I think i'm doing it incorrectly, what value do you enter in the conditions?

[@ernestoramirezgarcia9464]

It works for 256 rows only, after that the flow starts to duplicate the rows

[@dhiravtm464]

Hi, Im facing the same issue. Was your issue rectified?

[@k9b4]

Every time I run the flow, the Filter Array body always comes up empty no matter if it is a new or update. The only issue I can find is that someone stated that Filter Array does not work well with numbers. Are you aware of any fixes, because updates always create a new row and new data from SharePoint List creates a new row on excel.

[@k9b4]

Figured out the issue was my column title on excel for the ID has parentheses and forward slashes. Once I changed the title to only text, the flow worked.

[@anchu326]

I followed this video and the flow is continously running, it won't stop and the same rows are being duplicated

[@HowToExcelBlog]

How frequently is the list is being modified?

[@anchu326]

@@HowToExcelBlogThanks for replying i have connected the sharepoint to microsoft form, i'm expecting close to 10 entries per day.

[@aryannuccia]

Did you fix this error?

[@stephanweaver1960]

Not correct - tables can combine PQ and anything else - manual, calc columns, lookups etc. First few columns can be PQ, rest can be anything

[@HowToExcelBlog]

Yes, but what happens when you type data into a column in the Excel table, then you delete a row in SharePoint, then you refresh the Excel table / PQ?

[@kahani93.5]

Flow is very unpredictable. I never advice clients to use flow for mission critical stuff.

[@HowToExcelBlog]

Yes, I know what you mean. Microsoft makes updates all the time and sometimes it breaks things. Has happened to me a few times. Luckily I know people at Microsoft and was able to alert them and they fixed it.