Google Script Connect to MySQL | 5 Easy Steps

Google Scripts provides an easy way to automate workflows and processes using JavaScript code that integrates with Google products. For databases, Google Scripts can connect with Google Sheets but accessing external data sources requires some configuration.

This guide will walk through 5 simple steps for Google Script connect to MySQL database. By establishing this integration, you can build scripts that interact with your MySQL data tables to automate tasks like backing up data, generating reports, sending alerts based on SQL queries, and more. No need to rely solely on Google Sheets anymore – unlock the full power of JavaScript automation with your MySQL data using these tips!

Apps Script MySQL Steps

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

Google Apps Script MySQL Steps

• 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 below section.
• Step 2: Open Google Apps Script Code Editor Window as described below.

Google Apps Script MySQL Connection Steps: Set Up JDBC Connection with MYSQL

Step3: The next step in Google Script Connect to MySQL is to add the below code to Apps Script to connect to the MySQL server using a 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

Step 4: 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 a Table and Add Data

Step 5: 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. You will see the table was 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 ‘Follow this link for phpMyAdmin’ on the freemysqlhosting.net window. Login using MySQL server, and username details are shared on email ID.

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

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

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

MySQL Server Set-up

• 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 the Start new database button to create a new database, as shown below.

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

• Step 4: MySQL Server hosted successfully.

Limitations of Google Script Connect to MySQL using the manual method

• Knowledge of Coding: Using Google App Script and MySQL requires expertise with knowledge of coding, which can create barriers for people who do not know how to code. 
• Time-Consuming: Using the manual method requires a lot of time for writing, debugging, and customizing code. 

Use cases for Google Script Connect to MySQL

• Custom Applications: It can be used to design custom applications for specific workflow needs.
• Automation: You can create automated workflows wherein real-time notifications can be sent on the updation of databases
• Connect to External Services: The Google Apps script can bridge your MySQL databases and external services like APIs or other analytics platforms. This helps in expanding the functionality of MySQL. 

Additional Resources on Google Script Connect To MYSQL

• Sync data Google Sheets to MySQL
• Move Data from connect MySQL to Google Sheets

Conclusion

• Google Cloud Platform offers versatile services that are compatible with the 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, and 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!