Connect MySQL to Google Sheets: 2 Easy Methods

One of the widely used Database Management Systems used by businesses globally is MySQL. It offers a scalable, reliable, and robust relational database solution. Another free-to-use tool is Google sheets which allows you to store data in a tabular format & generate stunning reports & dashboards.  

Integration helps us seamlessly work across applications. You might have heard of using excel with many applications. Did you know that you can also connect MySQL to Google Sheets? All for free! 

In this article, you will learn how to connect MySQL to Google Sheets using 2 different methods.

What is MySQL?

MySQL is a free, open-source database that connects databases to applications to enable successful database administration. MySQL achieves its purpose even in the most demanding applications while assuring optimal speed, full-text indexes, and unique memory caches for increased performance. It is a dependable, powerful, and stable solution with sophisticated features.

Whether it’s an eCommerce website that receives a million queries per day or a high-speed transactional processing system, you can easily handle data using MySQL. MySQL has unrivaled scalability, allowing you to run deeply embedded programs with a reduced footprint, even in enormous warehouses with terabytes of data. 

MySQL’s standout feature is its on-demand flexibility. The open-source solution allows eCommerce organizations with specific database server requirements to customize it completely.

What are Google Sheets?

Google spreadsheets are online Excel sheets for personal or business purposes, which are entirely free of charge to use. When it comes to managing data and planning figures, Google Sheets, like Microsoft Excel, is helpful. Google sheets also have Excel-like features like data validations, filters, drop-down menus, and a few simple equations to make things easier. 

Google sheet is free to use and offers the significant advantage of it getting stored online in Google Drive and shared with anyone as per need.

What is Apps Script in Google Sheets?

Apps Script, based on JavasScript, is a built-in app development platform in Google Sheets. It includes a Java Database Connectivity (JDBC) API that provides connectivity to Microsoft SQL, Oracle, and MySQL databases. It covers several Visual Basic jobs for applications (VBA) in Excel. You may customize a Google spreadsheet by adding custom menus, performing validations, or extending functionality over one or multiple sheets using an app script. Most people use these scripts to automate specific operations like connecting MySQL to Google Sheets to make our day-to-day work easy and fast.

What do you need to connect MySQL to Google Sheets?

You can get several benefits if you connect MySQL to Google Sheets:

•  It will help create a variety of sales pipeline dashboards and reports, update and modify significant volumes of data, and visualize vast amounts of data.
• It will be necessary to manually export and update your MySQL datasets and copy and paste them into Google Sheets if your data is not linked. If you’re working with enormous volumes of data, this will take an excessive length of time. 
• Furthermore, the manual technique leaves room for a great deal of human error. It also allows you to make your data more accessible to those less familiar with business intelligence applications and SQL by connecting your MySQL database to Google Sheets.

How to connect MySQL to Google Sheets?

To connect MySQL to Google Sheets, follow the 2 different methods given below:

• Method 1: Manually connect MySQL to Google Sheets
• Method 2: Automate the process to connect MySQL to Google Sheets using Hevo Activate

Method 1: Manually connect MySQL to Google Sheets

Follow the simple steps given below to manually connect MySQL to Google Sheets:

• Step 1: Open the Script editor on your Google Sheet
• Step 2: Connect to the freshly built MySQL server using JDBC
• Step 3: Run and check the logs to verify if the process to connect MySQL to Google Sheets was successful
• Step 4:  Create a table in MySQL and run a query
• Step 5: Automatically retrieve and refresh your MySQL data

Step 1: Open the Script editor on your Google Sheet

• Step 1: Navigate to the Tools menu on a new Google Sheets spreadsheet.
• Step 2: Choose Script editor from the selection. A new tab will launch a script editor with the sample function myFunction(). You may use this file to define global variables or write your logic in the same function. It may ask for some permissions from your Google account.

• Step 3: On the Google Apps Script interface, give your project a name.

• Step 4: Your script must first establish a connection to the database to update an external database using JDBC. You then make changes by sending SQL queries. To allow Apps Script access, you’ll need to whitelist numerous IP ranges in your database settings. The IP addresses are listed below.

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

Copy the code given below and replace values for variables such as username, password, host, etc.

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

It may ask for Authentication Access for executing scripts before you run and connect MySQL to Google Sheets. As a result, all users will have to do now is provide access and go on. You may now execute any SQL query like Select, Create table, Insert, or any other function on your database.

Step 4:  Create a table in MySQL and run a query

 Run a test query to see whether Google Sheets can execute SQL queries to read data from MySQL server. 

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 indicates that you were able to connect MySQL to Google Sheets.

Step 5: Automatically retrieve and refresh your MySQL data

You can add a code to Google Sheets that will download and refresh your MySQL data every minute. This code replaces current data in your chosen sheet with data from your MySQL database while retaining the Google Sheets format you specified.

• Step 1: Append below code at the end of your existing code.

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

