The new Office JavaScript API for Power Query read-only data endpoint is designated for Excel and aligns with our vision of creating a familiar, robust cross-platform user experience.\nThese APIs allow you to retrieve data related to Power Query queries you have in your Excel workbooks.<\/p>\n
Here are two examples of how you can use the new Office JS APIs for Excel.<\/p>\n
1. If you want to see the query parameters name<\/strong>, last refresh date<\/strong>,\nand rows loaded count<\/strong>, call the API as shown in the following code sample.<\/p>\n 2. You can also count all of the queries in the workbook. For example, you can call the following Excel API.<\/p>\n For more details, see the documentation for Excel.Query class<\/a> and Excel.QueryCollection class<\/a>.<\/p>\n We\u2019re looking into adding more Power Query capabilities via Office JavaScript APIs, such as refreshing queries and editing them.<\/p>\n We encourage you to try out this API as we continue to work towards improving user productivity. For any feedback, suggestions, or questions on the API, let us know by leaving a comment, or using the Feedback button in the top corner in Excel – add #PowerQuery in your feedback so that we can find it easily.<\/p>\n Happy coding!<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" New Office JavaScript APIs allow you to retrieve data related to Power Query queries you have in your Excel workbooks.<\/p>\n","protected":false},"author":87670,"featured_media":10223,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[11],"tags":[18,168],"class_list":["post-10220","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-office-add-ins","tag-excel","tag-office-javascript-api"],"acf":[],"blog_post_summary":" New Office JavaScript APIs allow you to retrieve data related to Power Query queries you have in your Excel workbooks.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/posts\/10220","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/users\/87670"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/comments?post=10220"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/posts\/10220\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/media\/10223"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/media?parent=10220"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/categories?post=10220"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/microsoft365dev\/wp-json\/wp\/v2\/tags?post=10220"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\r\nasync function run() {\r\n await Excel.run(async (context) => {\r\n const queries = context.workbook.queries;\r\n queries.load(\"items\");\r\n await context.sync();\r\n console.log(\"rows loaded: \" + queries.items.map((q) => q.rowsLoadedCount));\r\n console.log(\"last refresh dates: \" + queries.items.map((q) => q.refreshDate));\r\n });\r\n}\r\n<\/code><\/pre>\nconst count = queries.getCount();<\/pre>\n
What\u2019s Next in Office JavaScript API for Excel?<\/h3>\n
More Excel development resources<\/h3>\n
\n