How to write an office script that executes a predefined query to load data into a sheet?

user_537503 0
Viewed 1987

I have an excel workbook in which I have selected the "Get Data" option to import from an OData Feed. Using a public odata feed for testing. This defines a query in excel that can be edited/viewed through Power Query Editor.

I am new to office script. I would like to write an office script that loads the data from the query into a specified sheet. Could you please guide me through a sample code how to reference a query defined in the workbook and load its data into a sheet/range?

Also how can I configure this script to execute on file open event?

Thanks.

office-scripts office-js powerquery excel
1 Answers

Edit:

Office Scripts doesn't yet support refreshing of Power Query.

~~ original ~~

If your Excel file with pre-configured query is on OneDrive or SharePoint site, you could try this to see if it works. Below script refreshes all data connections.

function main(workbook: ExcelScript.Workbook) {
    workbook.refreshAllDataConnections()
}

In general, Excel for web doesn't yet support Power Query features such as adding, modifying queries. Thus it is not yet possible to record actions pertaining to Power Queries using Office Scripts. It is in the backlog of features for Office Scripts. For refresh action, the above script should pull the data you need. Please let us know if it doesn't work.

user_41624150
Related