Skip to content

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 dataProvider plugin works.
  • Make sure your OData service accepts requests from your app’s origin (CORS).
  • If your app sends a Content-Security-Policy header, add the service’s origin to its connect-src directive.
  1. Load pages, sorting, and filters from the service

    The dataProvider option calls fetchRows whenever the page, the sort order, or the column filters change. fetchRows builds an OData URL from those parameters and returns the rows with the total row count.

    Vue
    <script setup lang="ts">
    import { HotTable } from '@handsontable/vue3';
    import { registerAllModules } from 'handsontable/registry';
    import type { GridSettings } from 'handsontable/settings';
    import type { DataProviderQueryParameters, DataProviderFetchOptions } 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 ');
    }
    async function fetchRows(
    { page, pageSize, sort, filters }: DataProviderQueryParameters,
    { signal }: DataProviderFetchOptions,
    ) {
    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'] };
    }
    // A plain object, not a `ref()`, so Vue never re-applies the settings.
    const hotSettings: GridSettings = {
    dataProvider: {
    rowId: 'ProductID',
    fetchRows,
    // 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/
    };
    </script>
    <template>
    <div id="example1">
    <HotTable :settings="hotSettings" />
    </div>
    </template>

    Each query parameter of fetchRows maps to one OData query option:

    fetchRows parameterOData query option
    page, pageSize$top=pageSize and $skip=(page - 1) * pageSize
    sort$orderby=<column> asc or $orderby=<column> desc
    filters$filter=<expression>
    (total row count)$count=true, read back from @odata.count

    The toODataFilter() function builds the $filter expression:

    • Each column filter becomes one group in parentheses. The groups are joined with and.
    • Inside a group, the conditions are joined with and or or, depending on the operator the user picks in the column menu.
    • The CONDITIONS table maps each condition name to an OData operator or function, for example begins_with to startswith().
    • Text values are wrapped in single quotes, and any single quote inside a value is doubled. Without that, a value like Sir Rodney's breaks 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 CONDITIONS for the column types you use.

    OData compares null differently from the Filters plugin. Is empty matches null values only, not empty strings. Is not equal to and Does not contain leave out rows whose value is null. Adjust the matching CONDITIONS entries if your data has empty values.

    With the dataProvider plugin, 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.

  2. Send changes to a writable service

    The dataProvider plugin needs all five keys: rowId, fetchRows, onRowsCreate, onRowsUpdate, and onRowsRemove. The Northwind service is read-only, so the example passes callbacks that do nothing and sets readOnly: 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 example Customers('ALFKI'). To send all changes in one request, use your service’s $batch endpoint. See Server-side data: CRUD for the payload of each callback.

  3. Fill an autocomplete cell from the service

    An autocomplete cell can ask the service for suggestions while the user types. Pass a function as the source option. The function sends the typed text in a contains() 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.

    Vue
    <script setup lang="ts">
    import { HotTable } from '@handsontable/vue3';
    import { registerAllModules } from 'handsontable/registry';
    import type { GridSettings } from 'handsontable/settings';
    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 hotSettings: GridSettings = {
    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/
    };
    </script>
    <template>
    <div id="example2">
    <HotTable :settings="hotSettings" />
    </div>
    </template>

OData V2 services

The examples use OData V4. For an OData V2 service, make these changes:

  • Add $format=json (or an Accept: application/json header), because V2 services return XML by default.
  • Replace $count=true with $inlinecount=allpages, and read the total from Number(json.d.__count), because V2 returns it as a string.
  • Read the rows from json.d.results instead of json.value.
  • Replace contains(field,'text') with substringof('text',field), and not contains(...) with not substringof(...).

What you learned

  • How to map the fetchRows page, 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 dataProvider configuration complete for a read-only service.
  • How to fill an autocomplete cell from an OData query.

Next steps