Google Script Connect to MySQL | 5 Easy Steps

• March 28th, 2022

Google Script Connect to MySQL FI

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.

Table of Contents

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.
Google Script Connect to MySQL: code editor
Image Source: Self

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.
Google Script Connect to MySQL: setup
Image Source
  • Step 2: Click on Start new database button to create a new database, as shown below.
Google Script Connect to MySQL: screenshot with database details
Image Source
  • Step 3: Check register email-id for MySQL server details.
  • Step 4: MySQL Server hosted successfully.

Simplify MongoDB & MariaDB’s ETL & Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline, helps load data from any data source such as Databases, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ Data Sources such as MySQL,  including 40+ Free Sources. It is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. 

Hevo loads the data onto the desired Data Warehouse/destination in real-time and enriches the data and transforms it into an analysis-ready form without having to write a single line of code. Its completely automated pipeline, fault-tolerant, and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.

GET STARTED WITH HEVO FOR FREE

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled securely and consistently with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Simplify your Data Analysis with Hevo today! SIGN UP HERE FOR A 14-DAY FREE TRIAL!

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. 

Google Script Connect to MySQL: run code
Image Source: Self

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

Google Script Connect to MySQL: auth
Image Source: Self

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: execution log
Image Source: Self

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: execution started
Image Source: Self

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!

No-Code Data Pipeline for MySQL