Skip to main content

Data Source

The Google Sheets API connector enables you to ingest spreadsheet data — including cell values, ranges, and entire sheets — from Google Sheets into Nexla. Follow the instructions below to create a new data flow that ingests data from a Google Sheets API source in Nexla.
google_sheets_api.png

Google Sheets API

Create a New Data Flow​

  1. To create a new data flow, navigate to the Integrate section, and click the New Data Flow button. Then, select the desired flow type from the list, and click the Create button.

  2. Select the Google Sheets API connector tile from the list of available connectors. Then, select the credential that will be used to connect to the Google Sheets API, and click Next; or, create a new Google Sheets API credential for use in this flow.

  3. In Nexla, Google Sheets API data sources can be created using pre-built endpoint templates, which expedite source setup for common Google Sheets API endpoints. Each template is designed specifically for the corresponding Google Sheets API endpoint, making data source setup easy and efficient.
    • To configure this source using a template, follow the instructions in Configure Using a Template.

    Google Sheets API sources can also be configured manually, allowing you to ingest data from Google Sheets API endpoints not included in the pre-built templates or apply further customizations to exactly suit your needs.
    • To configure this source manually, follow the instructions in Configure Manually.

Configure Using a Template​

Nexla provides pre-built templates that can be used to rapidly configure data sources to ingest data from common Google Sheets API endpoints. Each template is designed specifically for the corresponding Google Sheets API endpoint, making data source setup easy and efficient.

