Lernevate Team
Lernevate Contributor
Integrate Google Sheets with Apache Superset for data visualization and automation, eliminating manual updates and streamlining your reporting process.
Google Sheets is a versatile tool for tracking and analyzing data, but manually exporting and uploading data for reporting is inefficient. Connecting Google Sheets to Apache Superset automates the process, enabling real-time (or near real-time) data visualization and interactive dashboards. This guide walks you through integrating Google Sheets as a data source in Apache Superset.
The Challenge of Manual Data Updates
Google Sheets makes data collaboration easy, but extracting insights from spreadsheets requires additional steps. If you rely on manual exports, the process can become:
🚫 Time-consuming when dealing with frequent data changes
🚫 Error-prone due to manual copy-pasting
🚫 A bottleneck for real-time decision-making
One of our clients, a professional services company, used Google Sheets for tracking learning and development activities but struggled with generating dashboards from the data. Their analysts spent hours each week manually exporting data and updating reports. By integrating Google Sheets directly into Apache Superset, they transformed their workflow—eliminating delays and enabling instant access to up-to-date data.
Enable Google Sheets as a Data Connection in Apache Superset
1. Generate a Google Sheets Public URL
Apache Superset does not support direct OAuth authentication with Google Sheets, so the simplest way to connect is via a public CSV export link.
- Open your Google Sheet
- Click Share in the top-right corner of the sheet
- Under General Access, use the dropdown to update access to "Anyone with the link" (For production use-cases, refer to Step 2 about Google Cloud service credentials to use private sheets.)
- Click Copy Link (it should look something like 'https://docs.google.com/spreadsheets/d/.../edit?usp=sharing')
Ensure the data in the sheet is structured properly, with headers in the first row and no merged cells.
2. Add Google Sheets as a Data Connection in Superset
- Log in to your Apache Superset instance.
- Navigate to Settings > Data > Database Connections.
- Click + Database and select Google Sheets in the Supported Databases dropdown
- Add a Display Name for the connection (This defaults to "Google Sheets" which is fine in this simple example, but for more complex use-cases, this should be a strategic catalog name. In the learning and development world, this could be the name of the LMS where the data is being pulled from and into Google Sheets. This allows you to align different workflows/tools to separate database connections.)
- Leave Type of Google Sheets Allowed as the default of "Publicly shared sheets only" for testing purposes (For production systems, this should be set to "Public and privately shared sheets" with your organization's Google Cloud service account credentials to access the files. Here is a relevant Google Workspace article on that process.
- Add the Google Sheet Name and URL for all relevant sheets from step 1 (The Name should be the same name as the Google Sheet tab. If you would like to add more than one tab in a single Google Sheet, they need to be added separately. The URL would remain the same in those cases.)
- Press Connect (If you receive an error like 'The URL could not be identified. Please check for typos and make sure that ‘Type of Google Sheets allowed’ selection matches the input.', ensure you are inputting the correct share link from Step 1. If the tab/sheet name doesn't match, the connection will not throw an error here at database connection creation, but it will if you try to access the table from the SQL Lab.)
- For simple implementation after Database connected, select "CREATE DATASET". If you are a more advanced Apache Superset user, you can modify any of the settings here. These settings can be modified later as well.
3. Create a Virtual Table for Google Sheets
Now that SQLite is set up, create a virtual table pointing to your Google Sheets CSV.
-
Go to Data > Datasets.
-
Click + Dataset and select SQLite as the database.
-
Under SQL Query, enter:
CREATE TABLE google_sheets_data AS SELECT * FROM read_csv_auto('https://docs.google.com/spreadsheets/d/e/.../pub?output=csv'); -
Click Save.
4. Visualize Google Sheets Data in Superset
Once the dataset is created, you can start building dashboards:
- Go to Charts > + Chart.
- Select your newly created dataset from Google Sheets.
- Choose a visualization type (e.g., table, bar chart, line graph, etc.).
- Customize filters and metrics.
- Click Save & Explore to generate your report.
Automate Updates for Real-Time Data
Since Google Sheets is dynamic, you need to refresh the data periodically:
- Set up a Scheduled Query Execution in Superset to re-fetch data.
- Use Superset’s SQL Lab to create a recurring job that pulls data from the Google Sheets CSV.
- Alternatively, use a cron job or external script to update the SQLite database in the background.
Unlock the Full Potential of Your Data with Lernevate
Google Sheets and Apache Superset together offer a lightweight, scalable way to visualize data without heavy infrastructure. If you're looking to optimize your learning data workflows and reporting, our team at Lernevate is here to help.
Related Topics: