Skip to main content

Google Sheets

Google Sheets is a cloud-based spreadsheet application developed by Google, part of the Google Workspace productivity suite. It enables individuals and teams to create, edit, and collaborate on spreadsheets in real time from any device. The Google Sheets API (v4) provides programmatic read and write access to spreadsheet data, allowing applications to retrieve cell values, update ranges, append rows, manage spreadsheet structure, and apply formatting — all without manual interaction. Common use cases include ingesting structured data from spreadsheets into data pipelines, exporting processed datasets back to sheets for reporting and analysis, syncing spreadsheet data with databases or data warehouses, and automating spreadsheet updates from operational systems.

Google Sheets icon

Power end-to-end data operations for your Google Sheets API with Nexla. Our bi-directional Google Sheets connector is purpose-built for Google Sheets, making it simple to ingest data, sync it across systems, and deliver it anywhere — all with no coding required. Nexla turns API-sourced data into ready-to-use, reusable data products and makes it easy to send data to Google Sheets or any other destination. With comprehensive monitoring, lineage tracking, and access controls, Nexla keeps your Google Sheets workflows fast, secure, and fully governed.

Features

Type: API

SourceDestination

  • Seamless API Integration: Connect to any endpoint as source or destination without coding, with automatic data product creation
  • Visual Composition & Chaining: Build complex integrations using visual templates, chain API calls, and compose workflows with data validation and filtering
  • API Proxy: Expose curated slices of your data securely with a secure and customizable API proxy that validates and transforms data on the fly
  • Request optimization with intelligent batching, retry, and caching to minimize API calls and costs

Prerequisites​

Before creating a Google Sheets API credential in Nexla, ensure the following requirements are met.

Google Account​

An active Google account with access to the Google Sheets spreadsheets you want to connect is required. If you are accessing spreadsheets owned by a Google Workspace organization or shared with you, ensure your account has the appropriate read or write permissions on the relevant spreadsheets.

Google Sheets API Enabled​

The Google Sheets API must be enabled for the Google Cloud project associated with your OAuth 2.0 credentials. If you are using Nexla's built-in OAuth application, this is already handled for you. If you are configuring your own OAuth client credentials, complete the following steps to enable the API:

  1. Sign in to the Google Cloud Console.

  2. Select your project from the project selector at the top of the page, or click New Project to create a new project.

  3. In the left navigation menu, select APIs & Services > Library.

  4. In the API Library search box, type Google Sheets API and press Enter.

  5. Click the Google Sheets API result, then click Enable to activate it for your project.

If your use case also requires listing or accessing spreadsheet files via Google Drive (for example, searching for spreadsheets by name or accessing files in shared drives), you may also need to enable the Google Drive API in the same Google Cloud project. Navigate to APIs & Services > Library, search for Google Drive API, and click Enable.

Required OAuth 2.0 Scopes​

The Nexla Google Sheets API connector uses OAuth 2.0 to authenticate with the Google Sheets API. During the authorization process, Nexla requests the following access scopes:

  • https://www.googleapis.com/auth/spreadsheets — Full read and write access to all Google Sheets spreadsheets. Required for both source and destination flows that create, update, or delete spreadsheet data.

  • https://www.googleapis.com/auth/spreadsheets.readonly — Read-only access to all Google Sheets spreadsheets. Sufficient for source-only flows where no write operations are needed.

  • https://www.googleapis.com/auth/drive.file — Access to only the specific Google Drive files opened or created by the application. A more narrowly scoped alternative for workflows where broad Drive access is not needed.

Google recommends requesting the most narrowly scoped permissions your use case requires. For read-only data ingestion workflows, the spreadsheets.readonly scope provides the minimum necessary access and reduces the permissions granted to the Nexla application. For complete details on available scopes, refer to the Google Sheets API scopes documentation.

Google Workspace Considerations​

If you are using a Google Workspace (formerly G Suite) account, your organization's administrator may need to approve third-party application access before the OAuth flow can complete successfully. If you encounter an authorization error during the OAuth flow, contact your Google Workspace administrator to verify that the Nexla application is permitted to access Google Sheets data for your organization. For additional details, refer to Google's documentation on controlling third-party app access.

Authenticate​

Create a credential in Nexla​

  1. After selecting the data source/destination type, click the Add Credential tile to open the Add New Credential overlay.

  2. Enter a name for the credential in the Credential Name field and a short, meaningful description in the Credential Description field.

    The Google Sheets API connector uses OAuth 2.0, which means you authorize Nexla to access your Google Sheets account directly through Google's secure authorization flow. Your Google account password is never shared with Nexla.

  3. Click the Authorize button to initiate the OAuth 2.0 authorization flow. A new browser window will open, directing you to the Google sign-in page.

  4. Sign in with the Google account that has access to the Google Sheets spreadsheets you want to connect. If you are already signed in to multiple Google accounts, select the appropriate account from the list.

  5. Review the permissions that Nexla is requesting on the Google consent screen, and click Allow to grant access. Google will redirect you back to Nexla once authorization is complete.

    The OAuth 2.0 authorization flow is managed entirely by Google. Nexla does not have access to your Google account password. The authorization grants Nexla a secure, scoped token that allows it to access Google Sheets data on your behalf. This access can be revoked at any time by visiting your Google Account permissions page, finding the Nexla application, and selecting Remove Access.

  6. Click the Save button at the bottom of the overlay. The newly added credential will now appear in a tile on the Authenticate screen during data source/destination creation and can be selected for use with a new data source or destination.