Endpoint Settings​

  • Select the endpoint from which this source will fetch data from the Endpoint pulldown menu. Available endpoint templates are listed in the expandable boxes below. Click on an endpoint to see more information about it and how to configure your data source for this endpoint.

    Sheet Data (by Range)

    Reads cell values from a specific range in a Google Sheet. Use this endpoint when you need to ingest data from a defined area of a spreadsheet, such as a named range or a specific set of columns and rows.

    • Sends a GET request to https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range} and returns the cell values for the specified range.
    • Response data is extracted from $.values[*] — each element represents one row of cell values as an array.
    • Configure the following parameters: Spreadsheet ID — the unique ID of the Google Sheets spreadsheet (found in the URL between /d/ and /edit); Range — the A1 notation range to read (e.g., Sheet1!A1:D100 or simply Sheet1 to read the entire sheet).

    Use A1 notation for the range parameter, such as Sheet1!A1:D100. To read an entire sheet, use only the sheet name (e.g., Sheet1). The first row is commonly used as column headers, which can be promoted to field names during Nexla schema configuration.

    Spreadsheet Info

    Retrieves spreadsheet metadata including all sheet and tab names, IDs, and properties. Use this endpoint to discover the structure of a spreadsheet before reading data from specific sheets.

    • Sends a GET request to https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId} and returns the full spreadsheet metadata object.
    • Response data is extracted from $ (the root object) — includes spreadsheet properties, all sheet definitions, and named ranges.
    • Configure the following parameter: Spreadsheet ID — the unique ID of the Google Sheets spreadsheet.

    This endpoint returns metadata only, not cell values. Use it to retrieve sheet names and IDs for use in subsequent Sheet Data (by Range) or All Sheets Data requests via lookup-based macros.

    List Sheets/Tabs

    Lists all sheets (tabs) within a spreadsheet with their properties, including sheet ID, title, index, and dimensions. Use this endpoint to enumerate all tabs in a spreadsheet for dynamic multi-sheet ingestion workflows.

    • Sends a GET request to https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId} and extracts the sheets list from the metadata response.
    • Response data is extracted from $.sheets[*].properties — each element represents one sheet tab with its title, ID, and configuration.
    • Configure the following parameter: Spreadsheet ID — the unique ID of the Google Sheets spreadsheet.

    The sheet titles returned by this endpoint can be used as lookup values to dynamically construct range parameters for subsequent Sheet Data (by Range) requests, enabling automated ingestion from all tabs in a spreadsheet.

    List Spreadsheets

    Lists all Google Sheets spreadsheets in the authenticated user's Google Drive with metadata including file ID, name, and creation/modification timestamps. Use this endpoint to discover and inventory spreadsheets accessible to the credential.

    • Sends a GET request to the Google Drive API (https://www.googleapis.com/drive/v3/files) filtered to Google Sheets MIME type, and returns file metadata for all accessible spreadsheets.
    • Response data is extracted from $.files[*] — each element represents one spreadsheet file with its ID, name, and timestamp fields.

    This endpoint queries the Google Drive API rather than the Sheets API directly. The credential must have Drive read access (https://www.googleapis.com/auth/drive.readonly or equivalent) in addition to Sheets access. Spreadsheet IDs returned here can be used in other endpoints.

    All Sheets Data

    Retrieves data from all sheets and tabs within a spreadsheet by first listing the sheets and then reading each one. Use this endpoint for full-spreadsheet ingestion when the number or names of tabs are not known in advance.

    • Sends a GET request to retrieve all sheet properties from https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}, then iterates to read each sheet's data.
    • Response data is extracted from $.sheets[*].properties in the first iteration, then reads cell values per sheet tab.
    • Configure the following parameter: Spreadsheet ID — the unique ID of the Google Sheets spreadsheet.

    This endpoint performs multiple API calls — one to list sheets and one per tab to read values. For spreadsheets with many tabs, this increases API quota usage. Consider using Sheet Data (by Range) for targeted reads when the sheet names are known.

Endpoint Testing​

Once the selected endpoint template has been configured, Nexla can retrieve a sample of the data that will be fetched according to the current settings. This allows users to verify that the source is configured correctly before saving.

  • To test the current endpoint configuration, click the Test button to the right of the endpoint selection menu. Sample data will be fetched & displayed in the Endpoint Test Result panel on the right.

  • If the sample data is not as expected, review the selected endpoint and associated settings, and make any necessary adjustments. Then, click the Test button again, and check the sample data to ensure that the correct information is displayed.

Configure Manually​

Google Sheets API sources can also be configured manually, allowing you to ingest data from Google Sheets API endpoints not included in the pre-built templates or apply further customizations to exactly suit your needs.

First, select the HTTP method that will be used for calls to the Google Sheets API from the Method pulldown menu. The most common method for reading spreadsheet data is:

  • GET: For retrieving cell values, ranges, and spreadsheet metadata

API Endpoint URL​

  1. Enter the URL of the Google Sheets API endpoint from which this source will fetch data in the Set API URL field. This should be the complete URL including the protocol (https://) and all required path parameters.

    The Google Sheets API base URL is https://sheets.googleapis.com/v4/spreadsheets. Common endpoint patterns for reading data include:

    • Read values from a specific range: https://sheets.googleapis.com/v4/spreadsheets/{'{spreadsheetId}'}/values/{'{range}'}
    • Read all values from a sheet: https://sheets.googleapis.com/v4/spreadsheets/{'{spreadsheetId}'}/values/Sheet1
    • Get spreadsheet metadata: https://sheets.googleapis.com/v4/spreadsheets/{'{spreadsheetId}'}
    • Batch read from multiple ranges: https://sheets.googleapis.com/v4/spreadsheets/{'{spreadsheetId}'}/values:batchGet?ranges={'{range1}'}&ranges={'{range2}'}

    Replace {'{spreadsheetId}'} with the unique ID of your Google Sheets spreadsheet. The spreadsheet ID can be found in the spreadsheet URL — it is the long alphanumeric string between /d/ and /edit in the URL (for example, https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms/edit has an ID of 1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgVE2upms).

Range notation in the Google Sheets API uses A1 notation (for example, Sheet1!A1:D10 to access columns A through D, rows 1 through 10, on the sheet named Sheet1). To read an entire sheet, omit the cell range and use only the sheet name (for example, Sheet1). For more details on range notation and available parameters, refer to the Google Sheets API values.get documentation.

Date/Time Macros (API URL)​

Optional

Optionally, the API URL can be customized using macros — all macros added to the API URL will be converted into values when Nexla executes the API call. Macros are dynamic placeholders that allow you to create flexible API endpoints that can adapt to different time periods or data requirements.

Date/time macros are useful for Google Sheets sources when the spreadsheet range or query parameters include date-based identifiers. For example, you can use macros to dynamically reference a sheet tab named by date or to pass date parameters to query-capable endpoints.

  1. To add a macro, type { at the appropriate position in the API URL (within the Set API URL field), and select the desired macro from the dropdown list.

    • {now} – The current datetime
    • {now-1} – The datetime one time unit before the current datetime
    • {now+1} – The datetime one time unit after the current datetime
    • custom – Datetime macros can reference any number of time units before or after the current datetime — for example, enter (now-4) to indicate the datetime four time units before the current datetime

  2. Select the format that will be applied to datetime macros from the Date Format for Date/Time Macro pulldown menu. This format will be applied to the base datetime value of the macro — i.e., the value of {now} in {now-1}.

  3. Select the datetime unit that will be used to perform mathematical operations in the included macro(s) from the Time Unit for Operations pulldown menu — for example, for the macro {now-1}, when Day is selected, {now-1} will be converted to the datetime one day before the current datetime.

Lookup-Based Macros (API URL)​

Optional

Column values from existing lookups can also be included as macros in the API URL. Lookup-based macros allow you to reference data from previously configured data sources or lookups, enabling dynamic API endpoints that adapt based on existing data.

Lookup-based macros are useful when you need to construct Google Sheets API URLs that reference specific spreadsheet IDs, sheet names, or range parameters stored in another Nexla data source.

  1. To include a lookup column value macro, select the relevant lookup from the Add Lookups to Supported Macros pulldown menu.

  2. Type { at the appropriate position in the API URL, and select the lookup column-based macro from the dropdown list. Lookup-based macros are automatically populated into the macro list when a lookup is selected in the Add Lookups to Supported Macros pulldown menu.

Path to Data​

Optional

If only a subset of the data returned by the API endpoint is needed, you can designate the part of the response that should be included in the Nexset(s) produced from this source by specifying the path to the relevant data within the response.

For the Google Sheets API values.get endpoint, the response includes a values array that contains the actual spreadsheet data as a two-dimensional array of rows and cells. Specifying the path to this array allows Nexla to properly parse and organize the spreadsheet rows as individual records.

For the standard spreadsheets.values.get endpoint, the path to the cell data is $.values[*]. This tells Nexla to treat each row in the values array as a separate record. Without specifying this path, Nexla may not be able to properly interpret the nested JSON structure returned by the Google Sheets API.

  • To specify which data should be treated as relevant in responses from this source, enter the path to the relevant data in the Set Path to Data in Response field.

    • For responses in JSON format enter the JSON path that points to the object or array that should be treated as relevant data. JSON paths use dot notation (e.g., $.values[*] to access the rows array within a Google Sheets API response).
    Path to Data Example:

    If the Google Sheets API response includes a top-level values array containing the spreadsheet rows, the path to the response would be entered as $.values[*].

Autogenerate Path Suggestions​

Nexla can also autogenerate data path suggestions based on the response from the API endpoint. These suggested paths can be used as-is or modified to exactly suit your needs.

  • To use this feature, click the Test button next to the Set API URL field to fetch a sample response from the API endpoint. Suggested data paths generated based on the content and format of the response will be displayed in the Suggestions box below the Set Path to Data in Response field.

  • Click on a suggestion to automatically populate the Set Path to Data in Response field with the corresponding path. The populated path can be modified directly within the field if further customization is needed.

    PathSuggestions.png

Metadata​

If metadata is included in the response but is located outside of the defined path to relevant data, you can configure Nexla to include this data as common metadata in each record. This is useful when you want to preserve important contextual information that applies to all records but isn't part of the main data array.

For example, the Google Sheets API values.get response includes fields such as spreadsheetId, valueRenderOption, and range alongside the values array. These fields provide useful context about the data source and can be preserved as metadata on each record.

Metadata paths are particularly useful for preserving the spreadsheet ID, sheet range, and other response-level fields that identify the source of the data in each record.

  • To specify the location of metadata that should be included with each record, enter the path to the relevant metadata in the Path to Metadata in Response field.

    • For responses in JSON format, enter the JSON path to the object or array that contains the metadata.

Request Headers​

Optional

  • If Nexla should include any additional request headers in API calls to this source, enter the headers and corresponding values as comma-separated pairs in the Request Headers field (e.g., header1:value1,header2:value2). Additional headers are sometimes required for API versioning or to request specific response formats.

    You do not need to include any headers already present in the credentials. Authorization headers are automatically handled by Nexla based on your Google Sheets API credential configuration.

Endpoint Testing​

After configuring all settings for the selected endpoint, Nexla can retrieve a sample of the data that will be fetched according to the current configuration. This allows users to verify that the source is configured correctly before saving.

  • To test the current endpoint configuration, click the Test button to the right of the endpoint selection menu. Sample data will be fetched and displayed in the Endpoint Test Result panel on the right.

  • If the sample data is not as expected, review the selected endpoint and associated settings, and make any necessary adjustments. Then, click the Test button again, and check the sample data to ensure that the correct information is displayed.

Save & Activate the Source​

  1. Once all of the relevant steps in the above sections have been completed, click the Create button in the upper right corner of the screen to save and create the new Google Sheets API data source. Nexla will now begin ingesting data from the configured endpoint and will organize any data that it finds into one or more Nexsets.