I have an Excel Online (Business) workbook. There is a query on it which will populate the Query1 table and I have the properties set so that when it is manually opened, the data does refresh from the source. I am looking for a way to have my Power Automate flow refresh that data, essentially replicating the manual opening of the file, so that my flow has the latest data based on the query of my data source (PostgresQL query over ODBC connection). My flow currently uses “List rows present in a table” to retrieve the data, but it does not force any type of update from the data source. I’ve tried many different options such as trying to run an office script via the Run Script action, setting up a desktop flow which gets invoked from a cloud flow, but absolutely nothing works to refresh the table from the data source. I’ve tried every possible combination that Copilot comes up with and to date the results are zip, nada, nothing.
Is there anyone who has successfully done this or does anyone have any suggestions on how exactly this can be done. I’m about at wits end on it.
Thanks in advance!
I’ve done this - it does work, but not with Power Query as the data connector. You can only use Power Automate & Office Scripts to refresh data that connects to a Power BI datasource (Data Tab > Get Data > From Power Platform > Power BI), and theres a bunch of caveats around that too.
You might have more luck querying your database, and sending the updated data to your spreadsheet.
Thank you for the info Bianca. I have created a powershell script which will do the update of the workbook and now I’ll see if I can run that via PA desktop on a scheduled flow in PA cloud. The powershell script works well per my testing so I’ll see what happens with desktop and cloud flows. I may have to use Task Scheduler in order to run the powershell script. Not 100% certain yet.
Below is the powershell script if anyone is interested in using it.
Define the ODBC connection string
$connectionString = “DSN=DBdsnconnect;Uid=XXXXXXXX;Pwd=XXXXXXXX;”
Define the SQL query
$sqlQuery = “ENTER QUERY HERE;”
Define the path to the Excel file in OneDrive
$excelFilePath = “C:{one drive location}\AdminEmailList.xlsx”
Load the necessary assemblies for Excel
Add-Type -AssemblyName “Microsoft.Office.Interop.Excel”
Create a new Excel application
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Open($excelFilePath)
$worksheet = $workbook.Sheets.Item(“Query1”)
Open the ODBC connection
$connection = New-Object System.Data.Odbc.OdbcConnection
$connection.ConnectionString = $connectionString
$connection.Open()
Execute the SQL query
$command = $connection.CreateCommand()
$command.CommandText = $sqlQuery
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter $command
$dataTable = New-Object System.Data.DataTable
$adapter.Fill($dataTable)
Clear the existing data in the worksheet
$worksheet.UsedRange.Clear()
Populate the worksheet with the new data, including headers
$row = 1
Add column headers
foreach ($column in $dataTable.Columns) {
$worksheet.Cells.Item($row, $column.Ordinal + 1) = $column.ColumnName
}
$row++
Add data rows
foreach ($dataRow in $dataTable.Rows) {
$col = 1
foreach ($item in $dataRow.ItemArray) {
$worksheet.Cells.Item($row, $col) = $item
$col++
}
$row++
}
Create a table in the worksheet
$range = $worksheet.UsedRange
$table = $worksheet.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $range, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes)
$table.Name = “Query1”
Save and close the workbook
$workbook.Save()
$workbook.Close()
$excel.Quit()
Release the COM objects
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
Close the ODBC connection
$connection.Close()
Write-Output “Exiting script.”
Exit