Google Apps Script : Connectivity with Google Cloud SQL Database that lives in Google’s cloud

Google Cloud SQL is a MySQL database that lives in Google’s cloud. It has all the capabilities and functionality of MySQL. Google Cloud SQL is easy to use, doesn’t require any software installation or maintenance and is ideal for small to medium-sized applications.

Google Cloud SQL allows you to create, configure, and use relational databases that live in Google’s cloud. It is a fully-managed service that maintains, manages, and administers your databases, allowing you to focus on your applications and services.

By offering the capabilities of a familiar MySQL database, the service enables you to easily move your data, applications, and services in and out of the cloud. This enables high data portability and helps you achieve faster time-to-market because you can quickly leverage your existing database.

To learn about how to setup a Google Cloud SQL instance see the Getting Started guide.

Connect to a Google Cloud SQL instance:
We can connect to a Google Cloud SQL instance in the following ways:

• Using the command line prompt
• From the SQL prompt in the APIs console
• From admin and reporting tools
• From external applications
• From Google Apps Script scripts
• From App Engine Java application
• From App Engine Python applications

Here we are making connection to a Google Cloud SQL instance from Google Apps Script.

Connection to Google Cloud SQL instance From Google Apps Script :
Google Apps Script has the ability to make connections to databases via JDBC with the Jdbc Service.

Authorization :
In order to connect to an instance the user must be a member of the associated Google APIs Console project. Optionally, a user name and password can be specified to apply more fine-grained permissions. To learn more about access control, see access control documentation

Accessing Google Cloud SQL Databases:
We can connect to these databases in Apps Script, using the special method getCloudSqlConnection. This method works the same way as getConnection, but only accepts Google Cloud SQL connection strings.

var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://instance_name/database_name");

Once connected you can use the same code you would use to work against any MySQL database.

Writing to a Database :
This code will insert a record in person table in database

function insert() {
var fname="First Name"
var lname="Last Name"
var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://instance_name/database_name");
var stmt = conn.createStatement()
var query="insert into person(FNAME,LNAME) values('"+fname+"','"+lname+"')"
stmt.execute(query)
stmt.close()
conn.close()
}

Reading from a Database:
This code will read from database.

function read() {
var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://instance_name/database_name");
var stmt = conn.createStatement()
var query="select FNAME, LNAME from person"
var rs= stmt.executeQuery(query)
while(rs.next()){
Logger.log("First Name : "+rs.getString(1)+" , "+"Last Name : "+rs.getString(2))
}
rs.close()
stmt.close()
conn.close()
}