Google Sheets
The Google Sheets connector reads worksheets, writes rows, and creates or renames worksheets in Google Sheets. It signs in with your own Google OAuth client.
When to use it
- A team maintains a reference list by hand in a spreadsheet, and the data pipeline reads it.
- You write a small result where a person reads it.
For large or frequent loads, use a database instead of a sheet.
What you need
- A Google Cloud project with the Google Sheets API and the Google Drive API enabled.
- An OAuth client of type Web application, created in the Google Cloud Console under APIs & Services > Credentials.
- A Google account with access to the spreadsheets.
One OAuth client can serve both Google Sheets and Google Drive connections.
Provider-side setup for this connector, including the app registration, the redirect URI and the permissions, is in the File hub setup guide. Connection basics and environments are on the Connections page.
Connect
The connection form lists its steps on the left. Open any step from that list. The Environments step holds the values that differ per environment.
Test connection is in the form footer. Beside it, Test against picks Base, the connection as saved, or an environment, whose values are applied first.
| Field | Step | What to enter |
|---|---|---|
| Client ID | Authentication | The OAuth client ID, ending .apps.googleusercontent.com. |
| Redirect URI | Authentication | Filled in. Add it to the OAuth client's authorized redirect URIs. |
| Client Secret | Authentication | The OAuth client secret. |
| Connected Account, Display Name | Authentication | Filled in when you connect the Google account. |
| Default Spreadsheet ID | Connection | Optional. A node can name its own spreadsheet. |
| Default Worksheet | Connection | Optional, such as Sheet1. A node can name its own worksheet. |
| Notes | Options | Free text for your team. |
The Redirect URI is filled in for you. Copy it with the copy button and add it to the app registration exactly as shown. Use Edit beside the field only when the app is registered against a different address for DataLug.
Connect the Google account on the Authentication step. Save stays disabled until the account is connected. DataLug requests the Google Sheets and Google Drive scopes, and your email address and profile.
Supported operations
| Node | Operations |
|---|---|
| Source | List Spreadsheets, List Worksheets, Read Sheet |
| Load | Append Rows, Write Range, Update Rows, Create Spreadsheet, Create Worksheet, Rename Worksheet, Delete Worksheet |
Configure Action
Configure Action asks only for what the chosen operation needs.
| Step | What it asks |
|---|---|
| Operation | The operation, then the spreadsheet if the operation uses one. |
| Worksheet & rows | The worksheet, the range and the columns. Skipped when the operation has none. |
| Review | A summary and the action name. |
- The spreadsheet is chosen from a list. For a spreadsheet shared by link, press Enter an ID instead and paste the spreadsheet URL or its ID.
- That field accepts Google Sheets links only. A Drive file or Docs link is refused.
- The worksheet list shows each sheet's row and column counts.
- Picking a worksheet fills Range from its size and the columns from its header row. You can edit either.
- Read from sheet reads the columns again after you change the range.
- The action name is generated from the operation and the sheet. Click it to change it.
Which write to use
| Operation | What it does to the sheet |
|---|---|
| Append Rows | Adds rows at the end. Existing cells are unchanged. |
| Write Range | Replaces every cell in the range, including the header row when the range starts at row 1. |
| Update Rows | Finds each row by a key column and writes only the mapped columns. Other cells in the row are unchanged. |
To write results beside data you keep, use Update Rows.
Update Rows needs a key column in the sheet's header row, and every column it writes must be in the header row too. A row whose key matches nothing is reported as failed, not appended, and leaves on the node's ERR output.
Write Range and Update Rows stop before sending when a mapped column has no value in any incoming row.
How values are written
Append Rows, Write Range and Update Rows write each value as entered text. A
value such as =SUM(A1:A3) or 2026-10-31 lands in the cell as that text, not
as a formula or a date.
Turn on Interpret values like typed input on the Worksheet & rows step to have Google Sheets read each value as if you typed it into the cell. Formulas are then evaluated, and numbers and dates are converted. Use it only when the incoming data is trusted.
Limits
Google applies its own per-project and per-user quotas to the Sheets API.
The API limits card on the connection form is collapsed by default. Every field on it is optional, and a blank field uses the default shown below it.
| Setting | What it does | Default |
|---|---|---|
| Requests per period, Period | Paces outgoing calls. Set both to throttle. | No throttle, or the rate shown on the card. |
| Page size | Rows per request. A larger value is reduced to the provider's maximum. | The maximum shown on the card, or set by the provider. |
| Max attempts | Tries per call, including the first. | 3 |
| Cool-off (ms) | The first wait after a 429. Later waits grow from it. | 500 ms |
| Requests at once | Requests open at the same time within one run. | No limit |
A Retry-After header from the provider overrides the cool-off. The pace
applies per connection and is shared by every run on it. See
Run performance.
DataLug retries a call that the provider refused with 429, and a call that
could not connect. A read is also retried after a 408, a 5xx or a dropped
connection. A write is retried only after a 429 or a failed connection.
A Retry-After delay of up to one minute is honoured. A longer one fails the
call with the 429.
Troubleshooting
- A connection that worked stops working. The stored authorisation was withdrawn or has expired. Open the connection and connect the account again. Editing the other fields does not restore it.
- A run fails with "Method doesn't allow unregistered callers". The request reached Google with no token. Open the connection and connect the account again. If it happens in one environment only, check that environment's values on the Environments step.
- The spreadsheet field refuses a link. The link is to a Drive file or a Google Doc. Open the spreadsheet in Google Sheets and copy its address.
- Dates or numbers appear as text in the sheet. Turn on Interpret values like typed input on the node.
- Update Rows fails every row. The key column is missing from the header row, or its values do not match. Check the header spelling on the sheet.
- Google shows redirect_uri_mismatch when you connect. Copy the Redirect URI again and add it to the OAuth client exactly.
Related
- Google Drive, which can share the same OAuth client.
- Source node and Load node.