cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jendebett
Regular Visitor

data connection refresh excel with flow

Hello,

 

I have an excel workbook saved in a document library on sharepoint. The excel file contains a table with data from a .txt file.

 

every day a new .txt file is emailed, and I have used flow to save the file in sharpoint.

 

I have the data connection set to refresh in the background, and refresh on opening.

 

so every day I open excel, allow the table to refresh, save and close.

 

i'd really like to have flow do this data refresh without opening excel... is this possible?

48 REPLIES 48
MohamedEssam
New Member

Any update here? as I am facing the same issue and the script doesn't refresh the file.

MohamedEssam
New Member

any update here? as I am facing the same issue and the script doesn't refresh the file's external connections.

Alishia
New Member

Power Automate doesn't have a feature to allow you to refresh connections or open/close a document. I was also encountering the issue of the document not refreshing on "open" because it was locked by another user (due to using One Drive).

I needed my workflow to add rows to a spreadsheet, update the pivot tables, and then send an email with the results. Here is what I did to get around the pivot tables not updating.

In Excel:

1. Open the pivot table options and select "Refresh data on file open".

Alishia_1-1661964347980.png

 

2. On the worksheet with your pivot table(s), create a table in a cell that has similar information as below (in the workflow we will update this table so it forces the system to re-open the document and refresh). Once the table is created, select the column and "Hide columns".

Alishia_0-1661964278956.png

3. If you are using One Drive or a shared document like I did, when you have made all the changes you need, you will want to change the session to "viewing", and then close the document. 

*Any time you make changes manually in the document, make sure to "close" the session by changing it to "viewing" or else the workflow will not complete properly due to the doc being locked.

Alishia_2-1661964479219.png

In Power Automate: I have my trigger set to a schedule at the end of the month but your trigger or schedule and parameters can be whatever you set them as. I'm going to show you the steps that are important to the spreadsheet.

*Workflow snapshot:

Alishia_3-1661965589615.png

 

1. Add a row into a table > Select the file location and the table > Update all the fields from the SharePoint list (Value= outputs('Get_items')?['body/value'].

* I used this step to update the spreadsheet automatically with items added to a SharePoint list (so it can be extracted into a report monthly).

Alishia_4-1661965830279.png

2. Delay 1: 10 minutes. This allows One Drive time to close and end the current session.

Alishia_5-1661965878742.png

3. Update a row: Select the location of the file (this is the table we created and hid earlier).

Alishia_6-1661965982465.png

4. Delay 2: 2 Minutes

Alishia_7-1661966056237.png

5. Get file content (One Drive) > Select the file. I was limited to only being able to see my individual One Drive so make sure your excel workbook is saved in a location you can select in Power Automate.

Alishia_8-1661966115203.png

6. Convert file (One Drive)> Select the file. 

Alishia_9-1661966172730.png

7. Send an email > Attachment Name: (File Name= outputs('Convert_file') > Attachment Content: (Body= outputs('Convert_file'). It may be listed as "File content" in the Converted File section.

Alishia_10-1661966228306.png

 

 

 

 

 

 

 

 

takolota
Multi Super User
Multi Super User

It will take a bit of set-up, but you can use a CSV Parser template & some Excel List rows/Update row/Create row actions for this:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

There are also some Batch templates if you are dealing with a lot of data:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Excel-Batch-Create-Update-and-Upsert/td-...

Alphamolekuel
Frequent Visitor

Hey Guys , its my first post so don't be angry when my English isnt the best.

I am a newbe (working 6 months with Powerapps) and now i try to create a flow :

Flow is updating some cells and after updating the flow should copy the file an save this on a dynamic path .. 

I have the same issue, that the excel file has been opened to get the updated cells in the new file in the new sharepoint folder .. 

 

Now after 3 days i maybe found a workaround which could work, but its sad that the user have to pay extra..

After Updating the cells , i have to Open the filt with a Desktopflow, i built with this tutorial (link below) 

 

https://www.youtube.com/watch?v=4Qoh9t_ZvqI

 

After the Desktopflow is finished, which opens the data, refrehes it and save and closes xcel. I can save updated file to the new folder .. 

T he Bad Taste in this Story, The User have to book the Premium Flow License. In Germany it Costs 37,40 €/ per Month. Microsoft gives a 90 Days Trialversion .. I will test it and i hope  this Featuure will give me the Benefits i expect.

 

But maybe this Workaround can help some people 

 

NeonThunder
Advocate I
Advocate I

Microsoft need to address this.  We should be able to refresh data connections within a flow script, than having to open the Workbook.  Sure you can set it to refresh on open, but sometimes end users don't have access to the raw data and also a pivot refresh is almost always needed for multiple objects.

 

Feels like o365 is one big beta test at the expense of the users.

Yes it feels like nothing is already finished.. 
My Solution is that i Adress the excel file  then i fill the cells with the new variables . Afteer that i adress the same File . After the second Adress i brought a Delay with 1 Minute .. After the Delay i adress it again. Then are all cells up to date and i can copy the file to a new Folder. 
Its sad that we live in 2023 and me as an User cant get the updated File after updating the cells in the step before. 

Rolenlo
Regular Visitor

Seems to me that's this behavior where Microsoft does not support refresh... wish this could be changed eventually..

 

Troubleshoot Office Scripts running in Power Automate - Office Scripts | Microsoft Learn

ogend
Advocate II
Advocate II

I can't believe this is not a standard functionality. I found this idea submitted 3 years ago, can you please vote?

https://ideas.powerautomate.com/d365community/idea/8c426dcb-4390-445c-b148-b8a8219aa8a5

meanwhile, I got this working by setting up a PAD flow on a virtual machine and calling a PAD flow with a cloud flow, a huge pain in the neck, a premium license required.

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (3,773)