data connection refresh excel with flow

[jendebett]

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?

[MohamedEssam]

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

[MohamedEssam]

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

[Alishia]

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

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

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.

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:

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

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

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

4. Delay 2: 2 Minutes

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.

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

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.

[takolota]

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]

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]

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.

[Alphamolekuel]

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]

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]

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.