JDBC

Apps Script can connect to external databases through the JDBC service, a wrapper around the standard Java Database Connectivity technology. In Apps Script, the JDBC service supports Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases.

Setup for Google Cloud SQL

Google Cloud SQL lets you create relational databases that live in Google's cloud. Note that, unlike Apps Script, Cloud SQL is not free. Although lightly used databases don't cost much by themselves, Apps Script can only connect to Cloud SQL instances that have a static IP address, which can cost up to $88 per year ($0.01 per hour the connection is idle, as of May 2014).

To set up Cloud SQL with Apps Script, follow the steps below.

Create a Cloud SQL instance by completing the Try it now section of the Cloud SQL documentation.
When adding authorized networks to your Cloud SQL instance, add each of the following CIDR IP address ranges (one at a time) so that Apps Script's servers can connect to your database:
```
64.18.0.0/20
64.233.160.0/19
66.102.0.0/20
66.249.80.0/20
72.14.192.0/18
74.125.0.0/16
173.194.0.0/16
207.126.144.0/20
209.85.128.0/17
216.239.32.0/19
```
Copy the IP address that was assigned to your database. You'll use this to form the JDBC connection string.

Setup for other databases

If you already have your own MySQL, Microsoft SQL Server, or Oracle database, you can connect to it through Apps Script's JDBC service. First, you need to ensure that your database accepts connections from any of Apps Script's IP addresses. These are the address ranges you'll need to whitelist:

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

Note that the JDBC service can only connect to ports 1025 and above. Ensure your database is not serving off a lower port.

Sample code

The sample code below assumes you are connecting to a Google Cloud SQL database. The syntax for other databases will vary; consult their documentation. For more information on the JDBC methods, see the Java documentation for JDBC.

Create a database, user, and table

Most developers use the MySQL command-line tool to create databases, users, and tables, as shown in the Try it now section of the Cloud SQL documentation. However, it's possible to do the same thing in Apps Script, as shown below. It's a good idea to create at least one other user so that your script doesn't always have to connect to the database as root.

// Replace the variables in this block with real values.
var address = 'database_IP_address';
var rootPwd = 'root_password';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var root = 'root';
var instanceUrl = 'jdbc:mysql://' + address;
var dbUrl = instanceUrl + '/' + db;

// Create a new database within a Cloud SQL instance.
function createDatabase() {
  var conn = Jdbc.getConnection(instanceUrl, root, rootPwd);
  conn.createStatement().execute('CREATE DATABASE ' + db);
}

// Create a new user for your database with full privileges.
function createUser() {
  var conn = Jdbc.getConnection(dbUrl, root, rootPwd);

  var stmt = conn.prepareStatement('CREATE USER ? IDENTIFIED BY ?');
  stmt.setString(1, user);
  stmt.setString(2, userPwd);
  stmt.execute();

  conn.createStatement().execute('GRANT ALL ON `%`.* TO ' + user);
}

// Create a new table in the database.
function createTable() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  conn.createStatement().execute('CREATE TABLE entries '
      + '(guestName VARCHAR(255), content VARCHAR(255), '
      + 'entryID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(entryID));');
}

Write to the database

The examples below demonstrate how to write a single record to the database as well as a batch of 500 records. Batching is vital for bulk operations.

Note also the use of parameterized statements, in which the variables are denoted by ?. To prevent SQL injections, you should use parameterized statements to escape all user-supplied data.

// Replace the variables in this block with real values.
var address = 'database_IP_address';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

// Write one row of data to a table.
function writeOneRecord() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var stmt = conn.prepareStatement('INSERT INTO entries '
      + '(guestName, content) values (?, ?)');
  stmt.setString(1, 'First Guest');
  stmt.setString(2, 'Hello, world');
  stmt.execute();
}

// Write 500 rows of data to a table in a single batch.
function writeManyRecords() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);
  conn.setAutoCommit(false);

  var start = new Date();
  var stmt = conn.prepareStatement('INSERT INTO entries '
      + '(guestName, content) values (?, ?)');
  for (var i = 0; i < 500; i++) {
    stmt.setString(1, 'Name ' + i);
    stmt.setString(2, 'Hello, world ' + i);
    stmt.addBatch();
  }

  var batch = stmt.executeBatch();
  conn.commit();
  conn.close();

  var end = new Date();
  Logger.log('Time elapsed: %sms for %s rows.', end - start, batch.length);
}

Read from the database

This example demonstrates how to read a large number of records from the database, looping over the result set as necessary.

// Replace the variables in this block with real values.
var address = 'database_IP_address';
var user = 'user_name';
var userPwd = 'user_password';
var db = 'database_name';

var dbUrl = 'jdbc:mysql://' + address + '/' + db;

// Read up to 1000 rows of data from the table and log them.
function readFromTable() {
  var conn = Jdbc.getConnection(dbUrl, user, userPwd);

  var start = new Date();
  var stmt = conn.createStatement();
  stmt.setMaxRows(1000);
  var results = stmt.executeQuery('SELECT * FROM entries');
  var numCols = results.getMetaData().getColumnCount();

  while (results.next()) {
    var rowString = '';
    for (var col = 0; col < numCols; col++) {
      rowString += results.getString(col + 1) + '\t';
    }
    Logger.log(rowString)
  }

  results.close();
  stmt.close();

  var end = new Date();
  Logger.log('Time elapsed: %sms', end - start);
}

Closing connections

JDBC connections close automatically when a script finishes executing. (Keep in mind that a single google.script.run call counts as a complete execution, even if the HTML service page that made the call remains open.)

Nonetheless, if you know you're done with a connection, statement, or result set before the end of the script, it's a good idea to close them manually by calling JdbcConnection.close(), JdbcStatement.close(), or JdbcResultSet.close().

Showing an alert or prompt dialog also terminates any open JDBC connections. However, other showing UI elements — like custom menus or dialogs and sidebars with custom content — does not.