Google Sheets to MySQL: 2 Easy Methods

• February 18th, 2020

Google sheets to Mysql blog cover image.

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. 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. 

The article will introduce you to Google Sheets and MySQL Database. Furthermore, it will provide you 2 easy methods to move data from Google Sheets to MySQL in real-time. Read along to decide which method suits you the best!

Table of Contents

Introduction to Google Sheets

Google Sheets to MySQL: Google Sheets Logo
Image Source

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 collaborate on a single document, seeing your collaborators ’ contributions in real-time simultaneously. 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.

To learn more about Google Sheets, visit here.

Introduction to MySQL

Google Sheets to MySQL: MySQL Logo
Image Source

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 Sun Microsystems acquired MySQL AB 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 several 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.

Methods to Set up Google Sheets to MySQL Integration

Method 1: Using Hevo to Set Up Google Sheets to MySQL Integration

Get Started with Hevo for Free

Connecting Google Sheets (Free Data Source) 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 technologies, ensuring that your Google Sheets data always syncs to MySQL in a matter of a few minutes.

Hevo is fully managed and completely automates the process of loading data from your desired source and enriching the data, and transforming it into an analysis-ready form without writing a single line of code. Hevo’s pre-built integration with Github and PostgreSQL along with 150+ Sources (including 40+ free Data Sources) will take full charge of the data transfer process, allowing you to set up GitHub to PostgreSQL Migration seamlessly and focus solely on key business activities.

Sign up here for a 14-Day Free Trial!

Method 2: Using Google Scripts to Set Up Google Sheets to MySQL Integration

As its name implies, Google Apps Script is an app scripting platform that enables lightweight 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.

Methods to Set up Google Sheets to MySQL Integration

There are several ways that data can be migrated from Google Sheets to MySQL. Out of them, these 2 methods are the most feasible:

Method 1: Using Hevo to Set Up Google Sheets to MySQL Integration

Google Sheets to MySQL: Hevo Data Logo
Image Source

Hevo, a No-code Data Pipeline, helps you directly transfer data from Google Sheets and 150+ other data sources to Databases such as MySQL, Data Warehouses, BI tools, or a destination of your choice in a completely hassle-free & automated manner. Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled securely and consistently with zero data loss.

Hevo takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. You can also read our article about Google Sheets to Google Data Studio.

Hevo, a No-code Data Pipeline can help you overcome the shortcomings of the previous approach and move data from Google Sheets to MySQL for absolutely free of cost without writing a single line of code. With Hevo, you can load Google Sheets data to MySQL in 2 simple steps:

  • Configure Data Source: To connect Google Sheets with Hevo, log into your Hevo account and configure Google Sheets as a source as shown in the below image. To learn more about configuring Google Sheets as a data source, visit here.
Google Sheets to MySQL: Configuring Google Sheets as Source
Image Source
  • Configure Destination: To connect MySQL with Hevo, log in to your Hevo account and configure MySQL as a destination as shown in the below image. To learn more details about configuring MySQL as a destination, visit here.
Google Sheets to MySQL: Configuring MySQL as Destination
Image Source

Once completed, Hevo will immediately begin migrating data from Google Sheets to MySQL Database, allowing you access to your data in no time.

Advantages of Using Hevo

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.

Deliver smarter, faster insights with your unified data

Using manual scripts and custom code to move data into the warehouse is cumbersome. Changing API endpoints and limits, ad-hoc data preparation and inconsistent schema makes maintaining such a system a nightmare. Hevo’s reliable no-code data pipeline platform enables you to set up zero-maintenance data pipelines that just work.

Wide Range of Connectors – Instantly connect and read data from 150+ sources including SaaS apps and databases, and precisely control pipeline schedules down to the minute.

In-built Transformations – Format your data on the fly with Hevo’s preload transformations using either the drag-and-drop interface, or our nifty python interface. Generate analysis-ready data in your warehouse using Hevo’s Postload Transformation 

Near Real-Time Replication – Get access to near real-time replication for all database sources with log based replication. For SaaS applications, near real time replication is subject to API limits.   

Auto-Schema Management – Correcting improper schema after the data is loaded into your warehouse is challenging. Hevo automatically maps source schema with destination warehouse so that you don’t face the pain of schema errors.

Transparent Pricing – Say goodbye to complex and hidden pricing models. Hevo’s Transparent Pricing brings complete visibility to your ELT spend. Choose a plan based on your business needs. Stay in control with spend alerts and configurable credit limits for unforeseen spikes in data flow.

24×7 Customer Support – With Hevo you get more than just a platform, you get a partner for your pipelines. Discover peace with round the clock “Live Chat” within the platform. What’s more, you get 24×7 support even during the 14-dayfree trial.
Security – Discover peace with end-to-end encryption and compliance with all major security certifications including HIPAA, GDPR, SOC-2.

Get started for Free with Hevo!

Method 2: Using Google Scripts to Set Up Google Sheets to MySQL Integration

This example will explain, how to set up a connection that will create a table and populate it with the data from Google spreadsheets. It is assumed that the data is product listings with 2 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 here

Following steps are required to set up the Google Sheets to MySQL Integration:

  • Step 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
  • Step 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
  • Step 3: Copy the database details
  • Step 4: Go to your Google Sheet and open the Script Editor.
    1. Open Google Sheet file.
    2. Click Tools -> Script Editor.
  • Step 5: Name the Script Editor file (e.g. “MySQLConnection”).
  • Step 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. 
  • Step 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 
  • Step 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);
 }
  • Step 9: Create a function to write several 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();
 }
}
  • Step 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.

Limitations and Challenges of using Google Script Method

  • 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.
  • 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. 
  • 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.

Conclusion

The blog discussed Google Sheets and MySQL and explained their key features. Also, it provided a detailed explanation of 2 methods using which you can set up your Google Sheets to MySQL integration. The first method using Google Scripts although effective is time-consuming and requires a lot of code. You can use Hevo to set this integration and handle the ETL process.VISIT OUR WEBSITE TO EXPLORE HEVO

Visit our Website to Explore Hevo

Hevo helps you directly transfer data from a source of your choice like Google Sheets to a Database like MySQL, Data Warehouse, Business Intelligence tools, or any other desired destination in a fully automated and secure manner without having to write any code and will provide you a hassle-free experience.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Share your experience of connecting Google Sheets to MySQL in the comments section below!

No-code Data Pipeline for MySQL