Google Apps Script

JDBC

Google Apps Script has the ability to make connections to databases via JDBC with the JDBC service. The current support extends to Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle.

Writing to a database

This example pushes 5,000 rows of data into a database. Note in particular the use of the batched versions — this runs considerably faster than the non-batched version. Batching is vitally important for any bulk operations.

function insert() {
  var conn = Jdbc.getConnection('jdbc:mysql://<host>:<port>/<instance>', 'user', 'password');
  conn.setAutoCommit(false);
  var stmt = conn.prepareStatement('insert into person (lname,fname) values (?,?)');
  var start = new Date();
  for (var i = 0; i < 5000; i++) {
    // Objects are accessed using 1-based indexing
    stmt.setObject(1, 'firstName' + i);
    stmt.setObject(2, 'lastName' + i);
    stmt.addBatch();
  }
  var res = stmt.executeBatch();
  conn.commit();
  conn.close();
  var endTime = new Date();
  Logger.log('Time is ' + (endTime.getTime() - start.getTime()) + 'ms');
  Logger.log('Res has ' + res.length);
}

Reading from a database

Here's a second example that performs the much more common read operations, and pushes the retrieved data into a Spreadsheet.

function foo() {
  var conn = Jdbc.getConnection('jdbc:mysql://<host>:3306/<instance>', 'user', 'password');
  var stmt = conn.createStatement();
  stmt.setMaxRows(100);
  var start = new Date();
  var rs = stmt.executeQuery('select * from person');

  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var cell = doc.getRange('a1');
  var row = 0;
  while (rs.next()) {
    for (var col = 0; col < rs.getMetaData().getColumnCount(); col++) {
      cell.offset(row, col).setValue(rs.getString(col + 1));
    }
    row++;
  }
  rs.close();
  stmt.close();
  conn.close();
  var end = new Date();
  Logger.log('Time elapsed: ' + (end.getTime() - start.getTime()));
}

Accessing local databases

Google Apps Script is a cloud scripting language — all of the work is done on Google's servers. In order for a script to talk to a database that is not hosted in the cloud, you will need to ensure that Google's servers can contact your database. Google may contact your database from any one of our IP addresses. Here is the complete range of IP addresses:

216.239.32.0 - 216.239.63.255
64.233.160.0 - 64.233.191.255
66.249.80.0 - 66.249.95.255
72.14.192.0 - 72.14.255.255
209.85.128.0 - 209.85.255.255
66.102.0.0 - 66.102.15.255
74.125.0.0 - 74.125.255.255
64.18.0.0 - 64.18.15.255
207.126.144.0 - 207.126.159.255
173.194.0.0 - 173.194.255.255

Accessing Google Cloud SQL databases

Google Cloud SQL allows you to create, configure, and use relational databases that live in Google's cloud. You 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. To learn more about how to setup a Google Cloud SQL instance see the Getting Started guide.

Authorization

As explained in the Google Cloud SQL access control documentation, in order to connect to an instance the user must be a member of the associated Google Developers Console project. Optionally, a user name and password can be specified to apply more fine-grained permissions.

For Apps Script, this means that the effective user for the script must be a member of the API project. Scripts that execute as the active user will not be able to connect to the database unless the end users are also members of the project.

Important considerations

JDBC connections to a database server are only supported to ports 1025 and higher.

JDBC connections are destroyed when calls are made to Ui.alert(), Ui.prompt(), Browser.msgBox(), or Browser.inputBox(). Note that other UI functionality does not break the connections in the same way.

Also note that it is very important to close statements and connections correctly. Failure to clean up properly can result in serious errors.

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.