Edit

Share via

Excel.Query class

  • Reference
Package:
excel

Represents a Power Query query.

Extends
OfficeExtension.ClientObject

Remarks

[ API set: ExcelApi 1.14 ]

Properties

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.
error

Gets the query error message from when the query was last refreshed.
loadedTo

Gets the query loaded to object type.
loadedToDataModel

Specifies if the query loaded to the data model.
name

Gets the name of the query. Query names cannot contain periods or quotation marks.
refreshDate

Gets the date and time when the query was last refreshed.
rowsLoadedCount

Gets the number of rows that were loaded when the query was last refreshed. If last refresh has errors the value will be -1.

Methods

delete()

Deletes the query and associated connection. Tables associated with the query will no longer be connected.
load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.
refresh()

Refreshes the query. This only starts the refresh.
toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Query object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.QueryData) that contains shallow copies of any loaded child properties from the original object.

Property Details

context

The request context associated with the object. This connects the add-in's process to the Office host application's process.

context: RequestContext;

Property Value

Excel.RequestContext

error

Gets the query error message from when the query was last refreshed.

readonly error: Excel.QueryError | "Unknown" | "None" | "FailedLoadToWorksheet" | "FailedLoadToDataModel" | "FailedDownload" | "FailedToCompleteDownload";

Property Value

Excel.QueryError | "Unknown" | "None" | "FailedLoadToWorksheet" | "FailedLoadToDataModel" | "FailedDownload" | "FailedToCompleteDownload"

Remarks

[ API set: ExcelApi 1.14 ]

loadedTo

Gets the query loaded to object type.

readonly loadedTo: Excel.LoadToType | "ConnectionOnly" | "Table" | "PivotTable" | "PivotChart";

Property Value

Excel.LoadToType | "ConnectionOnly" | "Table" | "PivotTable" | "PivotChart"

Remarks

[ API set: ExcelApi 1.14 ]

loadedToDataModel

Specifies if the query loaded to the data model.

readonly loadedToDataModel: boolean;

Property Value

boolean

Remarks

[ API set: ExcelApi 1.14 ]

name

Gets the name of the query. Query names cannot contain periods or quotation marks.

readonly name: string;

Property Value

string

Remarks

[ API set: ExcelApi 1.14 ]

Examples

// This function retrieves the query collection in a workbook, 
// loads the query items in that collection, and then
// logs the name of each query to the console.
await Excel.run(async (context) => {
    const queries = context.workbook.queries; 
    queries.load("items"); 
    await context.sync(); 

    console.log("Name of each query: ")
    console.log("  " + queries.items.map((query) => query.name).join("\n  "));
});

refreshDate

Gets the date and time when the query was last refreshed.

readonly refreshDate: Date;

Property Value

Date

Remarks

[ API set: ExcelApi 1.14 ]

Examples

// This function retrieves the query collection in a workbook, 
// loads the query items in that collection, and then
// logs the last refresh date of each query to the console.
await Excel.run(async (context) => {
    const queries = context.workbook.queries; 
    queries.load("items"); 
    await context.sync(); 

    console.log("Last refresh date of each query: ")
    console.log("  " + queries.items.map((query) => query.refreshDate).join("\n  "));
});

rowsLoadedCount

Gets the number of rows that were loaded when the query was last refreshed. If last refresh has errors the value will be -1.

readonly rowsLoadedCount: number;

Property Value

number

Remarks

[ API set: ExcelApi 1.14 ]

Examples

// This function retrieves the query collection in a workbook, 
// loads the query items in that collection, and then
// logs the number of rows loaded in each query.
await Excel.run(async (context) => {
    const queries = context.workbook.queries; 
    queries.load("items"); 
    await context.sync(); 

    console.log("Rows loaded from each query: ")
    console.log("  " + queries.items.map((query) => query.rowsLoadedCount).join("\n  "));
});

Method Details

delete()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Deletes the query and associated connection. Tables associated with the query will no longer be connected.

delete(): void;

Returns

void

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

load(options)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(options?: Excel.Interfaces.QueryLoadOptions): Excel.Query;

Parameters

options
Excel.Interfaces.QueryLoadOptions

Provides options for which properties of the object to load.

Returns

Excel.Query

load(propertyNames)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNames?: string | string[]): Excel.Query;

Parameters

propertyNames

string | string[]

A comma-delimited string or an array of strings that specify the properties to load.

Returns

Excel.Query

load(propertyNamesAndPaths)

Queues up a command to load the specified properties of the object. You must call context.sync() before reading the properties.

load(propertyNamesAndPaths?: {
            select?: string;
            expand?: string;
        }): Excel.Query;

Parameters

propertyNamesAndPaths

{ select?: string; expand?: string; }

propertyNamesAndPaths.select is a comma-delimited string that specifies the properties to load, and propertyNamesAndPaths.expand is a comma-delimited string that specifies the navigation properties to load.

Returns

Excel.Query

refresh()

Note

This API is provided as a preview for developers and may change based on feedback that we receive. Do not use this API in a production environment.

Refreshes the query. This only starts the refresh.

refresh(): void;

Returns

void

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

toJSON()

Overrides the JavaScript toJSON() method in order to provide more useful output when an API object is passed to JSON.stringify(). (JSON.stringify, in turn, calls the toJSON method of the object that is passed to it.) Whereas the original Excel.Query object is an API object, the toJSON method returns a plain JavaScript object (typed as Excel.Interfaces.QueryData) that contains shallow copies of any loaded child properties from the original object.

toJSON(): Excel.Interfaces.QueryData;

Returns

Excel.Interfaces.QueryData