google-labs‎ > ‎

Accessing Google Cloud SQL from Apps Script

posted Feb 12, 2014, 10:29 AM by Bipin Sasi

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()
 }

 

 

Sample source code :

 

 

Code.gs

function doGet() {

var app=UiApp.createApplication();

 var conn = Jdbc.getCloudSqlConnection("jdbc:google:rdbms://instance/tablename");

  var stmt = conn.createStatement();

  stmt.setMaxRows(10000);

  var start = new Date();

 

   var rs = stmt.executeQuery("  SELECT * FROM authors");

 

  var grid=app.createGrid(21,4);

  var row = 1;

  while (rs.next()) {

 

      Logger.log(rs.getString(1));

       Logger.log(rs.getString(2));

        Logger.log(rs.getString(3));

     

     grid.setWidget(row, 1, app.createLabel(rs.getString(1)));

     grid.setWidget(row,2, app.createLabel(rs.getString(2)));

     grid.setWidget(row, 3, app.createLabel(rs.getString(3)));

 

     row++;

  }

  rs.close();

  stmt.close();

  conn.close();

  app.add(grid);

  return app;

};

Comments