How Superset queries Google Sheets as SQL tables
The main trade-off is convenience against control: a Google Sheet can become a queryable SQL table in Superset within minutes, but the sheet stays a spreadsheet that people edit by hand, and Superset treats it as a live source on every query. Superset reaches Google Sheets through the shillelagh[gsheetsapi] package and a gsheets:// connection string, and it authenticates either with service account credentials or with OAuth2. This page covers the service account path, which suits a shared, server-side connection where no individual user's Google login should drive the data access. Teams that have business data in spreadsheets, such as target lists, budget adjustments or reference mappings, and want it charted next to warehouse tables should decide early whether that data is small and stable enough to live in a sheet at all.
The connection works through shillelagh, which exposes each spreadsheet as a table that Superset can query with SQL. Because validation and queries run as the service account, the sheet has to be shared with the service account's client_email address before Superset can see it. This is the step most often missed: a sheet that opens fine in a personal browser will return access errors or empty catalogs in Superset until the service account is added as a viewer. Sharing with that one identity also means every person who can open the Superset chart can read the sheet's contents through Superset, regardless of whether they have Google access to the file. Confirm with your security team whether that matches how the sheet is classified before you share it. Check the current shillelagh and Superset documentation for exact connection options and supported credential formats, since these details change between releases.
What you need before connecting a sheet to Superset
Connecting a Google Sheet to Superset depends on four things lining up: a Superset image that carries the right Google Sheets driver, a Google Cloud identity that Superset can authenticate as, the Sheets API turned on in that project, and a network path from the Superset workers to Google. Work through these before anyone edits a database connection, because each one fails in a different way and the error messages rarely point to the cause.
A Superset image with the gsheets driver
Superset does not ship the Google Sheets driver in every build, so the image that runs your web server and workers must include the shillelagh[gsheetsapi] package. The Superset database catalog lists this package for the gsheets:// connection string. Confirm the exact Superset and shillelagh versions in your own image against that catalog, then pin them so a rebuild does not silently change driver behavior.
A Google Cloud project and service account
Create or choose a Google Cloud project that your organization owns and create a dedicated service account for Superset. The key fact you need from it is the client_email value, because every private sheet must be shared with that address. Ask who owns the project, who can create keys, and whether your policies allow service account keys at all, since that decides how credentials reach the metadata store or secret manager.
Google Sheets API enablement
The service account can only read sheets through the Google Sheets API, so that API must be enabled in the same project that issued the credentials. Check this before testing the connection, because a disabled API produces errors that look like permission problems. Record who approved the API enablement and whether your Google Workspace administrators restrict third-party or service account access in your domain.
Network egress from the Superset workers
Queries and catalog validation run from the Superset processes that execute them, so the web tier and any Celery workers that touch these databases need outbound HTTPS access to Google's API endpoints. Egress rules, proxies and firewall allowlists in restricted networks often block this path. Test from the same host and network namespace that runs Superset, not from a laptop, and confirm with your network team which hosts are permitted.
Confirmed sheet owners and sharing rules
Each sheet that Superset will query needs a named owner who agrees to share it with the service account and who knows what data it holds. Ask for the sheet inventory, the sensitivity of each file, and whether the sheet is a working document that people edit by hand. Sheets change shape easily, so agree on which tabs and column headers Superset may depend on before you build charts on them.
Configuring the gsheets connection in Superset
This sequence takes a self-hosted Superset from a standard image to a working Google Sheets connection, with each sheet shared deliberately and verified with a test query. Work through it in a staging environment first so credentials and sharing settings are proven before production users depend on them.
Build the image with the Google Sheets extra
Your platform team adds the shillelagh package with its Google Sheets API extra, shown in the Superset database catalog as shillelagh[gsheetsapi], to the Superset image or its Python environment. Check that the library imports inside the running web and worker containers, since the connection will fail at query time if the driver is missing from either. Confirm the installed version against the Superset release you are running, because driver requirements can change between releases. Done means the import succeeds in every container that runs queries.
Create the service account and key
In your Google Cloud project, an administrator creates a dedicated service account for Superset, enables the Google Sheets API for that project, and generates a JSON key. Keep the key out of source control and store it in your secrets manager, then restrict who can read it. Note the client_email value in the key file, because every spreadsheet you want Superset to query must be shared with that address. Done means you hold a key file, the API is enabled, and the client_email is recorded for the sharing step.
Add the gsheets database connection
A Superset administrator creates a new database connection in the Superset interface and selects the Google Sheets engine, or uses the gsheets:// connection string from the catalog. Paste the service account credentials into the connection's credential field and save the connection without exposing the key in logs or screenshots. Confirm the exact field names and credential format in the Google Sheets documentation for your Superset version. Done means the connection saves and the test connection action reports success.
Share each private spreadsheet with the service account
For every private spreadsheet that dashboards will use, the sheet owner shares it with the client_email from the key file and grants at least viewer access. Catalog validation and queries run as the service account, so a sheet that is not shared will not appear or will return an access error even when the connection itself is valid. Keep a short register of which sheets are shared, by whom and why, since that list is your access record. Done means each sheet opens for the service account and is listed in the register.
Test the connection against each shared sheet
Using the Superset SQL Lab, a data owner runs a simple query such as selecting a few rows from each shared sheet, treating the sheet tab as a table. Compare the returned row count and column headers with what the owner sees in Google Sheets. Repeat for each sheet, because a missed share shows up as an error on one sheet only. Done means every test query returns the expected rows and columns, and any failure is traced to a sharing or header problem before a dataset is built.
Add datasets and build the first chart
A Superset builder adds each verified sheet as a dataset, sets column types and metric definitions, and builds one chart on a dashboard to confirm end to end behavior. Check that the chart values match the sheet totals that a data owner has checked by hand. Record the dataset owner and the sheet it reads from, so a later change to the sheet's layout is traceable. Done means the chart renders, its values match the source, and the dataset is documented.
Confirm access and document the connection
Verify that a non-administrator Superset account can see only the datasets it is meant to use, since the service account holds the sheet access, not the individual viewer. Write down the sheets shared, the service account used, the key rotation owner and the point at which a sheet should move into a database. Done means access tests match your intended model and the documentation is stored with your other Superset runbooks.
How sheet tabs, headers and permissions map to Superset datasets
This table shows how each part of a Google Sheet becomes part of a Superset dataset and where access is decided. Read it before you register a sheet, because the sharing setting on the sheet, not the Superset role, controls what the service account can read. The main takeaway is that Superset roles govern who sees the chart, while the Google sharing setting governs what data Superset can reach.
| Sheet element | How Superset uses it | What to check before go-live | Where it usually goes wrong |
|---|---|---|---|
| Spreadsheet URL or ID | Identifies the file that the gsheets:// connection reads through the service account | Confirm the ID in the URL matches the file the business owner expects | Teams copy a similar file, such as a monthly version, and build dashboards on the wrong one |
| Tab name | Becomes the table name in the Superset SQL Lab and dataset picker | Agree tab names with the owner and avoid renaming tabs after datasets exist | A renamed tab breaks saved datasets and charts that reference the old name |
| Header row | Supplies column names for the table | Keep one header row in row one, unique and without merged cells | Merged or blank header cells produce unclear or duplicated column names |
| Column data types | Superset infers types from the values in each column, which drives filters and aggregations | Spot-check that numeric and date columns hold consistent values | Mixed text and numbers in one column cause sums and date filters to return unexpected results |
| Sharing with service account | Google grants the service account client_email read access, and every query runs under that identity | Share each private sheet with the client_email from the credential file, and record who approved it | Only the Superset team shares the sheet, so the data owner never knows it is exposed to Superset |
| Superset roles | Control who can open charts, dashboards and SQL Lab for the dataset | Set dataset and dashboard access in Superset, and review it after each role change | Teams assume a role restriction also hides the sheet from the service account, which it does not |
| Sheet owner's Google access | Governs the sheet for people who edit it in Google Drive, separate from Superset | Confirm the owner can still edit and revoke sharing without involving the Superset team | Access is granted to a personal account, and the sheet becomes unreachable when that person leaves |
Why impersonation does not carry user identity into Sheets
The Google Sheets documentation in the database catalog lists OAuth2 as an alternative to service account credentials. OAuth2 is the only option in which a sheet read could be tied to an individual person's Google session. Whether Superset passes each user's token through to shillelagh for every query, and how it handles refresh and failure, must be confirmed in the current shillelagh and Superset documentation before you design around it.
- Identity of the call: The intended identity under OAuth2 is the signed-in user's Google account, so the sheet should be read with that user's permissions if the connection is configured that way.
- Consent and token handling: Each user must grant access, and Superset must store and refresh tokens. Confirm where tokens are kept, how long they last and who can read them.
- Shared dashboards: A chart opened by a dashboard viewer may run under a different identity than the author. Test shared dashboards, cached results and scheduled reports separately.
- Setup effort: OAuth2 requires a Google Cloud OAuth client and consent configuration that your administrators own. Ask your implementation partner to list that work in the written scope.
Confirm each behavior with a test account that has no access to the sheet before you rely on per-user permissions.
With a service account, Superset authenticates to Google as one identity, identified by the service account's client_email. Catalog validation and queries run as that identity, so Google checks access against the service account, and Superset does not check it against the person who is logged in. Any user who can reach a database connection built on these credentials can query whatever the service account can read.
- Sharing step: Private spreadsheets must be shared with the service account client_email before validation or queries succeed. Sharing is the access boundary Google applies to that identity.
- Broad reach: A sheet shared with the service account is readable by every Superset user who can use that connection, unless Superset roles and dataset permissions narrow that reach. Confirm how database and dataset permissions apply to the connection.
- Credential custody: The service account key is a secret. Store it in a secrets store you control, plan rotation and restrict who can view the connection configuration.
- Google-side logs: Expect Google's access records to name the service account rather than the Superset user who ran the query. Confirm what Google records for your sheet types.
The Google Sheets configuration page is the natural starting point for confirming the connection and sharing steps.
The identity model shows up most clearly in audit evidence and access reviews. Superset records queries against its own users, while Google's records of sheet access name the service account. Row-level security in Superset filters rows in the SQL that Superset generates. It shapes what a role sees within a query result, but it does not change what Google allows the service account to read from the sheet.
- Two logs, two identities: Superset query history ties a query to a user, and the Google side ties the same read to the service account. Map the two records together in your evidence pack.
- Row-level filters: Use Superset row-level security to limit what each role sees within a sheet, and check the generated SQL to confirm the filter applies. Sheet-level read access still comes from sharing.
- Access reviews: Reviewing who can see a sheet means reviewing the Google sharing list for the service account and the Superset roles that reach the connection. Both lists must be reviewed.
- Offboarding: Deactivating a Superset user does not remove access that the shared service account already has. Key rotation and sharing changes are the controls that change what the service account can read.
Ask your auditor which evidence they will accept for each control before the build starts, since the audit trail for Sheets will differ from the trail for a database connection.
Securing the service account and the sheets it reads
A Google Sheets connection in Superset is only as tight as the service account behind it. The controls below cover where the key lives, which sheets the account can read, how the key is rotated, who can change the connection and who creates datasets from it. Each one is a decision your team should make before the first sheet goes live.
Store the key in encrypted fields
The service account credentials are entered in the database connection, so they sit in Superset's metadata database rather than in a config file on disk. Confirm that the SECRET_KEY used by your deployment is set to a value you control and stored in your secrets tooling, because that key protects encrypted database fields. Check that the key never appears in dashboards, exports or logs, and that database backups are encrypted and access-restricted.
Share each sheet with Viewer access
Private spreadsheets must be shared with the service account's client_email, and that email should only ever receive Viewer access. Share individual spreadsheets rather than a whole Drive folder or shared drive, so the account can read only the files a dashboard needs. Editor access is not required for querying and would let a leaked key change source data.
Use a dedicated service account
Create one service account for Superset's Google Sheets connections and do not reuse an account that other systems depend on. A dedicated identity makes its activity easy to isolate in Google Cloud audit logs and lets you revoke it without breaking unrelated workloads. Ask your Google Workspace administrator whether organization policies restrict service account key creation or external sharing, since those policies can block the design.
Rotate keys and remove unused ones
Service account keys do not expire on their own, so set a rotation schedule and record who owns it. When you rotate, add the new key, update the Superset connection, verify a query, then delete the old key in Google Cloud. Review the list of keys for each service account regularly and remove any that no longer map to a running connection. Confirm your rotation procedure against your own change process before relying on it.
Limit who can edit database connections
Anyone who can edit a database connection can view or replace the credentials it uses and point it at a different source. Restrict the database connection permissions in Superset's role model to a small administrator group, and keep sheet-backed connections separate from connections used by general analysts. Review this group during access reviews, the same way you review other privileged roles.
Log who creates sheet datasets
Creating a dataset from a sheet defines what other users can query, so dataset creation deserves its own audit trail. Use Superset's event and audit logging to record dataset and chart creation, and confirm which events your version records before you rely on them. Pair this with Google Drive activity logs for sharing changes, so a new share of a sensitive sheet is visible even when no one has yet built a dashboard on it.
When a sheet should move into a database
Keep a Google Sheet in Superset only while it is a small, slow-changing reference that a few people edit and one or two charts read. Move it into a database when several of these conditions hold: the sheet feeds many dashboards or datasets, its column layout must stay stable for downstream SQL, it contains sensitive rows that need database-level access control, several people or automated processes write to it at the same time, or you need an audit trail of who changed which value and when. A spreadsheet can satisfy none of these reliably, because the service account that Superset uses reads the sheet as a single identity, and Google Sheets has no row-level permissions that Superset can enforce for each dashboard viewer.
The usual migration path is a scheduled load, not a live link. The data team owns a job, run by an orchestrator or cron, that reads the sheet with the same service account, validates the column names and types, and writes the result into a table in your warehouse or PostgreSQL. Superset then points at that table, and the sheet becomes a source that people edit, not a dependency that dashboards query at render time. Confirm the load frequency, the validation rules and who gets alerted when a row fails, because these decide whether dashboards show stale or broken numbers. Keep the sheet shared with the service account until the load is retired, and remove that sharing afterward. The Google Sheets documentation in the Superset repository covers the connection side you will be retiring.
Common Google Sheets connection failures and fixes
This table lists the failures teams most often hit when a Google Sheet is queried from Superset, with the likely cause and the fix for each. Read it when a connection test or chart query fails, and confirm exact error text against the Superset version you run, since messages change between releases.
| Failure | Likely cause | Fix |
|---|---|---|
| Catalog validation or query fails with a permission error | The spreadsheet is private and not shared with the service account client_email | Share the sheet with the client_email from the service account JSON as a viewer, then retest the connection |
| Connection string is rejected or the driver is not found | shillelagh[gsheetsapi] is missing from the Superset image, so the gsheets:// dialect is not registered | Add shillelagh[gsheetsapi] to the image build, rebuild, and redeploy every web and worker container |
| Connection string is malformed or points at the wrong target | Wrong gsheets:// format, or the sheet URL pasted without the correct spreadsheet identifier | Copy the sheet URL from the Google Sheets docs page and check the connection string against the Google Sheets documentation for your version |
| Google API errors about access being disabled | The Google Sheets API or Google Drive API is not enabled in the project that owns the service account | Enable both APIs in that Google Cloud project, wait for propagation, then retry (confirm the required APIs in the Superset Google Sheets docs) |
| Columns show as text or chart types fail | Header row is missing, duplicated, blank, or sits below notes, which breaks column name and type inference | Put one clean header row at the top, use unique names with no blank cells, and keep data in a single table block |
| Queries on large sheets are slow or time out | A spreadsheet is treated as a live table with no indexing, so every chart scans the full sheet through the API | Reduce the range, cache dashboard results, and move the data to a database once volume or refresh frequency grows |
Questions teams ask before connecting Google Sheets to Superset
What does connecting Google Sheets to Superset cost in effort, and who does the work?
Most of the effort is internal coordination and a small amount of configuration, not a large build. The work splits into three parts: Google Cloud setup (creating a service account and its credentials), a Superset change (installing the shillelagh[gsheetsapi] package into the image and adding a gsheets:// database connection), and sharing each relevant sheet with the service account's client_email. Your team pays for its own time in all three. If you hire a partner, the fee follows from a written scope that lists which sheets, which roles and which images are in play. Ask for the one-time items (image change, initial connection, testing) separately from any recurring work, such as updating the image when Superset upgrades or rotating service account keys.
How long does setup usually take, and what drives it?
Duration depends on the number of sheets, the access model and how your image is built, not on the connection string itself. A single internal sheet shared with one service account is quick to connect. Duration grows with the number of sheet owners who must share their files, the number of separate service accounts or Google Cloud projects you need, whether your Superset image is built and promoted through a controlled pipeline, and how many test cycles your change process requires. Ask any provider to break its timeline into Google Cloud access, image change, connection setup, sheet sharing and testing, and to name the steps that depend on other teams.
What must our team provide before the work starts?
Three groups of access and decisions are needed before the work starts:
- Google Cloud access: permission to create or reuse a project, enable the Google Sheets and Drive APIs, and create a service account with a key that is stored in your secrets process.
- Sheet owners: named people who can share each spreadsheet with the service account's client_email and who will tell you when a sheet changes shape or moves.
- Image changes: permission to add shillelagh[gsheetsapi] to your Superset image, rebuild it and deploy it through your normal staging and production path.
Confirm with your security team whether service account keys are allowed in your environment at all, and whether your Google Workspace policy permits external sharing to a service account.
Can an internal engineer set up Google Sheets in Superset alone?
Yes, a capable engineer who already runs Superset can usually complete the technical steps alone, provided they have Google Cloud permissions and access to the image build. The harder part is governance: deciding which sheets are approved, who owns each one and how access is reviewed. Those decisions need a business owner, not only an engineer. A single engineer also becomes the only person who understands the setup, so document the service account, the connection string format, the sharing list and the rebuild steps in your runbook. Consider outside help if the team has no one who can change the Superset image or if the sheet inventory is large.
Does Superset respect each user's Google identity when they query a sheet?
Not by default with a service account. Catalog validation and queries run as the service account, so the spreadsheet must be shared with its client_email. Anyone who can use the database connection in Superset can then reach every sheet shared with that account, regardless of their Google permissions. Impersonation of individual users is limited in this setup, so treat the service account as a shared reader. If different groups must see different sheets, plan separate connections, separate service accounts or a move of the data into a database that supports row or role controls. Confirm the exact behavior of your Superset version before you promise per-user access to anyone.
When should a sheet move into a database instead?
Move a sheet into a database when it becomes a system of record, when several dashboards depend on it, when access must be restricted by user or group, or when it grows large enough that queries slow down or fail. Spreadsheets have no enforced schema, no transactional writes and limited audit history, so edits can silently break charts. A governed database table, loaded from the sheet by a pipeline you operate, gives you typed columns, permissions and logging. The cost is that someone must own the load process and its schedule. Ask whether the sheet is a source of truth or a convenience input before you choose the connection.
When is it worth bringing in a consultant?
Bring in a consultant when the Google Sheets connection is part of a wider Superset rollout with access controls, SSO or compliance evidence to produce, or when your team cannot change the Superset image or manage Google Cloud service accounts within your change process. A written scope from DeployOpen covers the Google Cloud setup, the Superset image change, the sheet sharing plan, testing and handover, and it is agreed before work starts. Ask for the deliverables, the assumptions about your environment and the items you must provide, so the quote reflects your actual sheets and controls.
How DeployOpen scopes and delivers the Sheets integration
DeployOpen plans, implements, documents and supports self-hosted open-source platforms inside your own infrastructure, and that includes connecting Apache Superset to Google Sheets through shillelagh. Engagements are fixed-scope, so the sheets, service account, permissions and handover items are agreed in writing before any build starts.
How we scope
- Inventory which sheets must be queried, who owns them, and whether they hold data that belongs in a database.
- Define the service account, its Google Cloud project, and the sharing model for each spreadsheet in the written scope.
- Record the Superset image changes needed to install shillelagh[gsheetsapi], and confirm them against your current release.
How we build
- Install the gsheets:// connection in a staging Superset, validate catalog access, and test queries against each shared sheet.
- Test that sheet edits, deleted rows and renamed columns behave as expected in dashboards and saved charts.
- Document the sharing steps, credential storage and secret rotation so changes to a sheet do not break reporting unexpectedly.
How we hand over
- Deliver a runbook covering service account ownership, sheet sharing rules, and the steps to move a sheet into a database.
- Walk your platform and data owners through how to request new sheets and who approves sharing with the service account.
- Hand over the configuration, test results and open items in writing so your team can operate the connection without us.
How we support
- Support ongoing upgrades to Superset and shillelagh, testing each change in staging before production.
- Review sheet sharing and service account access on a schedule agreed in the scope, with ongoing support available as a retainer.
- Advise when a sheet has outgrown Google Sheets and should be migrated into a database, with a separate written scope for that work.
