cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mohoelx1
Level: Powered On

How to update linked spreadsheet based on List activity

So I have a SP List that I exported to Excel, and so have the query link in the spreadsheet. I can manually open the spreadsheet and "Refresh" the data, but is there a way to either configure the query to do this automatically (without user intervention) or to do the Refresh via Flow?

 

My need is to get the updated data from the List into a spreadsheet 2x daily and send the spreadsheet to an external application for consumption. I really don't want to have a person have to open file / refresh / save twice daily. Obviously I can trigger a Flow to grab and email the file upon update.

 

I think I cannot utilize the "add row" or "update row" Excel Online actions because the List has calculated columns, and it seems whatever I try, I cannot get a "table" defined that Flow can recognize. 

 

Thoughts?

3 REPLIES 3
jwhite
Level 8

Re: How to update linked spreadsheet based on List activity

Hi @mohoelx1,

To my knowledge you cannot refresh a query using Flow, although I may be mistaken.

If this needs to be consumed by an external program could you use Flow to generate a csv twice a day?

For example below I've set up a flow which runs at 10:00 and 16:00 every day and creates a CSV from the content of a sharepoint list:

Here I have arbitrarily set the recurrance, but obviously you could modify this to your needs.

RecurringExportTrigger.png

 

I then create a CSV table with custom columns, as leaving it with automatic will often include some of the hidden columns which you don't generally need:

RecurringExportCSV.png

At this point it depends what you need to do with the file. You could either create the file and save it to a location, or add it as an attachment to an email:

RecurringExportFile.png

Here I added the expression

formatdatetime(utcNow(),'HHmm')

to the name of the file so that is going to be unique and shows some indication of when it was run. You could add more here to further identify it if needs be.

I then get the export emailed to me as below:

RecurringExportEmail.png

 

Let me know if this helps or if this isn't suitable and I'll see what else I can come up with!

 

Full flow in spoiler:

Spoiler
RecurringExportFullFlow.png

 

Community Support Team
Community Support Team

Re: How to update linked spreadsheet based on List activity

 

Hi @mohoelx1 ,

 

Have you created an Excel table in the Excel file you specified?

161.PNG

Here you need to ensure that the Table has been pre-created in the Excel file before you could configure Add a row into a table action.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mohoelx1
Level: Powered On

Re: How to update linked spreadsheet based on List activity

Yes, there is a table defined. Of course the error cannot be copied nor is the screen large enough to see the whole thing, but it doesn't appear to be intended to convey anything to me as an end user anyway...

 

Table.png

Error.png

 

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 190 members 2,279 guests
Please welcome our newest community members: