How to Save a Workbook via an Office Script

Seneca123 15 Reputation points
2024-04-14T13:32:50.7633333+00:00

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?

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,916 questions
0 comments No comments
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.