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.
Below is a some sample data (taken from Google Apps Script - Storing Data - Spreadsheets).
To retrieve data, you could use, after opening db:
It is also possible to get only some of the columns, and only selected rows:
To insert new data, you provide the data in an object:
To update a row/record, e.g. move Patrick Benson to Marketing:
And to delete Leslie:
Given the spreadsheet ID ssId, a spreadsheet is opened using:
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:
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:
At the end, the connection can be closed using:
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.