- SpreadJS Overview
- Getting Started
- JavaScript Frameworks
- Best Practices
- Features
- SpreadJS Designer
- SpreadJS Designer Component
- Touch Support
-
Formula Reference
- Formula Overview
-
Formula Functions
- Barcode Functions
- Compatibility Functions
- Database Functions
- Date and Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Logical Functions
- Lookup and Reference Functions
- Math and Trigonometric Functions
- Statistical Functions
- Sparkline Functions
- Text Functions
- Web Functions
- Other Functions
- RegEx Functions
- Import and Export Reference
- Frequently Used Events
- API Documentation
- Release Notes
QUERY
This function returns data from data manager tables in a worksheet.
Syntax
QUERY(tableAndRows, columns)
Arguments
This function has these arguments:
Argument | Description |
|---|---|
tableAndRows | The data table name or data table row. |
columns | [Optional] The field name. |
Data types
Accepts string data.
Returns the following types of data:
Return Types | Examples |
|---|---|
The whole table |
|
The whole column |
|
The data |
|
The certain row |
|
Remarks
Users can use the following simple row filters:
Type | Examples |
|---|---|
By row index |
|
By row primary key |
|
By key-value filter |
|
Users can select many columns by the use of array.
Type | Examples |
|---|---|
By column array |
|
By one dimension reference |
|
Example
The following GIF illustrates the use of the QUERY function to show item information according to the tax rate.
The following code sample shows how to implement the QUERY function in a worksheet.
// Create a new sheet
var sheet1 = new GC.Spread.Sheets.Worksheet();
sheet1.name("TS Reference in Formula");
spread.addSheet(0, sheet1);
spread.options.allowDynamicArray = true;
// unique tax rates
sheet1.setValue(3, 0, 'Different Tax Rates are:-')
sheet1.setFormula(3, 1, '=UNIQUE(TableSheet1[Tax Rate])'); // it returns a dynamic array and we have already set allowDynamicArray to true initially
// set a datavalidator of unique values
var dv = GC.Spread.Sheets.DataValidation.createFormulaListValidator('=$B$4#');
sheet1.setDataValidator(3, 3, dv);
// set value in cell from datavalidator
sheet1.setValue(3, 3, 20);
// apply tableStyle for a range
applyTableStyleForRange(sheet1, 5, 3, 400, 3);
sheet1.setValue(3, 2, 'Select a tax rate to Query the TableSheet')
sheet1.setArray(5, 3, [["stockItem", "unitPrice", "stockItemKey"]]);
// apply QUERY function
sheet1.setFormula(6, 3, '=QUERY("myTable?taxRate="&D4, D6:F6)');