Google Script Connect to MySQL | 5 Easy Steps

Google offers many widely used online applications like Google Docs, Google Sheets, Google Apps Script, Google Maps, Google Drive, a database like Google BigQuery etc. In addition, Google applications offer unique features and can connect internal and external products via in-build libraries and APIs. 

One of the fast and versatile apps provided by Google is Apps Script. Apps Script is a Cloud-based scripting platform with a JDBC Service that can easily connect with many databases like MYSQL, Microsoft SQL Server and Oracle databases. In this article you will now learn how to Google Script Connect to MySQL in 5 easy steps.

What is Google Apps Script?

Google Apps Script is a cloud-based scripting platform developed by Google in August 2009. It offers a lightweight cloud-based application development platform to extend the functionality of Google Apps. Users can write simple javascript programs in Apps script to extend the standard features of Google Workspace Apps. 

Apps script is a code editor window. No installation is required. Various Google apps like Google Sheets, Gmail, Calendar, Drive, and Google BigQuery can effortlessly connect internally using Apps Script. In addition, Google apps can connect with external databases like Oracle databases, MySQL or Microsoft SQL Server with inbuilt JDBC services of Apps Script. 

Features of Google Apps Script

• Apps Script is a rapid, versatile, and no installation application development platform.
• Apps Script comes with JDBC services, which helps connect with external databases like Oracle databases, Microsoft SQL Server, and MySQL.
• Apps Script can easily interact with various Google services like AdSense, Google Analytics, Calendar, Google Maps, Sheets, Google Drive, Gmail, and Google BigQuery.
• Users can add custom menus, dialogues, and sidebars to Google Docs, Google Sheets, and Forms with Apps Script.
• Apps Script can automate the tasks and build add-ons for Google services.
• Apps Script can run macros in Google Sheets as well.

Google Apps Script Code Editor

• Step 1: Open Google Apps Script from this link.
• Step 2: Click on ‘New Project’ to open the code editor window.
• Step 3: Name your untitled project accordingly. For this demonstration, the project is named ‘MySQL Connect’ as shown below.

What is MySQL?

MySQL is a free and open-source Relational Database Management System(RDBMS) available under various proprietary licenses. It is a fast, reliable, and highly scalable database management system. It was first owned by MySQL AB and later bought by Oracle Corporation. My SQL is written in C and C++ and was initially released on 23 May 1995. MySQL runs on all major operating systems like Linux, Solaris, macOS, Windows, FreeBSD etc.

MySQL is one of the most popular database management system software. Being a relational database, MySQL stores data in tables, rows, columns, and indexes as a relational database. Users need server credentials to establish a connection with MySQL. With a JDBC connection to MySQL, users can create a database, insert a record and fetch records using Structured Query Language(SQL). Using JDBC, users can import MySQL data to Google Sheets and export Google spreadsheet data to the MySQL server via Google Apps Script.

Features of MySQL

• MySQL is freeware and easy to use and understand.
• MySQL is scalable can work with a large amount of data and clusters of machines.
• MySQL uses SQL Queries to combine information from multiple tables for their users.
• MySQL is highly adaptable and works with cross-platform: Windows, Linux, macOS, and Ubuntu.
• MySQL comes with data encryption and data backup for recovery features as well.
• MySQL provides a secured interface as it has an encrypted password-protected system while connecting to the server. In addition, MySQL provides secure connections using SSH and SSL.

MySQL Server Set-up

For MySQL Server hosting, you can use Free MySQL Hosting. It gives 5.00MB of free space.

• Step 1: Click on the link to open Free MySQL Hosting site. Then, click on ‘Start my Free Account’ and enter login details to register to the site. You will see the below screen after a successful registration.

• Step 2: Click on Start new database button to create a new database, as shown below.

• Step 3: Check register email-id for MySQL server details.

• Step 4: MySQL Server hosted successfully.

Google Script Connect to MySQL Steps

The target is to connect Google Apps Script with MySQL, create a table and insert data into the table.

Google Script Connect to MySQL Steps: Pre-requisites

