Excel.Query class

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

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.
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

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

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