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/objdb.

This post is also available in: Dutch

29 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
  5. Ken

    I found out from testing that if you query from the data rows on a sheet and write the results back to the SAME sheet on an area set aside for query results, it will ‘break’ the objDB.* row functions! Yikes, they don’t work the second time anymore. I have to create a second sheet called ‘ScratchPad’ for writing back the query results to. To separate it from the first sheet where the data rows are.

    Replace this line from above

    SpreadsheetApp.getActiveSheet().getRange(1, 7, 6, 5).clear();

    with this

    var sheet = ss.getSheetByName(“ScratchPad”);
    sheet.getRange(1, 7, 6, 5).clear();

    A question about testing the data row maximum limits? That rows = objDB.getRows() statement to fetch all the rows from the spreadsheet. Will memory on the Google Sheet system become a problem? How many data rows can the ‘rows’ array be loaded with and handled for querying? 1,000 rows? 10,000 rows? 50,000 rows? 100,000 rows? Can I insert, update, and delete row 50,001 and beyond? As long as I stay within the maximum cell limits for a spreadsheet….

    Anyone testing the max row limits for objDB with the Google Sheet system memory? Any comments will be nice indeed…

    Reply
    1. Harry Post author

      You are right; the script treats the spreadsheet as a database, it can not write formulas. In practice, you can work well with spreadsheets with formulas in the same row as data, as long as you have data in the first columns, and formulas in the next. When updating a row, it will not replace the whole row, but only up to the last column for which you set a value.

      Take for example a simple spreadsheet, with three columns and at least three data rows under the header row: the header rows contains: ‘index’, ‘number’, ‘double’. The first columns contains a unique index, e.g. 1,2,3, etc., the second columns any value, and the third columns a formula ‘=2*B2’.
      function myFunction() {
      var db = objDB.open(SpreadsheetApp.getActiveSpreadsheet().getId());
      var rows = objDB.getRows(db, 'Sheet1'); // Contains all columns, including formulas
      // Wrong, overwrites formula
      rows[0].number = 2;
      objDB.updateRow(db, 'Sheet1', rows[0], {index:rows[0].index})
      // Better
      objDB.updateRow(db, 'Sheet1', {number:2}, {index:rows[1].index})
      // Alternative, load only data from sheet
      var altRows = objDB.getRows(db, 'Sheet1', ['índex', 'number'] ); // Contains all columns, including formulas
      altRows[2].number = 2;
      objDB.updateRow(db, 'Sheet1', altRows[2], {index:rows[2].index})
      }

      I’d be interested too if someone wants to test whether the script can handle the max row limits; and also how this affect the speed. The script is optimized for speed, reducing the number of calls to Google SpreadsheetApp services as much as possible, as these are slow, but the drawback is that the whole table will be in memory.

      Reply
  6. Bradley

    Hello, I was wondering if you can provide any advise for transfering data from google spreadsheets into mysql using your library. I’m currently building a monitoring project for my final year and I ‘m building a personal web interface with dashboards that would query the data from mysql and display them.

    Reply
    1. Harry Post author

      Hello Bradley,
      You can transfer data from Google Sheet to MySQL or the other way, that makes no difference. Use objDB.open both for the spreadsheet and the MySQL database, objDB.getRows to read all rows at once from the source, then loop through the rows and use objDB.insertRow to insert them row by row in the destination.

      Reply
  7. Guntis

    Thank you, thank you, thank you for this great library! It makes mysql database operations so easy!

    There is one query that produces error. Here is how.

    function testEscaping(){
    var url = “jdbc:mysql://” + DB.URL + “:” + DB.PORT + “/” + DB.DATABASE;
    var oc = objDB.open( url, DB.USERNAME, DB.PASSWORD );
    var rows = objDB.getRows( oc, ‘oc_product’, [], {model:”B23-U57-U57″} );
    Logger.log(rows);
    }

    Here is the Log:
    [15-11-06 16:17:19:008 PST] SELECT DISTINCT * from oc_product WHERE `model`=”B23-U57-U57″
    [15-11-06 16:17:19:031 PST] Exception in line 116.0: Value ‘0000-00-00’ can not be represented as
    [15-11-06 16:17:19:033 PST] testEscaping > getRows > objDB > objDB > objDB
    [15-11-06 16:17:19:033 PST] []

    Here is the affected row:

    INSERT INTO `oc_product` (`product_id`, `model`, `sku`, `upc`, `ean`, `jan`, `isbn`, `mpn`, `location`, `quantity`, `stock_status_id`, `image`, `manufacturer_id`, `shipping`, `price`, `points`, `tax_class_id`, `date_available`, `weight`, `weight_class_id`, `length`, `width`, `height`, `length_class_id`, `subtract`, `minimum`, `sort_order`, `status`, `viewed`, `date_added`, `date_modified`) VALUES
    (510, ‘B23-U57-U57’, ”, ”, ”, ”, ”, ”, ”, 3, 5, ”, 0, 1, 39.0000, 0, 9, ‘0000-00-00’, 0.00000000, 1, 0.00000000, 0.00000000, 0.00000000, 1, 1, 0, 0, 0, 0, ‘2015-08-26 21:57:21’, ‘0000-00-00 00:00:00’);

    Here is the table structure dump:


    — Table structure for table `oc_product`

    CREATE TABLE IF NOT EXISTS `oc_product` (
    `product_id` int(11) NOT NULL AUTO_INCREMENT,
    `model` varchar(64) NOT NULL,
    `sku` varchar(64) NOT NULL,
    `upc` varchar(12) NOT NULL,
    `ean` varchar(14) NOT NULL,
    `jan` varchar(13) NOT NULL,
    `isbn` varchar(17) NOT NULL,
    `mpn` varchar(64) NOT NULL,
    `location` varchar(128) NOT NULL,
    `quantity` int(4) NOT NULL DEFAULT ‘0’,
    `stock_status_id` int(11) NOT NULL,
    `image` varchar(255) DEFAULT NULL,
    `manufacturer_id` int(11) NOT NULL,
    `shipping` tinyint(1) NOT NULL DEFAULT ‘1’,
    `price` decimal(15,4) NOT NULL DEFAULT ‘0.0000’,
    `points` int(8) NOT NULL DEFAULT ‘0’,
    `tax_class_id` int(11) NOT NULL,
    `date_available` date NOT NULL DEFAULT ‘0000-00-00’,
    `weight` decimal(15,8) NOT NULL DEFAULT ‘0.00000000’,
    `weight_class_id` int(11) NOT NULL DEFAULT ‘0’,
    `length` decimal(15,8) NOT NULL DEFAULT ‘0.00000000’,
    `width` decimal(15,8) NOT NULL DEFAULT ‘0.00000000’,
    `height` decimal(15,8) NOT NULL DEFAULT ‘0.00000000’,
    `length_class_id` int(11) NOT NULL DEFAULT ‘0’,
    `subtract` tinyint(1) NOT NULL DEFAULT ‘1’,
    `minimum` int(11) NOT NULL DEFAULT ‘1’,
    `sort_order` int(11) NOT NULL DEFAULT ‘0’,
    `status` tinyint(1) NOT NULL DEFAULT ‘0’,
    `viewed` int(5) NOT NULL DEFAULT ‘0’,
    `date_added` datetime NOT NULL,
    `date_modified` datetime NOT NULL,
    PRIMARY KEY (`product_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=547 ;

    Reply
    1. Harry Post author

      Apparently, ‘0000-00-00’ is not a valid date representation in the JDBC connection, even though it works well in MySQL. The solution is to allow your date, datetime and timestamp fields to be NULL, and set them to NULL where they are ‘0000-00-00’ or ‘0000-00-00 00:00:00’.

      Reply

Leave a Reply

Your email address will not be published.