There are various ways to import data from Google Sheets into your data platform, which is integrated with ClickHouse. But if you are looking for a convenient one to transfer data from Google Sheets for BI and reporting purposes to ClickHouse, this article is for you.
Google Sheets is a product of Google that enables users to apply programmatic analysis to data without requiring them to code or maintain data pipelines. Users also want to gain access to BI-rich data on other platforms. This is when they are looking for a way to integrate Google Sheets and BI platforms with ClickHouse to turn their data into actionable insights. Some BI platforms for ClickHouse connected with Google Sheets to consider:
1. Connect Google Sheets to ClickHouse with Metabase
Metabase is an easy-to-use BI tool that allows users to ask questions about the data and display the answers in various graphs to easily read and process information. After connecting to ClickHouse using a JDBC driver, if you wish to collect data from Google Sheets to work with BI, there is no native integration.
The best way to perform an in-depth analysis of Google Sheets data with Metabase is to load Google Sheets data to a database or cloud data warehouse and then connect Metabase to this database and analyze data. This can be done by:
Using a third-party platform such as Stitch, Skyvia, or Hightouch... They offer solutions to extract data from Google Sheets, transform it, and seamlessly load the result to Metabase.
Using Metabase Google Sheets Add-on to import the result of a Metabase question to a Google Sheet by using the Metabase API.
It has two main functions that a user can access:
importQuestion: import a single question from Metabase using its question id number (you can find this at the end of a question URL) into the current Google Sheet tab
importAllQuestions: imports all questions in a Google Sheet using the following name convention: (metabase/123) -> imports question number 123
2. Connect Google Sheets to ClickHouse with Superset
In the previous article, we've discovered how to work with BI for ClickHouse in Apache Superset using Python. It allows users of various skill levels to easily explore and visualize their data using it, from simple pie charts to highly detailed geographic graphs.
To connect Google Sheets to Superset, you need to install the gsheetsdb driver that can be used to query data in Google Sheets.
Installing the gsheetsdb driver in your local environment
Add a driver to the ./docker/requirements-local.txt file and rebuild the Superset docker container
# Add the `gsheetsdb` driver
echo "gsheetsdb" >> ./docker/requirements-local.txt
# Rebuild your local image with the new driver baked indocker-compose build --force-rm
# Fire things back updocker-compose upAdding a Google Sheet as a database in Apache Superset
We can add the database to the Superset UI once the required driver is installed. Navigate to add a new database connection. Fill out the form by giving the database a name and entering the connection string gsheets://. Then click Save, and you are now ready to start querying Google Sheets.
3. Using Rocket.BI to connect to Google Sheets
Rocket.BI is a powerful business intelligence tool for data discovery, exploration, and visualization, natively integrated with ClickHouse. Rocket.BI is an ideal BI tool for ClickHouse as it helps turn ClickHouse data into beautiful charts and dashboards and then share insights with colleagues and external partners. See how Rocket.BI easily connected with ClickHouse here
If you feel connecting data from Google Sheets to the above platforms is too complicated and time-consuming, then Rocket.BI is the optimal solution for connecting to Google Sheets with just a single click.
Rocket.BI easily connects to Google Sheets seamlessly in some simple steps without additional setup methods. See our document about how to connect to Google Sheets
1. Click Add Data Source
2. In the menu, select Google Sheets
3. Click Access to Google Account in the pop-up window
4. Choose a Google account to continue and allow datainsider.co to access your data
5. Select one spreadsheet suggested on the list, then select one sheet contained in the selected spreadsheet
6. Click Next, your Google sheet will preview some first rows
7. Click Next to add Google Sheets job
Let's say you have a Google Sheet that contains sales data for your business, and you want to analyze this data in Rocket.BI. With Rocket.BI, you can easily connect to your Google Sheet and import the data into the platform for analysis. Here's how it works:
How about using Looker and BigQuery?
Similar to Metabase, Looker and BigQuery doesn't have a direct connector to Google Sheets. This acquires users to load Google Sheets data to a database or cloud data warehouse and then connect those tools to this database and analyze data.
You can look for data sources like MongoDB supporting direct integration with Looker or BigQuery to easily collect data from Google Sheets. There are also many other third-party platforms to help you extract and analyze data within minutes to work with BI for ClickHouse, such as Rocket.BI, which offers robust support for multiple data sources, catering to the diverse needs of users by seamlessly extracting and collecting data from various platforms and systems.
Did you know? Data Insider Rocket.BI is providing free trials with unlimited features, integrations, and visualisation capabilities. Visit https://www.datainsider.co/register to sign up for a free trial and experience full accessibility.
