Connect Google Sheets and Google Analytics

Google Sheets and Google Analytics are two Google sources, each with its own connection form in the Lake. Use this page to fill in the OAuth details and the spreadsheet of a Sheets connection, or the project, property and service account key of an Analytics connection.

Add a Google Sheets source

Open Lake and click Add datasource in the header, or open the New menu in the toolbar above the tree.
Choose Google Sheets in the picker. Supported connectors shows how the picker is grouped.
In the General section, fill in Name and Code and choose a Folder if you want one. These fields work as on any source; see Add a data source.
Fill in the OAuth, Spreadsheet and Sheet configuration sections, described below. The form is a single page: every section is on it at once.
Use Test Connection in the footer if you want to check the connection before saving. It is optional.
Click Add to save. On an existing source the button reads Save, and Duplicate makes a copy; then import the tables you want.

Fill in the OAuth section

The OAuth section holds the three values the connection signs in with.

Client ID: the help text reads "OAuth 2.0 Client ID from Google Cloud Console".
Client Secret: the help text reads "OAuth 2.0 Client Secret from Google Cloud Console". The field masks what you type.
Refresh Token: the help text reads "Refresh token obtained through OAuth authorization flow". The field masks what you type.

All three are required. When one is empty, the form names it: "Client ID is required", "Client Secret is required" or "Refresh Token is required". The section links to Google's OAuth documentation.

Point the connection at a spreadsheet

Spreadsheet ID is the id of the spreadsheet to read. Its help text shows the shape: "The ID from your Google Sheets URL (e.g., from https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit)".
Or extract from URL takes a Sheets link: paste it and click Extract ID. The button is disabled while the box is empty, and the box clears itself once the ID is taken.
Accepted link shapes: a link that holds /spreadsheets/d/<id>, a link with an id parameter, or the bare ID.

An empty field shows "Spreadsheet ID is required". An ID with other characters, such as spaces, shows "Invalid Spreadsheet ID format. Please use the ID from the Google Sheets URL".

Configure the sheets

Sheet configuration lists the sheets the connection reads. The field reads Sheets with the note (Sheet name and data range).

Click Add Sheet and fill in the row: Sheet name (required) and Range (e.g., A1:B10, AA1:BC100). Save keeps the row; Cancel discards it.
A saved row shows its name with Range: <range>, and the Edit and Remove actions. Remove is disabled for the only sheet: Cannot remove the only sheet.
Every connection needs at least one sheet with both a name and a range. While no row is valid, the section shows "Save at least one valid sheet" and "Each sheet must have both a name and a range".
A range is a cell range such as A1:B10 or AA1:BC100, with row numbers. Do not put the sheet name inside the range; set the sheet name in its own field. For a single cell, write A1:A1. A range can span at most 1000 columns and 100,000 rows.

Messages on a row:

"Sheet name cannot be empty" or "Range cannot be empty" when you save a row without one of them.
"Sheet name must be unique" when another sheet already uses the name, and "Sheet name contains invalid characters" for characters a sheet name cannot hold.
"Do not include sheet name in range. Sheet name is specified separately" when the range carries the sheet name.
"Invalid range format. Range must be in format like A1:B10, B2:F10, or AA1:BC100" when the range is not a cell range, and "Single cells not allowed. Use a range format like A1:A1 for single cell or A1:B10 for multiple cells" when you enter one cell.
"Range is too wide. Maximum 1000 columns allowed" or "Range is too tall. Maximum 100,000 rows allowed" when the range is too large.

The form checks on save that at least one sheet has both a name and a range ("At least one sheet must have both name and range").

Add a Google Analytics source

Open Lake and click Add datasource in the header, or open the New menu in the toolbar above the tree.
Choose Google Analytics in the picker; Supported connectors shows how the picker is grouped.
In the General section, fill in Name, Code and Folder; these fields work as on any source (Add a data source).
In the Connection section, fill in Project ID (placeholder your-project-id), Property ID (placeholder 123456789) and Service Account Email (placeholder service@project.iam.gserviceaccount.com), then add the key in the Service Account Key section below.
Click Add to save; on an existing source the button reads Save, and Duplicate makes a copy. Then import the tables you want.

