Connecting to MySQL Databases using Apps Script

Many web sites these days use a relational database to store data. You may, for instance, have an e-commerce site based on a shopping cart application (such as Zen Cart or osCommerce, among many others) that stores product descriptions, prices, sales transactions and more in a RDBMS (relational database management system). Alternatively, you may have a blog which, like this one, stores posts and associated data in database tables.

Being able to write to, and read from, these databases directly from your Google Sheet can be a huge advantage. In the e-commerce example, for instance, you may want to download order information for delivery completion, label printing or later sales analysis, or you may choose to upload new data from your spreadsheet to the on-line store including prices, product descriptions and so forth.

Google Apps Script can access many relational databases via its JDBC service, which is a wrapper around the standard Java Database Connectivity standard. In Google Apps Script, the JDBC service supports, as standard, the Google Cloud SQL, MySQL, Microsoft SQL Server, and Oracle databases.

Note: Google Apps Script has its own database, called ScriptDB, but this is now deprecated and is due to disappear in November 2014. If you are currently using ScriptDB, now is a good time to switch to an alternative, such as MySQL or one of the other RDBMSs described in this post.

To demonstrate, I’ve created a very simple one-table database in MySQL, perhaps the most-used RDBMS for the web:

Click image to enlarge

Click image to enlarge

In order to make a connection, I first need to use the getConnection() method of the JDBC service, using the general syntax shown below:

 // make the connection
 var connection = Jdbc.getConnection("jdbc:mysql://[database URL or IP]:[port number]/[database name]", "[user name]", "[password]");

Make sure that your database is open to remote connections before trying to connect (your web host can advise). Note also that JDBC service can only connect to ports 1025 and greater; since my database is using the default MySQL port of 3306, this isn’t a problem.

Now I can create and execute the query; for this example, I’m just going to select everything in the single table users. The data returned from the query will be stored in the variable result:

 // perform the query
 var SQLstatement = connection.createStatement();
 var result = SQLstatement.executeQuery("SELECT * FROM users");

My spreadsheet is ready to accept the data, with column headers listed in the first row; I want to start writing data into row 2 and onward:

Click image to enlarge

Click image to enlarge

So now I can select the destination range in my spreadsheet. I’ll start at cell A2:

 // choose a range in sheet
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var cell = ss.getRange('A2');

Now I’ll write out the records, looping through the records with a while(result.next()) loop and writing out four cells per row (one per data field) and one row per record:

 // loop through result object, setting cell values to database data
 var row = 0;
 while(result.next()) {
    for(var i=0; i<4; i++) {     // four fields per record
       cell.offset(row, i).setValue(result.getString(i+1));
    }
    row++;
 }

Finally, we need to tidy up and close the open connections:

 // tidy up
 result.close();
 SQLstatement.close();
 connection.close();

Here’s the code all together in the Script Editor:

Click image to enlarge

Click image to enlarge

Calling the function successfully imports the database data:

Click image to enlarge

Click image to enlarge

Of course, this is a deliberately simplified example. In practice, you may not know the names or the number of fields in a record; also your queries will probably not be limited to simple database reads via SELECT statements, but may well involve inserting, editing or deleting data with much more complex statements.

I’ll cover some of these situations in future posts. However, the connection procedure and basic querying concepts are the same, so I hope this post contains enough to get you started on your own.

[Of course, do keep your database access details private, don’t experiment on valuable data, do make suitable backups beforehand, and be especially careful if making (or allowing others to make) queries based on user-supplied data; all queries should be suitably ‘sanitized’ to prevent SQL injection and other evil deeds. You have been warned!]