# Google Sheets

> Bring the tabs of a Google spreadsheet into your warehouse, one table per tab, read again on every sync.

## Before you start

You need the spreadsheet's link, and a way for sanda to sign in to Google as something that can see it. There are two, and the **Authentication** field asks you to pick one. It opens on **Service Account Key Authentication**.

A service account is the easier of the two to keep running. It is a Google identity for software, and it can only see the spreadsheets that someone has shared with it. To set one up:

:::steps
1. **Prepare a Google Cloud project.** In the Google Cloud console, create a project or choose an existing one, and enable the Google Sheets API for it.
2. **Create the service account.** Create a service account in that project, add a JSON key to it, and download the key file.
3. **Share the spreadsheet with it.** The key file holds an email address for the service account, in its `client_email` entry. Share the spreadsheet with that address, with **Viewer** access.
:::

:::note
Some organisations stop people creating service account keys, by policy. If Google Cloud refuses to create the key, ask whoever administers your Google Cloud organisation, or use the OAuth option below.
:::

The other option, **Authenticate via Google (OAuth)**, needs your own Google OAuth application with the read-only Sheets and Drive scopes (`spreadsheets.readonly` and `drive.readonly`). It gives you a **Client ID** and **Client Secret**, and you obtain a **Refresh Token** by completing Google's consent screen once. Choose it only if you already have those, or your organisation does not allow service account keys.

## Connect Google Sheets

