Shared File Connection Refresh Sharepoint

Question

I have CSVs uploaded to a Sharepoint site 3x/day and I have established a connection to that Sharepoint folder from various Excel workbooks stored in the same Sharepoint site. These workbooks are shared with members of our organization as well as 3rd party contractors outside of our org. I would like that connection to refresh without me or someone else in my organization opening the file every morning.

The 3rd party contractors are unable to open the file in the desktop app which prevents them from refreshing the data connection on their own. Is there a better way to load this data into a shared Excel file so that it refreshes on its own? Or is there some setting I can change to improve this update with a shared file?

So far I have tried:

Changing the Power Query Properties to "Refresh data when opening the file" (seems to cause problems with multiple users accessing and the data connection doesn't refresh anyway)

Power Automate flow that opens the spreadsheet from Sharepoint connection in Windows Explorer, clicks the tab with the query, clicks Refresh, waits, saves document (fails on Task Scheduler due to my computer being off or not restarting overnight if I keep it on. Seems like a primitive solution anyway)

VBA on Macros enabled Workbook that refreshes when user clicks on tab (still testing this out, but doubtful my org will allow sharing .xlsm with 3rd party)

Considering:

Changing the sensitivity from Internal to Public. Through testing with my personal email and experiencing what the 3rd party is experiencing through repeated log ins, multi factor authentication and other validation it seems my org is placing excessive restrictions on our Office 365 documents. Why can't I establish a trusted 3rd party user and that log in be retained?

Using the Autosave function and another power automate task on task scheduler that restarts my computer before running the task described above.

Thanks in advance for any direction. This is fairly straightforward in Smartsheet. It would be nice if there was a clean way to load data daily into a share Excel file.

Ben

jkpieterse · Answer

BenDrury4&nbsp;Have you tried creating an Office script that refreshes all connections and then creating a power Automate flow that runs that script against the files in question?function main(workbook: ExcelScript.Workbook) {
	// Refresh all data connections
	workbook.refreshAllDataConnections();
}

Forum Discussion

Shared File Connection Refresh Sharepoint

Share

Resources