Excel.Query class

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

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

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

Property Value

Remarks

[ API set: ExcelApi 1.14 ]

Gets the query loaded to object type.

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

Property Value

Remarks

[ API set: ExcelApi 1.14 ]

Specifies if the query loaded to the data model.

readonly loadedToDataModel: boolean;

Property Value

Remarks

[ API set: ExcelApi 1.14 ]

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

readonly name: string;

Property Value

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  "));
});

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

readonly refreshDate: Date;

Property Value

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  "));
});       

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

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  "));
});

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

delete(): void;

Returns

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

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

Returns

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

Returns

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

Returns

Refreshes the query. This only starts the refresh.

refresh(): void;

Returns

Remarks

[ API set: ExcelApi BETA (PREVIEW ONLY) ]

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's 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