3

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.

Andy Dufresne
  • 6,022
  • 7
  • 63
  • 113

1 Answers1

2

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.

Sudhi Ramamurthy
  • 2,358
  • 1
  • 10
  • 14
  • Refreshing connections manually on sharepoint online gives an error. I will try out to see if the script works. – Andy Dufresne Jul 11 '20 at 07:11
  • I tried this and it doesn't work. Wonder how it worked for you? I had the exact same code that you shared. I didn't give any problems in the logs too. I am assuming it must be failing as manual refresh on excel online also fails with an error - https://stackoverflow.com/questions/62916391/refresh-all-connections-button-click-on-excel-online-gives-an-error. Is there some sharepoint config needed to get this working? – Andy Dufresne Jul 16 '20 at 09:28
  • 1
    In 2020 you said "In general, Excel for web doesn't yet support Power Query features such as adding, modifying queries." - has this situation changed at all? I'm looking for a way to create power queries from an Office JS addin but the Excel.Query object still appears read-only in the documentation I found at https://learn.microsoft.com/en-us/javascript/api/excel/excel.query?view=excel-js-preview – MLdeS Jan 10 '22 at 19:04
  • @Sudhi Ramamurthy any update in 2023 about when you will be able to add Power Query queries with Office Scripts? – snth Aug 24 '23 at 19:34