Google Sheets to MySQL: Best Methods to Move Data in 2021

on Tutorial • February 18th, 2020 • Write for Hevo

Are you trying to move data from Google Sheets to MySQL to leverage the power of SQL for data analysis or are you simply looking to backup data from Google sheets? Whichever be the case, this blog can surely provide some help. 

This post provides 2 easy ways to move data from Google Sheets to MySQL. Here are the complete details on what you will learn in this guide:

How to move data from Google Sheets to MySQL

There are two popular methods to load Google Sheets data to MySQL.

Method 1: Using Google Apps Script

Google Apps Script, as its name implies, is an app scripting platform that enables light-weight development and automation for Google applications. While it is based on javascript (and a portion of ECMAScript) it runs server-side on Google’s infrastructure. This approach would need you to deploy an engineering resource that understands both Google App Script and MySQL. Given the tech involvement, this might be slightly time-consuming.

Method 2: Using a No Code Data Pipeline, Hevo Data

Connecting Google Sheets (or any SaaS application for that matter) is easy with Hevo and requires a fraction of the time to set up. By design, Hevo is constantly on top of changes in both the technologies so that your google sheets data is always synced to MySQL.

But, before we dive into the details of how to move  Google Sheets Data to MySQL, let us first understand these individual applications briefly.

What are Google Sheets?

Google Sheets is a free web-based spreadsheet program provided by Google. It allows users to create and edit spreadsheets but also, and more importantly, it allows multiple users to simultaneously collaborate on a single document, seeing your collaborators’ contributions in real-time. It’s a part of the Google suite of applications, a collection of free productivity apps owned and maintained by Google. Despite being free, Google Sheets is a fully functional spreadsheet program, with most of the capabilities and features of more expensive spreadsheet software. Google Sheets is compatible with the most popular spreadsheet formats so that you can continue your work. With Google Sheets, like all Google Drive programs, your files are accessible via computer and/or mobile devices.

What is MySQL?

Meanwhile, MySQL is an open-source relational database management system or RDMS, and it is managed using Structured Query Language or SQL, hence its name. MySQL was originally developed and owned by Swedish company MySQL AB, but MySQL AB was acquired by Sun Microsystems in 2008. In turn, Sun Microsystems was then bought by Oracle two years later, making them the present owners of MySQL. MySQL is a very popular database program that is used in a number of equally popular systems such as the LAMP stack (Linux, Apache, MySQL, Perl/PHP/Python), Drupal and WordPress, just to name a few and is used by many of the largest and most popular websites, including Facebook, Flickr, Twitter, and Youtube. MySQL is also incredibly versatile as it works on a variety of operating systems and system platforms, from Microsoft Windows to Apple MacOS.

At their core, both systems use cells to store, arrange, categorize, organize, analyze, and manipulate data. The only difference is that Google Sheets refers to them as spreadsheets or books (a book contains multiple spreadsheets), whereas MySQL refers to them as tables or databases (a database is a collection of tables). Migrating data from Google Sheets to MySQL database allows users to benefit from the additional power and capabilities of MySQL in gaining insights from said data. MySQL provides greater capacity in bulk editing, the volume of data, as well as ease in creating custom data sets that answer specific questions using SQL.

There are several ways that data can be migrated from Google Sheets to MySQL. In this article, we’ll look at two of those options.

Let’s take a deeper look at each option.

Method 1: Moving Data From Google Sheets to MySQL Database using Google Scripts

In this example, we’ll look at how to set up a connection that will create a table and populate it with the data from Google spreadsheets. Let’s assume that the data is product listings with two columns:

  • Product Name
  • Product Code

Note: Setting up a connection to a MySQL server requires using JDBC (Java Database Connectivity). If you’d like to take a more in-depth look, here’s the link to Google Apps Script JDBC documentation.

