How to Save a Workbook via an Office Script

Question

Overview

-----------

• My SQL Query and Related Office Script
  I am getting acquainted with Office Scripts and the ability to trigger those scripts via PowerAutomate flows.
• My Use Case
  I want to schedule a nightly refresh of a SQL query housed within my Excel workbook. I currently refresh it manually via a PowerShell script, which isn't ideal.
• Successful SQL Query Refresh Via Office Script
  I have created an Office Script within my Excel workbook which is configured to refresh that SQL query. I use the RefreshAllDataConnections() method to accomplish this refresh. It works perfectly; I run the script, triggering the query, and my workbook updates accordingly! Now I just need to automate that triggering of that script. Please see below.
• Triggering the Office Script via PowerAutomate I have configured a PowerAutomate flow to trigger the above Office Query. All tests of this PowerAutomate flow come back "successful".

Issue

---

• Workbook Not Saving The successfully refreshed Excel workbook is not saved after the query runs, which negates the purpose of the PowerAutomate flow. (Note: This is how I've interpreted the issue. It's possible that I am diagnosing it incorrectly!)
• Illustration Let's say that my query was run on 4/12 and the "Date Last Run" column therefore currently shows "4/12". When I run the PowerAutomate (and trigger the Office Script) on 4/13, and then open the Excel workbook to review the results, the "Date Last Run" column still shows "4/12", whereas I expected "4/13".

My Question

---

Is there some way that I can configure my Office Script to save my Excel workbook after the RefreshAllDataConnections() fires?

Answer 1

For me, the "RefreshAllDataConnections()" method is only executing when I have the workbook open in the desktop Excel client. It doesn't work when I test it from a browser or PowerAutomate.

To properly test it I got the script to write "Hello World" in a specific cell after attempting to refresh the data. This text appears correctly in every test scenario, even when the data hasn't refreshed in the line of code immediately above it.

I also tried leaving a 15 second delay in my Power Automate cloud flow between a "refresh" action and a "Hello World" action. Same issue.

I suspect that the RefreshAllDataConnections() method is currently only supported in the desktop client, which is a real shame.

Answer 2

A bit more searching has confirmed my theory that it's not currently supported:

https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#refresh-not-fully-supported-in-power-automate