:::steps
1. **Choose Google Sheets.** Go to **Data · Connections**, press **New connection** and pick **Google Sheets**.
2. **Paste the spreadsheet link.** In Google Sheets, press **Share** at the top right of the spreadsheet, then **Copy link**, and paste it into **Spreadsheet Link**.
3. **Sign in.** Under **Authentication**, choose **Service Account Key Authentication**. For the OAuth option, fill in **Client ID**, **Client Secret** and **Refresh Token** instead. With a service account, paste the whole JSON key file into **Service Account Information.**
4. **Continue.** Press **Continue**, name the connection, and press **Read schema**. sanda signs in and lists the tabs it can see. If it fails, the message says why. See [Troubleshoot connections](https://docs.sanda-os.com.au/connections/troubleshooting).
:::

The rest of the flow, naming the connection, setting a frequency and choosing streams, is the same for every source. See [Add a connection](https://docs.sanda-os.com.au/connections/add-a-connection).

## What syncs

Each tab of the spreadsheet becomes its own stream, and lands in your warehouse as its own table. The first row of a tab is read as its column names, and the rows below it are the data.

Google Sheets does not tell sanda which rows changed, so sanda reads each tab in full on every run, and its streams use a full refresh mode. See [sync modes](https://docs.sanda-os.com.au/connections/sync-modes).

## Tips

- **Keep a header row, with no gaps.** Column names become field names in your warehouse. By default sanda stops reading columns at the first empty header cell. Turn on **Read Empty Header Columns** under **Advanced settings** if you have blank headers with data beneath them.
- **Tidy the column names.** **Convert Column Names to SQL-Compliant Format**, under **Advanced settings**, turns names such as `Order Total` into `order_total`. It is off by default. The options beneath it refine how it treats numbers, letters and special characters.
- **Rename tabs on the way in.** **Stream Name Overrides** takes a JSON list. Each item has a `source_stream_name`, the exact name of the tab, and a `custom_stream_name`, the name you want.
- **Many tabs take longer.** Google limits how fast its Sheets API can be read. More **Number of Concurrent Threads** speeds up a spreadsheet with many tabs, but can hit that limit.
- **One spreadsheet per connection.** To bring in several spreadsheets, add a connection for each.

## Configuration fields

What the connection form asks for. Required fields are marked; the rest are optional.

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Spreadsheet Link** | string | Yes | Enter the link to the Google spreadsheet you want to sync. To copy the link, click the 'Share' button in the top-right corner of the spreadsheet, then click 'Copy link'. |
| **Row Batch Size** | integer | No | Default value is 1000000. An integer representing row batch size for each sent request to Google Sheets API. Row batch size means how many rows are processed from the google sheet, for example default value 1000000 would process rows 2-1000002, then 1000003-2000003 and so on. Based on Google Sheets API limits documentation, it is possible to send up to 300 requests per minute, but each individual request has to be processed under 180 seconds, otherwise the request returns a timeout error. In regards to this information, consider network speed and number of columns of the google sheet when deciding a batch_size value. Default `1000000`. |
| **Convert Column Names to SQL-Compliant Format** | boolean | No | Converts column names to a SQL-compliant format (snake_case, lowercase, etc). If enabled, you can further customize the sanitization using the options below. Default `false`. |
| **Remove Leading and Trailing Underscores** | boolean | No | Removes leading and trailing underscores from column names. Does not remove leading underscores from column names that start with a number. Example: "50th Percentile? "→ "_50_th_percentile" This option will only work if "Convert Column Names to SQL-Compliant Format (names_conversion)" is enabled. Default `false`. |
| **Combine Number-Word Pairs** | boolean | No | Combines adjacent numbers and words. Example: "50th Percentile?" → "_50th_percentile_" This option will only work if "Convert Column Names to SQL-Compliant Format (names_conversion)" is enabled. Default `false`. |
| **Remove All Special Characters** | boolean | No | Removes all special characters from column names. Example: "Example ID*" → "example_id" This option will only work if "Convert Column Names to SQL-Compliant Format (names_conversion)" is enabled. Default `false`. |
| **Combine Letter-Number Pairs** | boolean | No | Combines adjacent letters and numbers. Example: "Q3 2023" → "q3_2023" This option will only work if "Convert Column Names to SQL-Compliant Format (names_conversion)" is enabled. Default `false`. |
| **Allow Leading Numbers** | boolean | No | Allows column names to start with numbers. Example: "50th Percentile" → "50_th_percentile" This option will only work if "Convert Column Names to SQL-Compliant Format (names_conversion)" is enabled. Default `false`. |
| **Read Empty Header Columns** | boolean | No | When enabled, the connector will continue reading columns after empty header cells and will include data from those columns using generated column names (e.g., "column_C"). By default, the connector stops reading columns when it encounters an empty header cell. Default `false`. |
| **Number of Concurrent Threads** | integer | No | Number of concurrent threads for syncing. Higher values can speed up syncs for spreadsheets with multiple sheets, but may hit rate limits. Google Sheets API limits to 300 read requests per minute (~5 req/sec). Default `2`. |
| **Authentication** | one of 2 options | Yes | Credentials for connecting to the Google Sheets API. |
| **Stream Name Overrides** | array | No | Renames streams (Google Sheets tab names) as they appear in becca. Each item is an object with a `source_stream_name` (the exact name of the tab in your spreadsheet) and a `custom_stream_name` (the name you want it to have). A `source_stream_name` that is not found in your spreadsheet is ignored and the default name is used. This only renames streams, not fields or columns. Leave it blank to keep every tab name. |

### Authentication

Credentials for connecting to the Google Sheets API.

Choose one of the following. Each asks for its own fields.

**Authenticate via Google (OAuth)**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Client ID** | string, secret | Yes | Enter your Google application's Client ID. |
| **Client Secret** | string, secret | Yes | Enter your Google application's Client Secret. |
| **Refresh Token** | string, secret | Yes | Enter your Google application's refresh token. |

**Service Account Key Authentication**

| Field | Type | Required | Description |
| --- | --- | --- | --- |
| **Service Account Information.** | string, secret | Yes | The JSON key of the service account to use for authorization. |


## Related

:::links
- [Sync modes](https://docs.sanda-os.com.au/connections/sync-modes): How sanda reads each stream on every run.
- [Sync schedules](https://docs.sanda-os.com.au/connections/schedules): How often a connection runs.
- [Allowlist sanda’s address](https://docs.sanda-os.com.au/connections/allowlist): For a system behind a firewall.
- [Troubleshoot connections](https://docs.sanda-os.com.au/connections/troubleshooting): What an error means and how to fix it.
:::