Scripting a connection between Google Sheets and MySQL

  1. To set up a JDBC connection to Google you must first ensure that several IP ranges are added to your SQL server’s whitelist. For the list of IP ranges, see here
  2. Obtain the connectivity information for your database. An example would look like this:
    Server: sql.mysite.net
    Name: sql123 Username: sqlabc
    Password: P@$w0rd
    Port Number: 3306
  3. Copy the database details
  4. Go to your Google Sheet and open the Script Editor.
    1. Open Google Sheet file.
    2. Click Tools -> Script Editor.
  5. Name the Script Editor file (e.g. “MySQLConnection”).
  6. Apply the database information
    1. A scripting template (Code.gs) will already be loaded. You may delete the content that’s there (Ctrl+A -> Del).
    2. Paste the new content (the database info) into the Code.gs. 
  7. Modify each line to reflect the format below:  
var server = “sql.mysite.net”
var dbName = “sql123”  
var username: “sqlabc”  
var password: “P@$w0rd”  
var port = 3306 

8. Create a function to connect Google Sheets to your MySQL database. An example function would be:

function openConnection() {
  var url = “jdbc:mysql://” + server + “:” + port + “/” + dbName;
  var conn = Jdbc.getConnection(url, username, password);
 }

9. Create a function to write a number of records from your Google Sheets file to your MySQL database. For example:

function writeRecords() {
 var sheet = SpreadsheetApp.getActiveSheet();
 var data = sheet.getDataRange().getValues();

 var stmt = conn.prepareStatement('INSERT INTO products ' +  '(productName, productCode) values (?, ?)');

 for (var i = 0; i < data.length; i++) {
  stmt.setString('Product name: ' + data[i][0]);
  stmt.setString('Product number: ' + data[i][1]);
  stmt.addBatch();
 }
}

10. Add the function to close the connection.

conn.close();

Run the script by pressing the Run Script icon
Note: The first time you run the script you will be asked to provide authorization to Google.

Google Sheets to MySQL Database using Google Scripts – Limitations and Challenges

  1. As you can see, this is not the easiest of options. Matter of fact, unless you are a coder or programmer, this is rather complex. Attempting to connect Google Sheets to MySQL using Google Apps Scripts has all of the issues inherent in programming a small applet: you’d need to find and hire someone who knows how to code and then, given your particular circumstances, they would have to tweak the code to ensure that it does exactly what you want. That takes time and money as the script would have to be tested and tweaked repeatedly.
  2. There’s an additional issue with using Google Apps Script. Google has set time limits on script executions. What that means is that if you are attempting to transfer large troves of data then these transfers may end up being prematurely halted. 
  3. Moreover, in case you are looking to clean and transform data before moving it from Google sheets to MySQL, you will need to build additional code to cater to that. This adds additional engineering overhead.

Method 2: Google Sheets to MySQL Using a No Code Data Pipeline

Hevo, a No Code Data Pipeline can help you overcome the shortcomings of the previous approach and move data from Google Sheets to MySQL, without having to write any code. With Hevo, you can load Google Sheets data to MySQL in two simple steps (Try by yourself here):

  1. Connect and authenticate Google Sheets.
    A screenshot showing the configuration of Google sheets as source in Hevo. 
  2. Configure Destination, MySQL in this case, where you want to move the data.
    A screenshot showing the configuration of mysql as destination in hevo.

Once completed Hevo will immediately begin migrating data from Google Sheets to your MySQL database, allowing you access to your data in no time. Here is a short overview of the Hevo platform:

The Hevo Advantage

The relative simplicity of using Hevo as a data pipeline platform, coupled with its reliability and data consistency, takes the difficulty out of data projects. However, additional benefits are available such as:

  • Low Time to Implementation – Can connect the source and move data in minutes.
  • Fully Managed – Hassle-free data load from Google Sheets to MySQL in real-time.
  • Alerts and Notification – Whenever there are unresolved inputs, you will be notified by either Slack or email.
  • Complete Data – Hevo’s fault-tolerant algorithms ensure that there is no data loss.
  • Scalability – Can add more data sources whenever needed. Additionally, Hevo is built to handle data of any size.
  • Data Cleaning and Preparation – Hevo can automate tedious tasks of cleaning, enriching data when moving data from Google Sheets to MySQL.

While Google Sheets does provide some impressive features, the capabilities for more advanced data visualization and querying make the transfer from Google Sheets to MySQL database a useful exercise. Hevo makes that easy for you. To explore how Hevo can be integrated into your marketing data analysis stack, give it a try by signing up for a 14-day free trial.

No-code Data Pipeline for MySQL