How to Connect MySQL to Google Sheets: 3 No-Code Methods

Connecting MySQL to Google Sheets may not be the most common data integration workflow, but it’s an important one. Though databases are the most powerful and sophisticated way to store tabular data, some work is simply better suited for a spreadsheet environment like Google Sheets.

You might expect there to be a straightforward method built into Sheets or MySQL that would provide a seamless connection, but that isn’t the case. You’ll need to do some legwork or use a third-party tool.

In this article, we’ll explore three easy, no-code methods to effectively connect MySQL to Google Sheets and ensure accurate, real-time data transfer. But before diving into the tutorial, let’s understand why integrating these two platforms might be beneficial for your business.

If you're eager to jump straight into the methods and steps, click here to skip the background and dive into the tutorial.

What is MySQL?

Image Source

Founded in the 1990s, MySQL has gone on to become one of the most popular SQL-based relational database management systems. Maintained by Oracle, the platform is important to many software stacks and helps companies build and maintain powerful data-driven B2B services and customer-facing web applications.

Some of the internet's largest platforms, including Facebook, Twitter, Flickr, Slack, Wikipedia, and YouTube, rely on MySQL backends to manage their data. MySQL’s open-source nature, combined with its robustness, speed, and regular updates from Oracle, makes it a go-to choice for many businesses. 

What is Google Sheets?

Image Source

Google Sheets is Google’s cloud-based spreadsheet software. It emerged as part of Google’s strategy to bring office applications to the web and make them more accessible to everyone. 

In other words, Google Sheets is Google’s answer to Microsoft Excel.

Unlike MySQL, Google Sheets is an excellent tool for end-users because it’s approachable for non-technical professionals. It’s handy for modelling unique data or viewing a small subset of a large dataset. 

On the other hand, spreadsheets are not designed to handle massive datasets. Anyone who’s ever tried to analyze millions of rows in a spreadsheet (Excel or Google) knows this all too well.

Spreadsheets and databases inherently do very different jobs but complement each other well. 

Once data querying is complete from databases, the completion of analysis and its presentation is often performed in a spreadsheet-type environment. For instance, most companies require large accounting systems which are driven by commercial-grade databases. Teams can select a subset of data and extract it to Excel or Sheets to conduct financial analysis.

Should You Connect MySQL To Google Sheets?

Because the two platforms have different strengths and different target users, there’s lots of utility in connecting them. Here are some of the benefits of connecting MySQL to Google Sheets.

I. Helps Improve Reporting

Connecting MySQL to Google Sheets gives you an opportunity to re-shape data and improve how your dashboards look. Offloading this data to Sheets can be an easy way to modify large volumes and visualize it as well.

II. Makes Data Exporting Easy

Most data teams prefer setting up an automated pipeline between the two platforms rather than performing periodic exports. This linking helps keep data in sync as changes land in MySQL, so all users are working from the same source of trusth. 

III. Reduces Human Errors

Building on from the previous point, if companies resort to manual exporting datasets, the chances of missing key data are greater than by automating the process. Human errors can factor into data management and documentation too. By connecting MySQL to Google Sheets, you can mitigate this risk.

IV. Makes Data Accessible

Google Sheets patches the biggest hole left by Microsoft Excel: it is a collaborative environment and easy to understand by anyone familiar with spreadsheets. This quality makes Google Sheets data more accessible to those less familiar with business intelligence applications and SQL and helps facilitate data workflows.

How To Connect MySQL To Google Sheets: 3 No-Code Methods

Connecting MySQL and Sheets isn’t a straightforward process as many might suspect.

If you’re familiar with both platforms, you might be able to hand-code an integration. We won’t cover that method today because there are many easier ways.

These methods include Google app scripts and various third-party data connector apps. The specific method you pick is entirely dependent on your business needs and processes. Regardless of this fact, we recommend sticking with the simplest and no-frills option where you have to use a minimal amount of coding or scripting.

Simpler methods will help save you time and not make the process of MySQL integration with Google Sheets a tedious and tiring affair. This saves your engineers’ and analysts’ time, allowing them to focus on other projects and innovations.

Here are 3 popular ways to connect MySQL to Google Sheets without writing any code.

Method 1: Connecting MySQL to Google Sheets with Estuary Flow

Estuary Flow is a data integration platform with a unique selection of connectors. These include MySQL and Google Sheets Connectors. You can use these connectors to create a real-time data pipeline that synchronizes tables in a MySQL database with tabs in a Google spreadsheet with no code.

Here’s how: Follow these steps -

