How to Connect Google Sheets to MySQL

Connecting software from different companies can be too complicated for many. As a Google Sheets user, however, you may want to export data to MySQL as it’s a powerful data analysis tool.

This article will explore some of the best methods for connecting Google Sheets and MySQL and moving data between them.

Build the Connection for Free Using Google Apps Script

Google Apps Script is a program for app scripting. It enables automation for Google applications and provides limited development options. Using Google Apps Script will require you to deploy an expert with proper knowledge of MySQL and Google App Script. That also implies that this process might be time-consuming. Also, to connect both programs you’ll need to use JDBC (Java Database Connectivity).

Before you start working with JBDC, you need to:

Whitelist several IP ranges. You can find them here.
Prepare connectivity information, including server, Name, Username, Password, and Port Number. It can look like this:
Server: sql.yoursite.netName: sqlabc123
Username: sqlxyz

Password: Testing123

Port Number: 2205
Copy the database information.

Now that you have everything ready, do the following:

Open Google Sheet and the Script Editor located at Tools>Script Editor.
Name your created Script Editor in a way to easily recognize it.
Apply the database information. Once a scripting template (code.gs) is loaded, you can delete it. In its place, you need to copy your database.
Using the information from step two, we need to enter the following:
var server = “sql.yoursite.net”var dbName = “sqlabc123”
var username: “sqlxyz”

var password: “Testing123”

var port = 2205
Now we can connect Google Sheets to MySQL database by creating a function and writing code.
Write the number of records that you want to transfer to MySQL.
Close the connection with “conn.close(); function.

Run the script. Note that before you do so, Google will ask for authorization, but only this one time.

Automated Solutions

If you would rather skip writing code, there are several automated solutions. They don’t require much technical knowledge, but you’ll need to purchase them. However, many companies are offering trials for their products. Here are some we recommend.

Klodio

Klodio is distributed as an add-on for Google Sheets, and it’s a recommended option for large companies. Some of their clients include Japanese online retail giant Rakuten and Netflix. This solution is quick to set up, and you can even use it to sync data across multiple sources. There’s no coding, as you’ll use drag and drop to create reports. Unlike many other automated solutions, Klodio supports two-way sync.

QueryClips

If your primary goal is to export MySQL data to Google Sheets, QueryClips might be for you. It requires no coding, and the result of queries can be easily shared. This is also a valuable tool for creating visuals to use in presentations. While QueryClips offers additional functionality, including data export to JSON and CSV, it’s not a solution for sending Google Sheets data to MySQL.

Blockspring

If all you need to do is export MySQL data to Google Sheets, Blockspring will make it simple. This is an add-on for Google Sheets that makes it quick to transfer a database. However, if you want to edit the data and then import it back in MySQL, you’ll have to use another solution.

Making a Decision

Both solutions have their advantages. The method involving Google Scripts is recommended if you’re a programmer or have one in your team. It consists of a lot of work while writing scripts, but you won’t have to invest in commercial solutions. However, keep in mind that this script will need to be periodically modified and tested to accommodate your business. If you need to hire someone to do it, you should ask about all future expenses. If you already have an expert in your company, this might be what you need, especially for small and medium organizations.

On the other hand, while some automated solutions need time to set up, most of them work out of the box. The main disadvantage is that these applications aren’t free. Most of them offer trials, while the price can be negotiated in some cases, especially if you’re a big company. For smaller organizations, you can get a less powerful but also more affordable solution. That’s why it’s important to pay only for the options you need to use.

Connect the Spreadsheet Data the Way You Want It

Hopefully, this article helped you identify one or more solutions for connecting Google Sheets and MySQL. All of them are recommended, but choose the one that offers the most for your organization. Also, keep in mind that some of them can’t transfer the data in both directions.

Which of these options is the most interesting to you? Do you need software that does one- or two-way transfer between Google Sheets and MySQL? Tell us in the comments section below.