Load data from an OData service
In this tutorial, you will connect Handsontable DataGrid to an OData V4 service. You will learn how to turn the grid’s page, sort, and filter state into OData query options, and how to fill an autocomplete cell with values from the same service.
The examples call the public, read-only Northwind demo service. Replace SERVICE_URL with the address of your own entity set.
Before you begin
- Read the Server-side data guide to learn how the
dataProviderplugin works. - Make sure your OData service accepts requests from your app’s origin (CORS).
- If your app sends a
Content-Security-Policyheader, add the service’s origin to itsconnect-srcdirective.
Load pages, sorting, and filters from the service
The
dataProvideroption callsfetchRowswhenever the page, the sort order, or the column filters change.fetchRowsbuilds an OData URL from those parameters and returns the rows with the total row count.JavaScript import Handsontable from 'handsontable/base';import { registerAllModules } from 'handsontable/registry';registerAllModules();const SERVICE_URL = 'https://services.odata.org/V4/Northwind/Northwind.svc/Products';const NUMERIC_FIELDS = ['UnitPrice', 'UnitsInStock'];// Each Handsontable filter condition becomes one OData `$filter` expression.const CONDITIONS = {eq: (f, a) => `${f} eq ${a}`,neq: (f, a) => `${f} ne ${a}`,gt: (f, a) => `${f} gt ${a}`,gte: (f, a) => `${f} ge ${a}`,lt: (f, a) => `${f} lt ${a}`,lte: (f, a) => `${f} le ${a}`,between: (f, a, b) => `${f} ge ${a} and ${f} le ${b}`,not_between: (f, a, b) => `${f} lt ${a} or ${f} gt ${b}`,contains: (f, a) => `contains(${f},${a})`,not_contains: (f, a) => `not contains(${f},${a})`,begins_with: (f, a) => `startswith(${f},${a})`,ends_with: (f, a) => `endswith(${f},${a})`,empty: (f) => `${f} eq null`,not_empty: (f) => `${f} ne null`,};function toODataFilter(filters) {return (filters ?? []).map(({ prop, operation, conditions }) => {const isNumeric = NUMERIC_FIELDS.includes(prop);// Compare text in lower case, because the Filters plugin ignores case.const field = isNumeric ? prop : `tolower(${prop})`;const toLiteral = (value) =>isNumeric ? parseFloat(String(value)) : `'${String(value).toLowerCase().replace(/'/g, "''")}'`;const parts = conditions.flatMap(({ name = '', args }) => {// Like the Filters plugin, accept the two bounds of `between` in either order.const values = isNumeric ? args.map(toLiteral).sort((x, y) => Number(x) - Number(y)) : args.map(toLiteral);// Skip a condition the table does not map, or a numeric input that is not a number.const isMapped =Object.hasOwn(CONDITIONS, name) && values.every((v) => typeof v === 'string' || Number.isFinite(v));return isMapped ? [`(${CONDITIONS[name](field, ...values)})`] : [];});return parts.length ? `(${parts.join(operation === 'conjunction' ? ' and ' : ' or ')})` : '';}).filter(Boolean).join(' and ');}const container = document.querySelector('#example1');new Handsontable(container, {dataProvider: {rowId: 'ProductID',fetchRows: async ({ page, pageSize, sort, filters }, { signal }) => {const filter = toODataFilter(filters);const query = new URLSearchParams({$count: 'true',$top: `${pageSize}`,$skip: `${(page - 1) * pageSize}`,...(sort ? { $orderby: `${sort.prop} ${sort.order}` } : {}),...(filter ? { $filter: filter } : {}),});const response = await fetch(`${SERVICE_URL}?${query}`, { signal });if (!response.ok) {throw new Error(`HTTP ${response.status}`);}const json = await response.json();return { rows: json.value, totalRows: json['@odata.count'] };},// The Northwind demo service is read-only, so the write callbacks do nothing.onRowsCreate: async () => [],onRowsUpdate: async () => {},onRowsRemove: async () => {},},colHeaders: ['Product', 'Quantity per unit', 'Unit price', 'Units in stock'],columns: [{ data: 'ProductName', width: 240 },{ data: 'QuantityPerUnit', width: 180 },{ data: 'UnitPrice', type: 'numeric', numericFormat: { style: 'currency', currency: 'USD' } },{ data: 'UnitsInStock', type: 'numeric' },],readOnly: true,pagination: { pageSize: 10 },columnSorting: true,filters: true,dropdownMenu: true,emptyDataState: true,notification: true,height: 'auto',// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});TypeScript import Handsontable from 'handsontable/base';import { registerAllModules } from 'handsontable/registry';import type { DataProviderQueryParameters } from 'handsontable/plugins/dataProvider';registerAllModules();const SERVICE_URL = 'https://services.odata.org/V4/Northwind/Northwind.svc/Products';const NUMERIC_FIELDS = ['UnitPrice', 'UnitsInStock'];// Each Handsontable filter condition becomes one OData `$filter` expression.const CONDITIONS: Record<string, (field: string, a?: unknown, b?: unknown) => string> = {eq: (f, a) => `${f} eq ${a}`,neq: (f, a) => `${f} ne ${a}`,gt: (f, a) => `${f} gt ${a}`,gte: (f, a) => `${f} ge ${a}`,lt: (f, a) => `${f} lt ${a}`,lte: (f, a) => `${f} le ${a}`,between: (f, a, b) => `${f} ge ${a} and ${f} le ${b}`,not_between: (f, a, b) => `${f} lt ${a} or ${f} gt ${b}`,contains: (f, a) => `contains(${f},${a})`,not_contains: (f, a) => `not contains(${f},${a})`,begins_with: (f, a) => `startswith(${f},${a})`,ends_with: (f, a) => `endswith(${f},${a})`,empty: (f) => `${f} eq null`,not_empty: (f) => `${f} ne null`,};function toODataFilter(filters: DataProviderQueryParameters['filters']): string {return (filters ?? []).map(({ prop, operation, conditions }) => {const isNumeric = NUMERIC_FIELDS.includes(prop);// Compare text in lower case, because the Filters plugin ignores case.const field = isNumeric ? prop : `tolower(${prop})`;const toLiteral = (value: unknown) => (isNumeric ? parseFloat(String(value)) : `'${String(value).toLowerCase().replace(/'/g, "''")}'`);const parts = conditions.flatMap(({ name = '', args }) => {// Like the Filters plugin, accept the two bounds of `between` in either order.const values = isNumeric ? args.map(toLiteral).sort((x, y) => Number(x) - Number(y)) : args.map(toLiteral);// Skip a condition the table does not map, or a numeric input that is not a number.const isMapped = Object.hasOwn(CONDITIONS, name) && values.every((v) => typeof v === 'string' || Number.isFinite(v));return isMapped ? [`(${CONDITIONS[name](field, ...values)})`] : [];});return parts.length ? `(${parts.join(operation === 'conjunction' ? ' and ' : ' or ')})` : '';}).filter(Boolean).join(' and ');}const container = document.querySelector<HTMLDivElement>('#example1')!;new Handsontable(container, {dataProvider: {rowId: 'ProductID',fetchRows: async ({ page, pageSize, sort, filters }, { signal }) => {const filter = toODataFilter(filters);const query = new URLSearchParams({$count: 'true',$top: `${pageSize}`,$skip: `${(page - 1) * pageSize}`,...(sort ? { $orderby: `${sort.prop} ${sort.order}` } : {}),...(filter ? { $filter: filter } : {}),});const response = await fetch(`${SERVICE_URL}?${query}`, { signal });if (!response.ok) {throw new Error(`HTTP ${response.status}`);}const json = await response.json();return { rows: json.value, totalRows: json['@odata.count'] };},// The Northwind demo service is read-only, so the write callbacks do nothing.onRowsCreate: async () => [],onRowsUpdate: async () => {},onRowsRemove: async () => {},},colHeaders: ['Product', 'Quantity per unit', 'Unit price', 'Units in stock'],columns: [{ data: 'ProductName', width: 240 },{ data: 'QuantityPerUnit', width: 180 },{ data: 'UnitPrice', type: 'numeric', numericFormat: { style: 'currency', currency: 'USD' } },{ data: 'UnitsInStock', type: 'numeric' },],readOnly: true,pagination: { pageSize: 10 },columnSorting: true,filters: true,dropdownMenu: true,emptyDataState: true,notification: true,height: 'auto',// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});Each query parameter of
fetchRowsmaps to one OData query option:fetchRowsparameterOData query option page,pageSize$top=pageSizeand$skip=(page - 1) * pageSizesort$orderby=<column> ascor$orderby=<column> descfilters$filter=<expression>(total row count) $count=true, read back from@odata.countThe
toODataFilter()function builds the$filterexpression:- Each column filter becomes one group in parentheses. The groups are joined with
and. - Inside a group, the conditions are joined with
andoror, depending on the operator the user picks in the column menu. - The
CONDITIONStable maps each condition name to an OData operator or function, for examplebegins_withtostartswith(). - Text values are wrapped in single quotes, and any single quote inside a value is doubled. Without that, a value like
Sir Rodney'sbreaks the query. - Text comparisons go through
tolower(), so they ignore case the same way the Filters plugin does. - Numeric columns, listed in
NUMERIC_FIELDS, send unquoted numbers. - The table maps the conditions of text and numeric columns. A condition it does not map, such as a date condition, is left out of the query, and so is a numeric condition whose input is not a number. Add entries to
CONDITIONSfor the column types you use.
OData compares
nulldifferently from the Filters plugin. Is empty matchesnullvalues only, not empty strings. Is not equal to and Does not contain leave out rows whose value isnull. Adjust the matchingCONDITIONSentries if your data has empty values.With the
dataProviderplugin, the column menu offers condition filters only. Filter by value is not available, because the grid holds only the current page. See Sorting and filtering.- Each column filter becomes one group in parentheses. The groups are joined with
Send changes to a writable service
The
dataProviderplugin needs all five keys:rowId,fetchRows,onRowsCreate,onRowsUpdate, andonRowsRemove. The Northwind service is read-only, so the example passes callbacks that do nothing and setsreadOnly: true.For a writable OData service, map each callback to one HTTP request per row:
// `fetch()` resolves on HTTP errors, so throw to let the grid report the error.// A failed update is also rolled back in the grid.async function send(url, init) {const response = await fetch(url, init);if (!response.ok) {throw new Error(`HTTP ${response.status}`);}}const json = { 'Content-Type': 'application/json' };// In the `dataProvider` option:onRowsCreate: async ({ rowsAmount }) => {const body = JSON.stringify({ ProductName: 'New product' });for (let i = 0; i < rowsAmount; i++) {await send(SERVICE_URL, { method: 'POST', headers: json, body });}// The grid fetches the current page again after a create.return [];},onRowsUpdate: async (rows) => {await Promise.all(rows.map(({ id, changes }) => send(`${SERVICE_URL}(${id})`, {method: 'PATCH',headers: json,body: JSON.stringify(changes),})));},onRowsRemove: async (rowIds) => {await Promise.all(rowIds.map((id) => send(`${SERVICE_URL}(${id})`, { method: 'DELETE' })));},The
Products(5)key syntax fits a numeric key. For a text key, quote it and double any single quote inside it, for exampleCustomers('ALFKI'). To send all changes in one request, use your service’s$batchendpoint. See Server-side data: CRUD for the payload of each callback.Fill an autocomplete cell from the service
An
autocompletecell can ask the service for suggestions while the user types. Pass a function as thesourceoption. The function sends the typed text in acontains()filter and limits the response with$top=10.The example sets
strict: false, so the cell accepts any text and no validation request runs. The editor ignores responses to queries the user has already typed past, but it still sends one request per keystroke. For a production service, add a short debounce before the request.Double-click a cell in the Product column and type, for example,
cha.JavaScript import Handsontable from 'handsontable/base';import { registerAllModules } from 'handsontable/registry';registerAllModules();const SERVICE_URL = 'https://services.odata.org/V4/Northwind/Northwind.svc/Products';// Ask the OData service for up to 10 product names that contain the typed text.async function fetchProductNames(query) {const term = query.toLowerCase().replace(/'/g, "''");const params = new URLSearchParams({$select: 'ProductName',$top: '10',$orderby: 'ProductName',$filter: `contains(tolower(ProductName),'${term}')`,});const response = await fetch(`${SERVICE_URL}?${params}`);if (!response.ok) {throw new Error(`HTTP ${response.status}`);}const json = await response.json();return json.value.map((product) => product.ProductName);}const container = document.querySelector('#example2');new Handsontable(container, {data: [{ order: 'SO-10248', product: 'Queso Cabrales', quantity: 12 },{ order: 'SO-10249', product: 'Tofu', quantity: 9 },{ order: 'SO-10250', product: 'Manjimup Dried Apples', quantity: 35 },{ order: 'SO-10251', product: 'Ravioli Angelo', quantity: 6 },{ order: 'SO-10252', product: 'Chang', quantity: 25 },],colHeaders: ['Order', 'Product', 'Quantity'],columns: [{ data: 'order', readOnly: true },{data: 'product',type: 'autocomplete',width: 240,strict: false,// Show no suggestions when the request fails.source(query, process) {fetchProductNames(query).then(process, () => process([]));},},{ data: 'quantity', type: 'numeric' },],height: 'auto',// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});TypeScript import Handsontable from 'handsontable/base';import { registerAllModules } from 'handsontable/registry';registerAllModules();const SERVICE_URL = 'https://services.odata.org/V4/Northwind/Northwind.svc/Products';// Ask the OData service for up to 10 product names that contain the typed text.async function fetchProductNames(query: string): Promise<string[]> {const term = query.toLowerCase().replace(/'/g, "''");const params = new URLSearchParams({$select: 'ProductName',$top: '10',$orderby: 'ProductName',$filter: `contains(tolower(ProductName),'${term}')`,});const response = await fetch(`${SERVICE_URL}?${params}`);if (!response.ok) {throw new Error(`HTTP ${response.status}`);}const json = await response.json();return json.value.map((product: { ProductName: string }) => product.ProductName);}const container = document.querySelector<HTMLDivElement>('#example2')!;new Handsontable(container, {data: [{ order: 'SO-10248', product: 'Queso Cabrales', quantity: 12 },{ order: 'SO-10249', product: 'Tofu', quantity: 9 },{ order: 'SO-10250', product: 'Manjimup Dried Apples', quantity: 35 },{ order: 'SO-10251', product: 'Ravioli Angelo', quantity: 6 },{ order: 'SO-10252', product: 'Chang', quantity: 25 },],colHeaders: ['Order', 'Product', 'Quantity'],columns: [{ data: 'order', readOnly: true },{data: 'product',type: 'autocomplete',width: 240,strict: false,// Show no suggestions when the request fails.source(query, process) {fetchProductNames(query).then(process, () => process([]));},},{ data: 'quantity', type: 'numeric' },],height: 'auto',// licenseKey: 'your-license-key', // get a key at https://handsontable.com/pricing/});
OData V2 services
The examples use OData V4. For an OData V2 service, make these changes:
- Add
$format=json(or anAccept: application/jsonheader), because V2 services return XML by default. - Replace
$count=truewith$inlinecount=allpages, and read the total fromNumber(json.d.__count), because V2 returns it as a string. - Read the rows from
json.d.resultsinstead ofjson.value. - Replace
contains(field,'text')withsubstringof('text',field), andnot contains(...)withnot substringof(...).
What you learned
- How to map the
fetchRowspage, sort, and filter parameters to$top,$skip,$orderby, and$filter. - How to quote OData text values and keep numeric values unquoted.
- How to read the total row count from
@odata.count. - How to keep the
dataProviderconfiguration complete for a read-only service. - How to fill an autocomplete cell from an OData query.