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.

This post is also available in: Dutch

9 thoughts on “ObjDB: the easy way to store data with Google Apps Script

    1. Harry Post author

      According to the Google Apps Script documentation, JDBC works with MS-SQL, so this library should also work. But I don’t have an MS-SQL server, so if you check, please let us know how it works.

      Reply
      1. Jimson

        Hi Harry,

        I was just going through the script, Initally i was in search of JDBC script which can transfer/update data from MYSQL to/from Google spreadsheets.

        Through cloud i have tried to establish a connection from Xeround.com you can have a try.
        give us more examples for the same.
        waiting for your response

        Reply
        1. Harry Post author

          Hi Jimson,

          This library should do the trick.

          In Xeround, you create a database instance with any name. In the instance, you find the connection details, specifically the External DNS hostname, something like instance12345.db.xeround.com:7220

          In the database instance, create a database, objdb_demo for this example.
          Create the table:

          CREATE TABLE `tutorial_data` (
            `FirstName` varchar(64),
            `LastName` varchar(64),
            `EmployeeId` int(11),
            `Department` varchar(64),
            `PhoneNumber` varchar(64)
          );
          

          Now add the following function to the demo script:

          function copydb()
          {
           var ss = SpreadsheetApp.getActiveSpreadsheet();
            ssDB = objDB.open( ss.getId());
            var rows = objDB.getRows( ssDB, 'Tutorial Data'  );
            var xDB = objDB.open('jdbc:mysql://instance12345.db.xeround.com:7220/objdb_demo', '<user>', '<password>');
            for( var i=0;  i < rows.length;  i ++ ) {
              objDB.insertRow( xDB, 'tutorial_data', rows[i] );
            }
          }
          
          Reply
  1. Rajesh

    rows = objDB.getRows( db, ‘Sheet1′ );
    rows will given an entire sheet1 data
    rows[2] will give particular row data,
    My queries :
    how to retrieve particular cell value or
    copy selected rows data in flex table

    Reply
    1. Harry Post author

      Suppose you have a sheet with headers keyColumn, someColumn, anotherColumn, then
      rows = objDB.getRows( db, 'Sheet1', ['someColumn', 'anotherColumn'], {keyColumn:123} )
      will give only the columns someColumn and anotherColumn, and only for the row where keyColumn=123.

      See also ther examples in the script of the demo spreadsheet.

      Reply
  2. Bobby

    Is there an easy way to write the information from a query into a html table (similar to using the query with spreadsheets as shown here: http://acrl.ala.org/techconnect/?p=4001). However, I want a script that will display several tables on one web page. I really like your library, but I am trying to figure out how to get the information from the array of objects into a table.

    Thanks!

    Reply
    1. Harry Post author

      The library does not include a way to write the information to a HTML table.
      However, this is a common situation, there should be good examples of how to do it on the Internet.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Current ye@r *