I want to know if there is a way using Apps Script to connect to my Google Cloud SQL DB to execute a query.
I've read many posts and it seems that the only way to access to the DB Cloud SQL is to use the App Engine.
Anyone know a solution? Thank you
4 Answers
12
votes
Yes its obviously possible to connect apps script with Google Cloud SQL through JDBC.
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()
}
2
votes
From your Google Developers console, click Overview, go down to the middle of the page click, how to connect to your cloud SQL instance and click the link. Next select Java for the language go down until you find Connecting from an external network, find your url.
it should look like this:
url = "jdbc:mysql://your_address?user=root";
If IPv4 if enabled (numbers in 000.000.000.00 format)
Copy url leaving out ?user=root";
paste into var conn = Jdbc.getConnection("jdbc:mysql://your_address/
add /db_name and finished line of code should look something like this.
var conn = Jdbc.getConnection("jdbc:mysql://your_address/db_name”, user, userPwd);
Two things to remember
Allow the App script network ID’s first
Create database ahead of time to write to from Apps Script, it makes life much easier.
0
votes
