How to connect to an Amazon RDS Instance using Google Apps Script?

In the previous post we have seen how to use Google Apps Script to capture Twitter Leads into Google Spreadsheets. Google Script has awesome features inbuilt that we can forget so many conventional ways through which we used to built web apps. With the advent of mobile applications and the opportunity Google Scripts provide in running powerful JavaScript code on Cloud without charging anything from the user has so much of potential if we can connect both of them. In this post let us see how we can use JDBC class of Google Apps Script can be used to connect to an Amazon RDS Instance. Are you thinking how is this even relevant if you are from an Analytics background? Think if you can build an Analytics dashboard solution using the powerful Amazon RDS database with simplest of simplest Coding on Google Apps Script. You never need to worry about the resources, uptime and speed of your solution!

Connecting to an Amazon RDS Instance using Google Apps Script

What is an Amazon RDS Instance?

Amazon RDS Instance is nothing but a pure Database hosted on Amazon cloud. Unlike Amazon EC2, RDS is a pure Relational Database solution. So you don’t need to worry about setting up all the servers on a command line. Amazon provides a pretty good Dashboard to monitor your database instance. However I suggest you to install MySQL WorkBench to work with your database easily if you are using a MySQL instance.

Configuring the RDS Instance

Setting up a RDS instance is very simple and straight forward. I will not be going into the details as it is out of scope for the present post. But just understand each and every step you are going through. If you would like to know how to setup a RDS instance, read it here. Since I was using a Free tier account, I selected a micro instance with backups turned off. You will be asked to provide the credentials with which you can connect to your database. Also you will be asked to select the version of the MySQL you want to use. For whatever reason the present JDBC drivers which Google Apps Script uses doesn’t properly work with MySQL’s 5.6 version. So try to select a 5.5 version of MySQL when you are creating a RDS instance. Once you have setup the RDS instance we can use MySQL Workbench to connect to the database and create a sample table. All you need will be the end point to which you need to connect and the credentials.

Setting up Security Rules for RDS Instance

By default when you setup a RDS instance the security group on which this instance is created, it only allows your present IP address to access the database remotely. For Google Script to access your database, you need to whitelist the Google’s ip addresses. Below are the ip addresses in CIDR notation you need to add.

You can go through these images to understand how to add the above IP addresses to the Security group to allow your database to accept inbound traffic from these IP Addresses.

amazon-rds-security-group-google-apps-script amazon-rds-security-group-inbound-rules-add-google-apps-script

Once you are done with the addition of these IPs your RDS instance is successfully configured.

Google Apps Script to connect to RDS Instance

Google Apps Script provides a service called JDBC which can be used to connect to external databases. Use the below script to connect to the RDS endpoint and get all the rows from a table called “user”

Save the script and run it. The above script should print all the rows in the Logs. You can open the logs and check the status. If something goes wrong somewhere try to read the error and understand what went wrong. You can read the Google Script JDBC documentation to understand how to create tables, write to tables and other tasks. Please leave a comment here if you have used this process to create interesting things.

Ram Manohar

Author: Ram Manohar

Independent Digital Analytics and Tag Management Consultant. Web Analytics Expert with 8+ years of experience. Well versed in Digital Analytics, Web Programming, WordPress and Digital marketing. Contact me for consulting!