Use as a data source​

To create a new data flow, navigate to the Integrate section, and click the New Data Flow button. Select the Google Sheets API connector tile, 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.

Endpoint templates​

Nexla provides pre-built templates that can be used to rapidly configure data sources to ingest data from common Google Sheets API endpoints. 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.

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.

Once the selected endpoint template has been configured, click the Test button to the right of the endpoint selection menu to retrieve a sample of the data that will be fetched. Sample data will be displayed in the Endpoint Test Result panel on the right, allowing you to verify that the source is configured correctly before saving.

Manual configuration​

Google Sheets API data sources can also be manually configured to ingest data from any valid Google Sheets API endpoint, including endpoints not covered by the pre-built templates, chained API calls, or custom request parameters. Select the Advanced tab at the top of the configuration screen, and follow the instructions in Connect to Any API to configure the API method, endpoint URL, date/time and lookup macros, path to data, metadata, and request headers.

The Google Sheets API base URL is https://sheets.googleapis.com/v4/spreadsheets. Common endpoint patterns include reading a range (/{spreadsheetId}/values/{range}), retrieving spreadsheet metadata (/{spreadsheetId}), and batch reading multiple ranges (/{spreadsheetId}/values:batchGet). The spreadsheet ID is the alphanumeric string between /d/ and /edit in the spreadsheet's URL. For the standard spreadsheets.values.get endpoint, set the path to data to $.values[*], and use A1 notation (e.g., Sheet1!A1:D10) for ranges — to read an entire sheet, use only the sheet name. Response fields such as spreadsheetId, valueRenderOption, and range outside the values array can be preserved as record metadata. For details, see the Google Sheets API values.get documentation.

Once all of the relevant settings have been configured, 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.

Use as a destination​

Click the + icon on the Nexset that will be sent to the Google Sheets API destination, and select the Send to Destination option from the menu. Select the Google Sheets API connector from the list of available destination 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.

Endpoint templates​

Nexla provides pre-built templates that can be used to rapidly configure destinations to send data to common Google Sheets API endpoints. Select the endpoint to which data will be sent from the Endpoint pulldown menu. Then, click on the template in the list below to expand it, and follow the instructions to configure additional endpoint settings.

Append Rows (Sink)

Appends rows of data to a Google Sheet, adding new rows after the last existing row in the specified range. Use this endpoint for ongoing data loading where records should be continuously added to a sheet without overwriting existing data.

  • Sends a POST request to https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append with the row data in the request body.
  • Configure the following parameters: Spreadsheet ID — the unique ID of the target spreadsheet; Sheet Range — the A1 notation range or sheet name where rows will be appended (e.g., Sheet1); Value Input Option — how input values are interpreted. Options: User Entered (parses values as if typed by a user, enabling date and formula parsing), Raw (stores values exactly as provided).

The Google Sheets API enforces a quota of 300 write requests per minute per project and 60 write requests per minute per user. Use record batching in Nexla to reduce the number of individual API calls and avoid hitting these limits when writing large volumes of data.

Update Cells (Sink)

Updates specific cell values in a Google Sheet range, overwriting existing values in the specified area. Use this endpoint when you need to refresh or replace data in a known fixed range of a spreadsheet.

  • Sends a PUT request to https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range} with the updated cell values in the request body.
  • Configure the following parameters: Spreadsheet ID — the unique ID of the target spreadsheet; Sheet Range — the A1 notation range to update (e.g., Sheet1!A1:D10); Value Input Option — how input values are interpreted. Options: User Entered (parses values as if typed by a user), Raw (stores values exactly as provided).

This endpoint overwrites the values in the specified range. Cells in the range that are not included in the request body will be cleared. Use Append Rows if you want to add new data without affecting existing rows.

Manual configuration​

Google Sheets API destinations can also be manually configured to send data to any valid Google Sheets API endpoint. Select the Advanced tab at the top of the configuration screen, and follow the instructions in Connect to Any API to configure the API method, data format, endpoint URL, request headers, attribute exclusions, record batching, and response webhooks.

The Google Sheets API expects data in JSON format using a ValueRange object with a values property — a two-dimensional array where each inner array represents one row of cells. Common write endpoints include appending rows (/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED), updating a range (/{spreadsheetId}/values/{range}?valueInputOption=USER_ENTERED), and batch updates (/{spreadsheetId}/values:batchUpdate). The valueInputOption parameter is required — use USER_ENTERED to let Google Sheets parse values as if typed by a user (e.g., dates and formulas), or RAW to store values exactly as provided. For details, see the Google Sheets API values documentation.

Save & activate​

Once all endpoint settings have been configured, click the Done button in the upper right corner of the screen to save and create the destination. To send the data to the configured Google Sheets API endpoint, open the destination resource menu, and select Activate.

The Nexset data will not be sent to the Google Sheets API endpoint until the destination is activated. Destinations can be activated immediately or at a later time, providing full control over data movement.