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?

43 REPLIES 43
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
Super User
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-...

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (1,944)