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

Table of contents
Reading Time: 2 minutes

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.

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

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

Written by 

Rishi is a tech enthusiast with having around 10 years of experience who loves to solve complex problems with pure quality. He is a functional programmer and loves to learn new trending technologies. His leadership skill is well prooven and has delivered multiple distributed applications with high scalability and availability by keeping the Reactive principles in mind. He is well versed with Scala, Akka, Akka HTTP, Akka Streams, Java8, Reactive principles, Microservice architecture, Async programming, functional programming, distributed systems, AWS, docker.