• Step 1: The first step in Google Script Connect to MySQL is the MySQL Server must be hosted and running properly. Host MySQL Server as shown in the above section.
• Step 2: Open Google Apps Script Code Editor Window as described in the earlier section.

Google Script Connect to MySQL Steps: Set Up JDBC Connection with MYSQL

The next step in Google Script Connect to MySQL is to add the below code to Apps Script to connect to MySQL server using JDBC connection.

var server = "sql6.freemysqlhosting.net";
var port = 3306;
var db = "sql6481535";
var user = "sql6481535";
var pwd = "********";

/**
* Connect to MySQL.
*/
function connectmysql() {
  var url = "jdbc:mysql://" + server+":"+port+"/"+db;
  console.log(url)
  var conn = Jdbc.getConnection(url, user, pwd);
  Logger.log(conn);
  conn.close();
}

In the above code:

• Server: is the MySQL server name.
• Port: is the port of MySQL.
• Db: is the database name created.
• User: is the database user name.
• Pwd: is the password for the database user.
• Url: is the URL created to connect to MySQL server via JDBC connection
• connectmysql(): is the function to connect to the MySQL Server using JDBC connection.

Google Script Connect to MySQL Steps: Run Code

The next step in Google Script Connect to MySQL is to select the ‘connectmysql()’ function and run the code by clicking the Run button on the Apps Script window. 

In a few seconds, it will ask for permission to access data for once. Click on Review Permissions.

Choose your Google account to continue. Google hasn’t verified this app message will appear. Click on Advanced and then click on ‘Go to MySQL Connect(unsafe)’. Click on Allow to give Apps Script the required permission. This will make your script run.

See the execution log for errors or success messages. From the below image you can see the execution is completed successfully. JDBC connection to MySQL server is made successfully.

Google Script Connect to MySQL Steps: Run Script to Create Table and Add Data

Now for the next step in Google Script Connect to MySQL add code in the connectmysql() function to create a table and insert one row of data.

var server = "sql6.freemysqlhosting.net";
var port = 3306;
var db = "sql6481535";
var user = "sql6481535";
var pwd = "**********";

/**
* Connect to MySQL and write one row of data to a table.
*/
function connectmysql() {
  var url = "jdbc:mysql://" + server+":"+port+"/"+db;
  console.log(url)
  var conn = Jdbc.getConnection(url, user, pwd);
  Logger.log(conn);
  stmt = conn.createStatement();
  stmt.execute('CREATE TABLE emp (id INT(6), name VARCHAR(20), email VARCHAR(40));');
  console.log("Table Created successfully")
  stmt.execute("INSERT INTO emp (id, name, email) values (1, 'Anthony', 'anthony1456@gmail.com');");
  console.log("Data inserted successfully")
  conn.close();
}


See the execution log for errors or success messages. From the below image you can see the table created successfully and data inserted successfully.

Google Script Connect to MySQL Steps: Check MySQL Database for Results

Next to Google Script Connect to MySQL click on ‘Follow this link for phpMyAdmin’ on the freemysqlhosting.net window. Login using MySQL server and username details shared on emailId.

Table ‘emp’ with one row of data created in MySQL Server in the below screenshot.

Table created successfully and one row of data inserted successfully in MySQL via Google Apps Script.

Conclusion

Google Cloud Platform offers versatile services compatible with Google database Google BigQuery and provides excellent compatibility with external databases. The functionality of Apps Script and super rapport with different databases make it a widely used scripting platform nowadays. Follow the step-by-step instructions to Google Script Connect to MySQL and insert data in MySQL via Apps Script easily. 

However, as a Developer, extracting complex data from a diverse set of data sources like Databases, CRMs, Project management Tools, Streaming Services, Marketing Platforms to your MySQL Database can seem to be quite challenging. If you are from non-technical background or are new in the game of data warehouse and analytics, Hevo Data can help!

Visit our Website to Explore Hevo

Hevo Data will automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from 100+ multiple sources like MySQL to Cloud-based Data Warehouses like Snowflake, Google BigQuery, Amazon Redshift, etc. It will provide you with a hassle-free experience and make your work life much easier.

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 our unbeatable pricing that will help you choose the right plan for your business needs!