# QUERY

## Content

This function returns data from a Data Manager table in a worksheet.

## Syntax

```text
QUERY(tableAndRows, [columns], [returnObject])
```

>type=note
> **Note:**
> To specify `returnObject` while omitting `columns`, leave the second argument empty. For example, `QUERY("Products?productId=1",,TRUE)`.

## Arguments

This function has the following arguments:

| Argument | Description |
| -------- | ----------- |
| tableAndRows | Required. The Data Manager table name, optionally followed by a row selector. This argument can be a text value or a reference that resolves to a table selector. |
| columns | Optional. The column name, an array of column names, or a reference that contains the column names to return. If omitted, all columns are returned. |
| returnObject | Optional. A logical value that specifies the result type. `FALSE` or an omitted value returns a two-dimensional array. `TRUE` returns the first matching row as an object, including related-table objects when relationships are configured. |

## Data types

* `tableAndRows` accepts a text value or a reference that resolves to a table selector.
* `columns` accepts a column name, an array of column names, or a reference that contains column names.
* `returnObject` accepts a logical value. Use `TRUE` to return the first matching row as an object, or `FALSE` to return a two-dimensional array.

The function returns the following types of results:

* A two-dimensional array when `returnObject` is `FALSE` or omitted.
* An object when `returnObject` is `TRUE`. The object contains the first matching row and can include related-table objects when relationships are configured.
* An empty result when no rows match the specified selector or filter.
* The `#VALUE!` error value when the Data Manager or specified table is unavailable.

## Remarks

You can use the following row selectors:

| Selector | Description | Example |
| -------- | ----------- | ------- |
| Table name | Returns all rows in the table. | `QUERY("Products")` |
| Row index | Returns the row at the specified zero-based row index. | `QUERY("Products#0")` |
| Primary key | Returns the row identified by the table primary key. | `QUERY("Products/1")` |
| Key-value filter | Returns rows whose field value exactly matches the specified value. | `QUERY("Products?categoryId=1")` |

You can select one or more columns by using a column name, an array, or a reference.

| Type | Example |
| ---- | ------- |
| One column | `QUERY("Products", "productName")` |
| Column array | `QUERY("Products?categoryId=1", {"productName", "unitPrice"})` |
| One-dimensional reference | `QUERY("Products?categoryId=1", A1:D1)` |

>type=note
> **Notes:**
>
> * The result follows dynamic-array or array-formula spill behavior when `returnObject` is `FALSE` or omitted. Enable dynamic arrays to spill the result into adjacent cells.
> * For remote tables, the function might evaluate asynchronously while the data is being retrieved. The formula is recalculated when the table data changes.

## Examples

The following examples use a Data Manager table named `Products`. The table contains fields such as `productId`, `productName`, `categoryId`, and `unitPrice`.
![image](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260810.e3ade9.png?width=600)
The following formula returns all rows and columns from the `Products` table.

```text
=QUERY("Products")
```

The following formula returns the `productName` and `unitPrice` columns for all rows.

```text
=QUERY("Products", {"productName","unitPrice"})
```
The following formula returns the selected columns for the row whose primary key is `1`.
 
```text
=QUERY("Products/1", {"productName","unitPrice"})
```
The following formula returns the `productName` and `unitPrice` values for products whose `categoryId` value is `1`.

```text
=QUERY("Products?categoryId=1", {"productName","unitPrice"})
```

The following formula returns the first product whose `productId` value is `1` as an object. This form can be used with the DataObject cell type.

```text
=QUERY("Products?productId=1",,TRUE)
```

The following formula returns a sorted list of unique category ID values. The result spills when dynamic arrays are enabled.

```text
=SORT(UNIQUE(QUERY("Products","categoryId")))
```

The following code sample uses the QUERY function with the DataObject cell type.

```JavaScript
// Set a DataObject cell type that uses the QUERY function to provide data.
sheet.setFormula(2, 1, '=QUERY("Products?productId=1",,TRUE)');
var cellType = new GC.Spread.Sheets.CellTypes.DataObject(
    '=IFERROR(@.productName & " " & @.unitPrice, "")'
);
sheet.setCellType(2, 1, cellType);
```

![image](https://cdn.mescius.io/document-site-files/images/b2223940-43c2-44cf-8eda-f5ab9acd84f0/image-20260810.3ad71f.png?width=400)