Connect MySQL to Google Sheets: 2 Easy Methods

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! 

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

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

How to connect MySQL to Google Sheets?

Method 1: 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

1. 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.
2. 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.
3. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.
4. 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.
5. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. 

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

What is Apps Script in Google Sheets?

1. Apps Script, based on JavasScript, is a built-in app development platform in Google Sheets.
2. It includes a Java Database Connectivity (JDBC) API that provides connectivity to Microsoft SQL, Oracle, and MySQL databases.
3. It covers several Visual Basic jobs for applications (VBA) in Excel.
4. 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.
5. You may customize a Google spreadsheet by adding custom menus, performing validations, or extending functionality over one or multiple sheets using an app script.

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

Additional Resources on MYSQL to Google Sheets

• Move Data from Google Script Connect to MySQL

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!

FAQ on MySQL to Google Sheets