Tag Archives: database

ObjDB: the easy way to store data with Google Apps Script

There are various ways in Google Apps Script to store data, a common way is to store data in a spreadsheet. Spreadsheet data is relatively easy to access, and sample code is provided to read data from a spreadsheet into an array of JavaScript objects, and to write from objects to the spreadsheet. For more advanced database functions (update, select, delete), you have to do some more programming.

For larger applications, you may want to use MySQL or other databases via JDBC, Again, some sample code is provided, but this time not to read/write to and from JavaScript objects. A lot more coding is required, especially to link the fields in each record to the field name, instead of just the offset in the results rows.

To make it easier to work with data in Google Apps Script, both from spreadsheets and from JDBC databases, I have developed the ObjDB library. This is a set of functions, with which you can open a spreadsheet or database, and do all the basic operations: select, update, insert, delete. Functions are the same for spreadsheets and for JDBC databases, except the different parameters for the open() function, so if you start with data in a spreadsheet, and later move the data to a database, there is hardly any code to change.

To show how the library works, I prepared a demo spreadsheet with script. Open the link, it should create a new copy of the spreadsheet, if not, copy it yourself. Then go to ToolsScript editor…, and run any of the functions to see it in action.

Note that the script in this spreadsheet includes the objDB library, see under ResourcesManage Libraries….

For more documentation, reference and source code, go to http://googlescripts.harryonline.net/home/objdb.

Make life easier with SQL Views

In a complex database, with many tables, you often end up with long queries combining those tables. Rather than repeatedly writing out those queries, you can use SQL Views–turn those complex queries into a virtual table, and use that table instead. Requires hardly any storage space, always up-to-date, wish I had found this earlier.

In this blog post, I explain how SQL Views can be applied in the database structure of eZ Publish.Go to the post on share.ez.no.