Skip to content
sandadocs
Console Start free trial

Google Sheets

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

View as Markdown

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:

  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.

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

  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.

The rest of the flow, naming the connection, setting a frequency and choosing streams, is the same for every source. See 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.

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.

Something unclear or out of date? Tell us, and we will fix the page.