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

Schedule Data Refresh - SharePoint / Excel

Hi All, 

 

I have recently started to use SharePoint 365 to create Excel files for my organisation in which I am using data sources from other sources to populate via Power Query. 

 

Now the question is rather than manually enter these excel files each week to refresh the data is there a way I can set a refresh schedule? Is it possible to use something like Power BI or Microsoft Flow to set this up as I can't currently see any way of setting any schedule up directly within SharePoint?

 

Many thanks, 

Gaz

7 REPLIES 7
Super User
Super User

Re: Schedule Data Refresh - SharePoint / Excel

Hi Gaz,

I would say it's possible to do this with Flow. This thread describes how to export SharePoint data to Excel.

https://powerusers.microsoft.com/t5/General-Flow-Discussion/MS-Flow-Export-Sharepoint-List-content-t...

You can then combine it with this in order to run it on a schedule.

https://docs.microsoft.com/en-us/flow/run-scheduled-tasks

Gazsim
Level: Powered On

Re: Schedule Data Refresh - SharePoint / Excel

Hi and thanks for your reply.

 

Not sure however I think the first part refering to the flow of exporting SharePoint list content is relevant if you dont mind me saying? I just need to take the excel document which is saved in SharePoint and refresh the data without actually having to manually enter the document itself?

 

Thanks, 

Gaz

Super User
Super User

Re: Schedule Data Refresh - SharePoint / Excel

Hi Gaz

Sorry about that. I misunderstood what you said.

So just to summarise...

  • You're storing Excel files in a SharePoint list, possibly as an attachment
  • Each Excel spreadsheet contains external data connections
  • You want to automate the task of opening the Excel file from SharePoint, refreshing the external data, and saving the file back into SharePoint

For this, I think it's possible to use some combination of Flow, Excel macros/the 'Refresh data when opening the file' Excel option, PowerShell & on-premises gateway, or some other technique to accomplish this.

I think someone with more experience than me could assist you better - perhaps one of the guys from the BI forum.

Also, @ericonline here has good experience with workflow (not just Flow), and might be able to chip in.

Gazsim
Level: Powered On

Re: Schedule Data Refresh - SharePoint / Excel

Hi, yes that is pretty much it in a nutshell! :-)

 

Thanks again, 

Gaz

Community Support Team
Community Support Team

Re: Schedule Data Refresh - SharePoint / Excel

Hi @Gazsim 

 

It seems you want to refresh data on a schedule.

You can use flow to achieve that.

https://docs.microsoft.com/en-us/flow/run-scheduled-tasks

 

And could you please refrence this case?

https://community.powerbi.com/t5/Service/Schedule-refresh-from-a-Sharepoint-excel-file/td-p/475425

 

Best Regards.

Yumia

Gazsim
Level: Powered On

Re: Schedule Data Refresh - SharePoint / Excel

Hi Yumia, 

 

To be honest I'm not sure I totally follow these posts and what they are achieving? From what I am seeing it refers to publishing your excel file or data source from SharePoint to Power BI if I am correct and then setting a refresh schedule within the data set in Power BI. 

 

However how will this then refresh the data in my excel file located on SharePoint?

 

Sorry but not an expert in this area!

 

Thanks, 

Gary

Community Support Team
Community Support Team

Re: Schedule Data Refresh - SharePoint / Excel

Hi @Gazsim 

 

If you want to solve the issues of PowerBI's schedule setting.

You can post here:

https://community.powerbi.com/t5/Forums/ct-p/PBI_Comm_Forums

 

Best Regards.

Yumia

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 104 members 4,188 guests
Please welcome our newest community members: