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

15 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
  3. DeT Info Service

    Hi,
    thanks for this great Library!

    I have one question: I want to select rows that match a condition like “greater then”.
    For now i tried different syntax but cant get it working.

    var rows = objDB.getRows( db, ‘NamesList’,[] , {Name:’John’} );
    Thats easy, but i want to query all rows where the name is NOT empty.
    Or, i want to query all rows where the score is smaller then 10 and greater then 30.

    Any idea or help?

    Thanks again,

    Reply
      1. DeT Info Service

        Hi and thanks for your answer!

        Thats sad because I just switched from a “true” MySQL Database back to Google Spreadsheet. Just because my team needs to edit some data the easy way. Meanwhile I can deal with that ‘equal to’ thing but thinking about to use the query-methods for spreadsheet offered by Google too.

        There is an other question:
        I use your objDB to read and write to different sheets. Because i want to take advantage of the possibilities of spreadsheets I started to use formulas. Lets say, i got colums A and B for data and C for a formula. And lets say there are 4 rows of data. I found that your objDB returns even the colums with the values created by the formulas which is really nice and usefull.

        Because there will be more rows to be stored, i copied the formula in column C all the way down to row 1000, i think.

        What happens now, if I save a new row, is that its stored to row 1001. Obviously because objDB thinks there is data present in rows up to 1000. Is there any way to get arround that?

        Thanks again,

        Reply
        1. Harry Post author

          Hi,
          Come to think of it, there is an easy solution to selecting rows based on a “greater then” condition: create an extra column where you use a formula that gives TRUE if the condition is met, and select based on this column.

          If you use objDB, you should indeed use the first columns for values, the later columns for formulas. For reading, this does not make a difference, but for writing (updating or adding a row), it changes all cells up to the last column with a changed value. So if you write to column A and B, column C is not affected; if column B would be a formula, and you write to column A and C, column B would become a value. In your case, this seems to be done well, so that is no problem.

          The best way to deal with formulas in later columns, and adding these to new rows, is creating a script that is triggered when the document changes, and that copies the formulas from the last row that has these formulas, to the new rows (range.getFormulas() – range.setFormulas()). I might include that in a next version of objDB, but for now, you would have to write that script yourself.

          Reply
  4. Ken

    Harry did a great job with ObjDB to turn a spreadsheet into a database with simple db operations. I think using a spreadsheet with simple CRUD operations is good to prototype what you want to do before moving on into a mySQL database. Here’s my contribution below. Hope you like it. It queries the data and writes the output back to a scratchpad area of the spreadsheet! This will make ObjDB more useful if you need.

    The output of that .getRows query is the ‘rows’ array dump into the Log Output as a long string. I think the string is in JSON format. I added a few code lines to the getEngineers() function so that the ‘rows’ array gets row- and cell- parsed, and written back to the spreadsheet. Each value output to its own cell. I found the few code lines on the internet and adapted to this:

    function getEngineers()
    {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    SpreadsheetApp.getActiveSheet().getRange(1, 7, 6, 5).clear();

    ssDB = objDB.open( ss.getId());
    var rows = objDB.getRows(ssDB, ‘Tutorial Data’, [], {Department:’Engineering’});

    var allrows = [],
    singlerow;

    for (i = 0; i < rows.length; i++) {
    singlerow = rows[i];
    allrows.push([singlerow.FirstName, singlerow.PhoneNumber, singlerow.Department,
    singlerow.LastName, singlerow.EmployeeId]);
    }

    dataRange = sheet.getRange(1, 7, allrows.length, 5);
    dataRange.setValues(allrows);
    Logger.log( rows );
    }

    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>