Office JavaScript API for Power Query read-only data now available for Excel

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. These APIs allow you to retrieve data related to Power Query queries you have in your Excel workbooks.

Code samples: Use Office JavaScript API for Power Query

Here are two examples of how you can use the new Office JS APIs for Excel.

1. If you want to see the query parameters name, last refresh date, and rows loaded count, call the API as shown in the following code sample.


async function run() {
    await Excel.run(async (context) => {
        const queries = context.workbook.queries;
        queries.load("items");
        await context.sync();
        console.log("rows loaded: " + queries.items.map((q) => q.rowsLoadedCount));
        console.log("last refresh dates: " + queries.items.map((q) => q.refreshDate));
    });
}

2. You can also count all of the queries in the workbook. For example, you can call the following Excel API.

const count = queries.getCount();

For more details, see the documentation for Excel.Query class and Excel.QueryCollection class.

What’s Next in Office JavaScript API for Excel?

We’re looking into adding more Power Query capabilities via Office JavaScript APIs, such as refreshing queries and editing them.

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.