• Step 2: Allow access by clicking the Run button, and the execution of code will happen to retrieve and refresh MySQL automatically.

This method allows you to manually connect MySQL to Google Sheets via Google Scripts. However, it often requires the technical expertise in Google Scripts as well as a portion of your Engineering team’s time and efforts to set up & maintain a seamless connection. You can remedy this by automating it via the second method described below.

Method 2: Automate the process to connect MySQL to Google Sheets using Hevo Activate

Hevo Activate helps you directly transfer data from Google BigQuery, Snowflake, Amazon Redshift, Facebook, etc., to CRMs such as Salesforce, HubSpot, various SaaS applications like Google Sheets, Data Warehouses, and a lot more, in a completely hassle-free & automated manner.

Hevo Activate 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 in a secure, consistent manner with zero data loss.

Hevo Activate takes care of all your data preprocessing needs and lets you focus on key business activities and draw a much more 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. 

Check out what makes Hevo Activate amazing:

• Real-time Data Transfer: Hevo Activate, with its strong Integration with 100+ sources, allows you to transfer data quickly & efficiently. This ensures efficient utilization of bandwidth on both ends.
• Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
• Secure: Hevo Activate has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Tremendous Connector Availability: Hevo Activate houses a large variety of connectors and lets you bring in data from numerous Marketing & SaaS applications, databases, etc., such as Airflow, HubSpot, Marketo, MongoDB, Oracle, Salesforce, Redshift, etc. in an integrated and analysis-ready form.
• Simplicity: Using Hevo Activate is easy and intuitive, ensuring that your data is exported in just a few clicks. 
• Completely Managed Platform: Hevo Activate is fully managed. You need not invest time and effort to maintain or monitor the infrastructure involved in executing codes.
• Live Support: The Hevo Activate team is available round the clock to extend exceptional support to its customers through chat, email, and support calls

Sign up here for a 14-Day Free Trial!

You can easily configure Google Sheets as your target destination by following the simple steps given below:

• Step 1: Click Activate in the Asset Palette.
• Step 2: Do one of the following:
  • Select the TARGETS tab in the Targets List View, and click + CREATE TARGET.

• Select the ACTIVATIONS tab in the Activations List View, and click + CREATE ACTIVATION.

1. In the Select Warehouse page, select your Activate Warehouse or click + ADD WAREHOUSE to add a new warehouse. Read Activate Warehouses to configure the selected Warehouse type.
2. In the Select a Target page, click + ADD TARGET.

• Step 3: In the Select a Target Type page, click on Google Sheets.

• Step 4: In the Configure your Google Sheets account page, select the authentication method for connecting to Google Sheets.

• Step 5: Do one of the following:
  • Connect using a User Account:
    1. Click + ADD GOOGLE SHEETS ACCOUNT.
    2. Select the Google account associated with your Google Sheets data and click ALLOW to authorize Hevo to access your data.
  • Connect using a Service Account:
    1. Attach the Service Account Key. Read Downloading the key file for steps to create a new key.Note: Hevo supports only JSON format for the key file.
    2. Click CONFIGURE GOOGLE SHEETS ACCOUNT.
• Step 6: In the Configure your Google Sheets Target page, specify details such as a unique Target Name, your Google Drive folder(optional) and your Google Spreadsheet.

• Step 7: Click TEST & CONTINUE. You can view the new Target in the Targets List View. If you are creating an Activation, you return to the Select Data to Synchronize page. Refer to section, Field Mapping in Google Sheets to know how to map the Warehouse fields to your selected Google spreadsheet.

Conclusion

In this article, you have learned how to effectively connect MySQL to Google Sheets using 2 different methods. The first method allows you to connect MYSQL to Google sheets via Google scripts manually. You can say that google app script is a powerful scripting language inside Google spreadsheets. It enhances and expands the capabilities of Google Sheets and performs a variety of automation. MySQL is one of the additional features utilized with applications script.

Applying the Google Apps Script strategy will need a lot of information and technical capabilities, and it can take some time and effort. While using Google Apps Script often requires minimal coding, it necessitates the deployment of engineering personnel familiar with MySQL and Google Apps Script. This can be a time-consuming and resource-intensive task if you require to transfer data from MySQL to Google Sheets frequently. For a more effortless and efficient approach, you can go for the second method and use a No-Code Automated solution like Hevo Activate!

Visit our Website to Explore Hevo

Hevo Activate helps you directly transfer data from a source of your choice such as Snowflake, Amazon Redshift, Facebook, etc., to any SaaS application like Google Sheets, CRMs like Salesforce, etc., in a fully automated and secure manner without having to write the code repeatedly. It will make your life easier and make data migration hassle-free. It is user-friendly, reliable, and secure.  

Want to take Hevo for a ride? Sign Up for a 14-day free trial and simplify your Data Integration process. Do check out the pricing details to understand which plan fulfills all your business needs.

Tell us about your experience of learning how to connect MySQL to Google Sheets! Share your thoughts with us in the comments section below.