The three fields are required: the form names any that is missing ("Project ID is required", "Property ID is required", "Service Account Email is required"), and an address that is not an email shows "Invalid email format". A documentation panel sits beside the form; drag the divider to resize it, or use the Documentation rail to collapse it.

Add the service account key

The Service Account Key section holds the credentials of the connection.

Click Choose File and pick the JSON key file of a service account. The file is read into the box below as formatted JSON.
Or paste the key JSON into the box yourself. The placeholder shows the shape: {"type": "service_account", "project_id": "...", ...}.
A file that cannot be read as JSON shows "Invalid JSON file".
The key is checked again when you save or test the connection: an empty key shows "Service account key is required", and text that is not readable JSON shows "Invalid JSON format in service account key".

The key is stored as a secret; see Connector secrets for how stored secrets are kept.

Save, test and edit

Test Connection in the footer checks the source with the settings on the form; it is optional and saves nothing. A passing test reads Google Sheets connection test successful on the Sheets form and Connection test successful on the Analytics form. A failing test shows the Connection test failed toast with the server's message.
Saving confirms with a toast. The Sheets form reports the connection by name: "Google Sheets connection added successfully", "Google Sheets connection updated successfully" or "Google Sheets connection duplicated successfully". The Analytics form uses the shared wording: "Connection added successfully", "Connection updated successfully" or "Connection duplicated successfully".
After you save a new connection, the app opens the new datasource. A save that fails while you edit shows the error at the top of the form; a failed create or duplicate arrives as an Error toast with the server's message.
Opening a saved connection fills the form from it. Name and Code are locked in edit mode, and Duplicate clears them for the copy. On the Analytics form, the saved key is loaded back into the box as formatted JSON. Revert in the footer resets your changes.

Who can add a Google source

To open a saved connection, you need View Lake Objects.
To add, edit, duplicate or test one, you need Add/Modify Lake Objects, which includes View Lake Objects.

For the full list, see Who can do what in the Lake.

Questions, answered

Which credentials do I need for Google Sheets?

An OAuth 2.0 Client ID and its Client Secret, plus a Refresh Token. The help texts on the three fields name their source in Google Cloud Console, and the OAuth documentation link opens the flow that produces the token. All three are required.

Can I paste the spreadsheet link instead of the ID?

Yes. Paste the link into Or extract from URL and click Extract ID. The button accepts a link with /spreadsheets/d/<id>, a link with an id parameter, or the bare ID, and it clears the box once the ID is taken. If the link holds no recognisable ID, nothing is extracted.

Why is Extract ID greyed out?

The URL box is empty. Type or paste a link and the button turns on.

What does a sheet range look like?

A cell range with row numbers, such as A1:B10 or AA1:BC100. Do not include the sheet name in the range; the sheet name goes in its own field. For a single cell, write A1:A1. A range can span at most 1000 columns and 100,000 rows.

Why can't I remove the last sheet?

A Sheets connection needs at least one sheet with both a name and a range, so Remove stays disabled on the only sheet (Cannot remove the only sheet). Add another sheet first, then remove the one you do not need.

Why does the form say "Save at least one valid sheet"?

No sheet row is valid yet: at least one row must have both a Sheet name and a Range. Fill in the row, or add one, to continue.

Which fields does the Google Analytics form ask for?

Project ID, Property ID, Service Account Email and the service account key in the Service Account Key section. All are required: an empty field shows its own message ("Project ID is required", "Property ID is required", "Service Account Email is required"), and an address that is not an email shows "Invalid email format".

Why was my service account key rejected on Google Analytics?

An empty key shows "Service account key is required"; text that is not readable JSON shows "Invalid JSON format in service account key"; and a file that cannot be read shows "Invalid JSON file". Add the JSON key file of a service account again, or paste its contents.

What does the footer test check, and what does a failure show?

Test Connection checks that the source can be reached with the settings on the form. It is optional and saves nothing. On success the footer reads Google Sheets connection test successful on the Sheets form or Connection test successful on the Analytics form. On failure the Connection test failed toast carries the server's message, and you can fix the settings and test again.

What opens when I edit a saved Google connection?

The same form, filled from the saved connection. Name and Code are locked in edit mode; Duplicate clears them for the copy. On the Analytics form the saved key is loaded back into the box as formatted JSON so you can read or replace it.