How To Connect MySQL To Google Sheets Without Writing Code
Connecting MySQL to Google Sheets is a straightforward process you can complete without writing code. Here are three 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 to a spreadsheet.

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 today’s article, we’ll discuss three easy methods you can use to effectively connect Google Sheets to MySQL and ensure accurate, complete data transfer. 

However, before we begin, it is important to understand the two platforms and why they’re relevant in this use case, and whether synchronizing the two makes sense for your business.

Let’s get started.

Rather skip to the tutorial? Click here.

What Is MySQL?

 

Blog Post Image

 

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.

MySQL is also one of the most common and popular database management platforms available today. It is open-source and has a rich feature set with excellent reliability. It is also supported through regular updates by Oracle. 

MySQL is robust, quick, and easy to understand. Its popularity is evident across the internet: Facebook, Twitter, Flickr, Slack, Wikipedia, and YouTube all use MySQL backends.

What Is Google Sheets?

 

Blog Post Image

 

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

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 Through Estuary

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.

Blog Post Image

This image shows where you’ll be at the end of step 5, below.

Here’s how:

  1. 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 and choose the MySQL connector.
  3. Configure your MySQL database to meet the prerequisites. You can find them here
  4. Choose a unique name for your capture. 
  5. Provide the MySQL server address, database username (if you followed the prerequisites exactly as written, this should be “flow_capture”), and a password. 
  6. Click Next. Flow lists all the tables in your database. These will be converted into Flow data collections. You can remove any you don’t want to capture.
  7. Click Save and Publish.
  8. Click Materialize collections. 
  9. Choose the Google Sheets connector. 
  10. Choose a unique name for the materialization. Provide the URL for the spreadsheet and authenticate with Google. 
  11. Scroll down to view the Collection Selector. Each data collection you just captured from MySQL will be mapped to a separate tab in Google Sheets. Provide a Sheet Name for each. 
  12. Click Next, and then Save and Publish. 

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

After your free trial, Estuary Flow is just $0.75/GB of data streamed, without compromising timeliness.

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

Method 2: Script Configuration Through 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 the freshly built MySQL server using JDBC

Replace the username, password, host, etc., in the code provided below with the appropriate values.

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 check the logs to verify if the process to connect MySQL to Google Sheets was successful

 

Blog Post Image

 

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: Create a table in MySQL and run a query

 

Blog Post Image

 

Image Source

To test whether Google Sheets can now run SQL queries to read data from the MySQL server, run a test query.

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()

A successful test run means you were able to connect Google Sheets and MySQL.

Step 5: Automatically retrieve and refresh your MySQL data

If you want MySQL data to be downloaded and refreshed every minute, you can add this code to Google Sheets. This code maintains the Google Sheets format you specify while replacing the existing data in your selected sheet with data from your MySQL database.

  1. Append the following code to the end of your existing code.
plaintext
ScriptApp.newTrigger(‘readData’) .timeBased() .everyMinutes(1) .create();

The code will be executed to automatically retrieve and refresh MySQL after you click the Run button to provide access.

Method 3: Connecting With Zapier

 

Blog Post Image

 

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.

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.

Follow the basic guide below.

  1. Create an Account with Zapier.
  2. Authenticate Google Sheets and MySQL.
  3. Set up a trigger in MySQL which will kick off your automation.
  4. Choose a resulting action for Google Sheets.
  5. Specify the data you want to send from MySQL to Sheets.

Conclusion

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.