objDB

objDB is a Google Script Library that encapsulates working with data from a spreadsheet and from a JDBC-connected database, using the same interface for both data sources.

It makes it possible to develop an application using a spreadsheet for storing data, and, after moving the data to a database, change one or two lines of code and use the data from the database using the JDBC service.

JDBC is a service in Google Apps Script that can make connections to databases. It supports MySQL, Microsoft SQL Server, and Oracle. The objDB library has not been tested with other database than MySQL.

How to include the library in your project

Script ID: 1o_O_ZkZm1GuTF5J1LYkfntUpiaT0sxDHyhZXL4fQma89mNIB65epbL6H

(For the legacy editor, use the project key: MJMF2lqsgWV-I-dlyqJN6OrljYCrJdQKl)

• Reference

• Source code

objDB basics

Data for the ObjDB consists of JavaScript objects: if you read data, an array of objects is returned, and if you write data, the data must be presented as a JavaScript object. Each row or record corresponds to an object, where the columns headers or field names are used as properties.

Below is a some sample data (taken from Google Apps Script - Storing Data - Spreadsheets).

To retrieve data, you could use, after opening db:

rows = objDB.getRows( db, 'Sheet1' );

resulting in [{FirstName:"John", LastName:"Berger", EmployeeId:3512, Department:"Sales", PhoneNumber:"(212) 123-4560" }, {{FirstName:"Patrick", ... ]

It is also possible to get only some of the columns, and only selected rows:

rows = objDB.getRows( db, 'Sheet1', ['FirstName','LastName'], {EmployeeId:567} );

resulting in [{FirstName=Maryanne, LastName=Packard}]

To insert new data, you provide the data in an object:

objDB.insertRow( db, 'Sheet1', {FirstName:"Leslie", LastName:"Scott", EmployeeId:1234, Department:"Engineering", PhoneNumber:"(212) 123-4564"} )

To update a row/record, e.g. move Patrick Benson to Marketing:

objDB.updateRow( db, 'Sheet1', {Department:"Marketing"}, {EmployeeId:1342 } )

And to delete Leslie:

objDB.deleteRow( db, 'Sheet1', {EmployeeId:1234 } );

Working with data in a spreadsheet

Given the spreadsheet ID ssId, a spreadsheet is opened using:

var db = objDB.open( ssId );

The first row of the spreadsheet should contain the headers. These are normalized (all non-word characters and initial numbers removed, case not changed) and used as properties for the objects. The data should start in the second row.

If there are extra rows, these can be skipped, as illustrated below:

var db = objDB.open( ssId );

objDB.setSkipRows( db, 'Sheet1', 1, 2); // Starting at row 1, skip 2 rows

Working with JDBC databases

See also the JDBC overview in the Google Apps Script documentation. The same parameters that are used in the JDBC.getConnection method are used here to open the database:

var url = 'jdbc:mysql://<host-ip>:3306/<database>';

var db = objDB.open( url, user, pass );

At the end, the connection can be closed using:

objDB.close( db )

Additional methods are available for working with JDBC database, such as explicitly formulated queries, and getting the last insertID. See the reference page for more details.