Google Drive (Sheets)

Connecty can now read data directly from your Google Sheets. That means the spreadsheets your business teams already live in can power AI answers, charts and workflows in Connecty - without a separate ETL project.

Under the hood, Connecty automatically profiles your Sheets data during sync and query execution: column types, basic stats, and sampling are all captured so answers in conversational analytics and Query Inspector are based on fresh, well-understood data.

Integration options

You can bring Google Sheets into Connecty in two ways:

1. Direct Google Sheets connection Use Sheets as a first-class data source. Connecty reads the tabs you point it to and treats them like regular tables for questions, charts, and workflows.

2. Sheets via BigQuery If you already have BigQuery external tables or views that depend on Google Sheets stored in Google Drive, Connecty can now query those as well. This is ideal if your data team already manages those external tables and joins them with warehouse data.

In both setups, Connecty needs access to the underlying Google Sheets via a service account.

---

Prerequisite: Share your Google Sheets with the service account

1. Contact Support to get your personalized service account email (for example: [e-mail]).

2. In Google Sheets / Google Drive:

   • Share each relevant Sheet or a containing folder with that service account email.

   • Give it at least Viewer access.

3. If you are using BigQuery external tables:

   • Make sure the same service account has access to the Sheets used by those external tables.

   • Confirm that your BigQuery project has the Google Drive API enabled and appropriate scopes (usually already handled by your data team).

Once this is done, Connecty can read the Sheets during sync and query execution.

---

Option 1: Connect Google Sheets directly

Use this if your business teams maintain key tables (e.g. targets, mappings, or override tables) directly in Google Sheets.

1. In Connecty, open Settings → Data Workspaces → Add Data Source.

2. Choose Google Sheets.

3. Select or enter:

   • The Google project / connection you want to use.

   • The Spreadsheet ID or URL and the tabs you want to expose as tables.

4. Save the connection and trigger a sync for that Data Workspace.

Important Data Transformations:

1. If its an Excel, convert it first to to Google Sheet format.

2. Pay attention to column data formats. For e.g. if a cell contains number, change cell format to Number, no commas, no currency.

3. Rename columns without spaces, separated by ‘_’ if needed, and in lower caps

4. Select range of values (exclude column title, rows like ‘Sum’ or ‘Total’, specify sheet) e.g. Sheet1!A2:Z

---

Option 2: Use Sheets via BigQuery

Use this if you already have BigQuery external tables (or views) defined on top of Google Sheets.

1. In Google Cloud / BigQuery:

   • Ensure your Sheets-based tables are configured as Drive-backed external tables or views.

   • Verify that the Connecty service account has access to the underlying Sheets.

2. In Connecty, connect your BigQuery warehouse (if not already):

   • Go to Settings → Data Workspaces → Add Data Source.

   • Choose BigQuery and complete the connection details.

3. Recommended checks:

   1. Map column names and format (e.g. number, string, date time) manually instead of auto-detect while connecting new Sheets to your Bigquery.

   2. Select range of values (exclude column title, rows like ‘Sum’ or ‘Total’, specify sheet) e.g. Sheet1!A2:Z

Modeling requirements?

If the Sheets store formulas or pivots, Connecty can help you with AI assisted modeling. Contact Support.