1. Sign Up or Log In: Go to the Estuary Web application. Sign up for a free trial if you don’t have an account yet.
2. Create a New Capture: Choose the MySQL connector.
3. Configure MySQL: Configure your MySQL database to meet the prerequisites.
4. Name Your Capture: Choose a unique name for your capture.
5. Provide Database Details: Enter the MySQL server address, username, and password.
6. Select Tables: Flow lists all the tables in your database. Remove any you don’t want to capture.
7. Publish and Materialize: Save and publish the capture. Then, choose the Google
8. Sheets connector to materialize the data.
9. Map Collections: Map each data collection from MySQL to a separate tab in Google Sheets.
10. Finish Setup: Click Save and Publish to complete the process.

All existing data from your MySQL database will be copied to Google Sheets, and new data that appears in MySQL will also be copied to Google Sheets in real time.

For more help with this method, see the Estuary Flow documentation on:

• How to create a Data Flow
• The MySQL connector
• The Google Sheets connector 

Method 2: Using Google Apps Script to Connect MySQL and Google Sheets

Step 1: Open the Script editor on your Google Sheet

1. Open a new Google Sheets spreadsheet and go to the Tools menu.
2. Pick the Script Editor option from the list. The script editor will open in a new tab with the sample function "myFunction()”. Use this file to define global variables or use the same function to write your logic. It might ask for certain Google account permissions.
3. Name your project on the Google Apps Script interface.
4. For your script to use JDBC to update an external database, it must first establish a connection to the database. SQL queries are then sent to make modifications. You must whitelist various IP ranges in your database settings in order to grant Apps Script access. Below is a list of the IP addresses.

plaintext
216.239.32.0 – 216.239.63.255
209.85.128.0 – 209.85.255.255
207.126.144.0 – 207.126.159.255
173.194.0.0 – 173.194.255.255
74.125.0.0 – 74.125.255.255
72.14.192.0 – 72.14.255.255
66.249.80.0 – 66.249.95.255
66.102.0.0 – 66.102.15.255
64.233.160.0 – 64.233.191.255
‍64.18.0.0 – 64.18.15.255

Step 2: Connect to MySQL server using JDBC

• Replace the placeholder values in the script below with your database credentials:

plaintext
var Database_Host = “sql6.freemysq.net” 
var Database_Name = “sql64555251” 
var Database_username = “sql6515120125” 
var Database_password = “LUDdnjvnjvnv” 
var Port_number = “3306” 
function getConnection() { 
var url = 'jdbc:mysql://'+Database_Host+':'+Port_number+'/'+Database_Name 
var conn = Jdbc.getConnection(url, Database_username, Database_password); Logger.log(conn); 
}

Step 3: Run and Verify the Connection

Image Source

• Before you execute and connect MySQL to Google Sheets, it could ask for Authentication Access for executing scripts. Users will therefore only need to grant access in order to continue. Now you may use your database to run any SQL query, including select, create tables, insert, and other functions.

Step 4: Execute SQL Queries in Google Sheets

Image Source

• Create a table or run SQL queries to interact with your MySQL database:

plaintext
var stmt = conn.createStatement(); 
stmt.execute("CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL);") 
stmt.close()

Step 5: Automate Data Retrieval

• If you want MySQL data to refresh automatically in Google Sheets, add the following code to your script:

plaintext
ScriptApp.newTrigger(‘readData’)
.timeBased()
.everyMinutes(1)
.create();

Method 3: Automating MySQL to Google Sheets with Zapier

Zapier is a data automation and web app integration tool that helps professionals automate tedious tasks for them. These tasks can be anything from web app management to data handling and querying. The platform also allows simple code-free integration and connection of Google Sheets with MySQL.

Steps to Set Up Zapier Integration:

1. Create an Account: Sign up with Zapier.
2. Authenticate Apps: Connect Google Sheets and MySQL to your Zapier account.
3. Set Up a Trigger: Set up a trigger in MySQL to start the automation.
4. Define an Action: Choose a corresponding action in Google Sheets.
5. Specify Data: Select the data you want to send from MySQL to Google Sheets.

Keep in mind that the speed of updates to Google Sheets — and the number of tasks you can perform — depends on the plan you’re using. You’ll use at minimum the Starter plan to use MySQL because it’s considered one of Zapier’s Premium Apps.

Conclusion: The Best No-Code Methods to Connect MySQL to Google Sheets

There are plenty of no-code options available for connecting MySQL to Google Sheets and we’ve just highlighted three today. 

Our recommendation for the best no-code solution is using Estuary, especially if you’re using the data integration platform for other purposes. With its powerful and seamless operation, Estuary Flow is the ideal software to extract MySQL data with low latency, which helps you replicate all data on both systems for analytical and operational uses.

If you’re interested in the product or want to learn more about Estuary, start your free trial or contact our team.