How-to

How to pull data from a REST API into DataLens

Call a JSON endpoint with whatever authentication it demands, tell DataLens where the rows live inside the response, and get a dataset.

Request beta access Open the app

What you will do

You will configure a REST endpoint, supply its authentication, identify the array of records inside the JSON response, and load that array as a dataset.

The awkward part of any API is that the rows are rarely at the top level — they are under a key such as data or results. The Data Path field is how you point at them.

When this is useful

  • The system you need data from has an API but no database you can reach.
  • A SaaS tool exposes reporting data as JSON and you want it beside your other datasets.
  • You want to profile or chart API data without writing an integration script first.
  • You are prototyping and need to see the shape of an API response as a table.

Before you start

  • The endpoint URL — The full URL you would call, including any query string it needs.
  • Credentials, if the API needs them — A bearer token, an API key and its header name, a username and password, or OAuth 2.0 client credentials with a token URL.
  • Knowing where the rows are — Call the endpoint once in any tool and look at the response. If the records sit under a key, note it — you will need it for Data Path.

Steps

  1. Open Data → Connections → REST API

    Select Data, then Connections, then the REST API tab.

    The REST API Connector form, with Source Name, Dataset Name, Method and Endpoint URL.

  2. Name the source and the dataset

    Source Name identifies the connection. Dataset Name is what the loaded data will be called under Datasets.

    Keeping them distinct matters when one API feeds several datasets from different endpoints.

  3. Set the method and endpoint

    Choose the HTTP Method — GET for most read APIs, POST where the API expects a request body.

    Enter the full Endpoint URL, including any query parameters the API requires.

  4. Point at the rows with Data Path

    Data Path is optional and is where most API loads succeed or fail. Leave it empty if the response is already a top-level array of objects.

    If the records are nested — a response shaped like { "data": [ ... ] } — enter that key so DataLens reads the array rather than the wrapper.

    After testing, a preview showing one row per record rather than a single row containing the whole response.

  5. Configure authentication

    Choose the Authentication type the API uses: No Auth, Bearer Token, API Key, Basic Auth or OAuth 2.0.

    Bearer Token takes a token. API Key takes both the key and the Header Name to send it in. Basic Auth takes a username and password. OAuth 2.0 takes a Token URL, Client ID, Client Secret and Scope, and fetches a token for you.

    For OAuth 2.0, a confirmation that a token was fetched.

  6. Test, then load

    Test the call first. DataLens makes a real request and shows you what came back, so you can confirm the auth works and the Data Path found the rows.

    When the preview looks right, load it. The response becomes a dataset with one row per record.

    The dataset under Data → Datasets, with a column per field in the records.

What happens next

API responses often need flattening and renaming before they are useful. Prepare handles both — Normalise Text, Rename Column, Cast Type and Standardise All Names are the usual first moves on API data.

Once shaped, the dataset joins, models, charts and publishes like any other.

Example

A team wants weekly ticket volumes from their support tool. The API returns { "results": [ ... ] }, so they set Data Path to results, choose Bearer Token, paste the token and test. The preview shows one row per ticket. They load it, cast created_at to a date in Prepare, and chart volume by week in Analyse.

Tips

  • Test before loading, every time. The test call shows you what the API actually returned, which is quicker than reasoning about why a load produced one row.
  • A dataset with exactly one row and strange column names almost always means Data Path was left empty when it was needed.
  • Prefer a token with the narrowest scope the API offers.
  • If the API paginates, one call returns one page. Load what a single call returns and plan for the rest rather than expecting the whole history.

Limitations

  • The connector loads what one call returns. It does not automatically page through a paginated API.
  • The response must contain an array of records that can be read as rows. Deeply nested objects with no repeating array have nothing tabular to extract.
  • Five authentication types are supported: No Auth, Bearer Token, API Key, Basic Auth and OAuth 2.0. Request signing schemes outside these are not available.
  • A load is a point-in-time snapshot. It does not refresh on its own.

Related how-to guides

Connect a database

Connect PostgreSQL, MySQL, SQL Server, Redshift, BigQuery or Snowflake, browse the tables and load what you need.

Clean a messy dataset

Drop nulls, fill gaps, remove duplicates and cap outliers — with a live preview before anything is applied.

Transform and derive columns

Cast types, split and rename columns, standardise names and values, and build new columns from expressions.

Connect a CRM

Bring Salesforce, HubSpot, Dynamics 365 or Zoho objects in as datasets you can join with everything else.

Related questions

More of these on the DataLens FAQ page.

What authentication does the DataLens REST API connector support?

No Auth, Bearer Token, API Key with a custom header name, Basic Auth, and OAuth 2.0 using a token URL, client ID, client secret and scope.

My API returns one row instead of many. Why?

The records are almost certainly nested under a key in the response rather than at the top level. Set the Data Path field to that key — for a response shaped { "data": [ ... ] }, enter data — and the array is read as rows.

Try this in DataLens

DataLens is in private beta. Request access and work through this guide on your own data.

Request beta access