Google Apps Spreadsheet Reporting Scripts
In an effort to create an integrated cloud and open source Business Operating System (BOS), we have been doing a lot of work with Google Apps Spreadsheets and Google Apps Script for dynamic end user reports with data from our internal MySQL databases.
Since GAS provides JDBC access to databases, this seamed pretty easy. But the devil is in the details. We have had JDBC connected Gapps spreadsheets running as our primary reporting tool for operations and sales for over 6 months now, with mixed results.
Have been developing a set of scripts to make connecting Google Apps to MySQL databases easier, they can be found here, with an explaination at the end of this post.
The good
There is a lot of the good... The ability to very rapidly give detailed access to data-based reports to both internal and external users has been a god send.
The ability to simply "invite" external users has allowed us to fairly elegantly grant access to our customers to relevant reports.
Internally, the ability to create "generic" reports which business users can then use the built-in (fairly basic) pivot ability.
With some base scripts, it is possible to create a whole new report, tailored to business needs within minutes.
Built in versioning helps with detecting end-user errors. With the wide variety of widgets available in GAS UI scripts, it is easy to embed end user help, which is quite nice.
But there are some problems. Gapps can sometime be very slow without any apparent reason. Mysterious "server errors" pop up every now and again. While a simple ctrl+f5 almost always resolves these problems, it can be frustrating to end users.
Spreadsheets with thousands of rows can sometimes be quite slow, and frustrating to end users. What is strange about this is that the slowdown seems to be quite random, with really no ryhme or reason.
Also, GAS seems to still be quite buggy, nothing huge, but quite a few small things. For example the JDBC getters do not seems to work correctly with quite a few data types, so they need to be extracted as text. The documentation can be a bit spotty for GAS as well, but having used OpenSource tools for the past years, am pretty used to that.:)
One big bummer is the inability to create a "library"of scripts which can be included in other scripts or spreadsheets, but I assume this is a security issue with Google, so I am not holding my breath. We have experimented using Javascript eval and http getting to pull scripts from bitbucket,with fairly unpredictable results, and have given up on that.
The ugly
These things could be deal breakers for some, but we are able to work around them.
- There is no way to dissallow viewing of script contents to users. Why is this a big deal?
- Since we are using JDBC, we need to put dbaccess information in the script iteself, including username and password. Since we an not dissallow users from viewing scripts, we are exposing authentication information to all of our users, which is not great.
This is a big deal for us, and are looking for ways around this:
Some ideas come to mind.. I.
- Put end user usernames into mysql, then using their google usernames and passwords?
- Have the database username and pasword information be entered by the end user using a GAS UI script. This could be annoying to end users, and we would have to create and manage a lot of passwords on the MySQL server.
The code we have been using has evolved over the past months, and will continue to in the coming months.
Currently the system is pretty simple, we send call any stored procedure on the Mysql database (the SP takes card of getting whatever data we want, so we have a bit of a tiered architecture). The stored procedure can have zero or more parameters which are sent by the GAS script. The script dynamically builds the procedure call, so any number of parameters can be used.
Once the SP is called, the script then takes care of:
- Creating column headers based upon the column names in the result set.
- Retrieves the values using the correct data type by using the record set meta-data.
- Put simply the resulting rows into the sheet of choice in the spreadsheet.
Limitations:
- The data refresh is an truncate-add, all of the existing rows in the sheet refreshed will be removed.
- No update functionality yet, this is a one way pull of data. BUT, my great friend +Matija Svoren has put together a great little framework for updating data from a Gapps spreadsheet, and I hope to document it and get it realeased sometime soon.
There is quite a bit of documentation in the code which is on google code. I hope someone will find this helfull, and even perhaps contribute to completing the concept.
Comments
Post a Comment