Skip to content

Easily visualizing location data with Google Fusion Tables

Hands with map on itSometimes you have a list of locations in a Google spreadsheet and want to visualize where the locations using a map. Google Fusion tables lets you do just that, for free, with no technical expertise needed.

To do so, you need to create a spreadsheet. I created a spreadsheet of birthplaces of the Presidents of the USA. Here’s the spreadsheet. You want to make sure you have column headers and that your location information is all in one column. You can see that I concatenated birth city and state into one column, because you can only map one column (unless you have lat/lng, in which case you can use those two columns). You also can’t concatenate any columns when the data has been pulled into fusion tables.

Then, create a new google fusion table (under the ‘more’ menu). Choose a spreadsheet as your data source and then past in the spreadsheet link.

Your location column may or may not have been given a data type of location. If not, use the ‘edit’ menu then ‘change columns’ to convert it to a location type.

Add a map using the red plus sign and select your column as the location. Wait for your column to be geocoded (if you have lat/lng you shouldn’t need to do this).

And there you are. Here’s the map I generated. You can see that if you click on the marker you will get information about each president, which is a nice bonus feature. You can also share this within your organization or with the wider world and do additional filtering.

Fusion tables is great when you have the structured data and just need a simple map representation.

Caveats:

  • Once your data is in fusion tables, you are extremely limited on what you can do with it (see the concatenation above, for example). Do whatever data massaging you need in the spreadsheet. This also means that you probably want the spreadsheet to be your source of record.
  • There’s no way to update your data. So when the next president enters office, I will either have to create a whole new fusion table or delete all the rows and re-import.
  • Fusion tables seems to no longer be under active development. At least I haven’t seen many feature changes over the past couple of years. It is out of beta. I think it’s fine to build adhoc tooling on top of this service, but if I were looking for the core of my business I’d avoid this.

The limits of Google forms

paperwork photo
Photo by Harald Groven

I spent a fair amount of time in the spring working on Google forms. If you have Google apps for your domain and are doing any kind of data entry at all, you should use Google forms. With this, you can create a web and mobile friendly data entry form easily, with validation, for free.

And by you, I mean ‘you, the non technical user’–a big win.  I’m a big big fan of anything that removes developers from the loop because they are too often the bottleneck.  Of course, as you’ll see below, Google forms is not entirely the land of milk and honey.

Any data entered typically goes into a Google spreadsheet for easy batch processing. You can limit use of the form to your Google apps domain, too.

Here are some ways that I’ve seen Google forms used to eliminate manual re-keying of data:

  • Event RSVPs
  • Contact forms
  • Order forms for collateral
  • Transaction reporting
  • Timesheet or vacation tracking

Google forms are extremely flexible, but do have some significant limitations (which is why other form building companies like Wufoo haven’t had all the air sucked out of them).

Some limitations and issues are:

  • If you post to a Google spreadsheet, the typical use case, you are bound by spreadsheet limits: 400k rows, 256 columns.
  • You can’t host the form on your domain. The best solution to this issue I found was to do a URL redirect of a domain name. For example, from someform.example.com to the Google form URL. This lets you replace the form while still sending people to the same URL.
  • You can’t have a form email an arbitrary address on submission without custom code.
  • Multi page forms are possible, but clunky.
  • Validation is limited, though using regular expressions gives you a fair bit of power (but then takes form creation/maintenance out of the realm of the non developer).
  • UI customization is limited. A Google form will always look like a Google form (short of serious server side gymnastics). It will always have the ‘powered by Google forms’ link, the same crappy ‘response received’ page, and the same horrible handling of closed forms (an unmodifiable message from Google, with no way to customize it).
  • Option lists are static (though if you use formRanger, you can alleviate this issue).
  • File upload is not supported. Seriously. Even though Google wants you to use Google Drive.

As alluded to above, you can use Google Apps Script to alleviate some of the issues with Google forms. However, doing so pushes the maintenance of that form into developer land (or at least ‘power user’ land).

Even with all the warts, Google forms is a powerful tool. And did I mention it is free? If you or anyone in your business is currently doing manual data re-entry, and the limits above haven’t scared you away, I’d take a long hard serious look at Google forms.

Google Spreadsheet Custom Functions With Spreadsheet Based Configuration

The business for which I work, 8z Real Estate, runs on Google spreadsheets–they are everywhere, and are especially powerful when combined with Google Forms. (Except in the tech department–we use more specialized tools like wikis and bug trackers.)

Recently I was cleaning up one of these spreadsheets and making it more efficient. This spreadsheet was an ongoing list of items that should be charged to various real estate agents. There was a fairly clear set of rules (person A should be charged this for item B, but not for item C, and person D should not be charged for items E, but should for item F). The rules were also fairly constant–not a lot of change. While they were clear, there were some intricacies that had tripped up some folks. And since there was real money involved, there was often a lot of time expended to make sure the charges were correct.

For all these reasons, it made sense to automate the charge calculations. Given the data was already in a Google spreadsheet, I decided a custom function was the best way to do this. The custom function could read from a configuration tab in the same spreadsheet with a list of people and items which would represent the charge rules. In this way, if there was a new item, a new row could be added to the configuration spreadsheet without requiring any help from a developer.

I was able to write the functions fairly quickly, using QUnit for Google Apps Script. I can’t recommend using QUnit highly enough–developing in Google Apps Script combines the joys of javascript (with its … intrinsic difficulties) and a remote execution environment that can be tough to debug. So, unit test your Apps Script code!

The initial implementation pulled data from the configuration tab with each custom function call. This naive implementation worked fine up to a couple of hundred rows. However, eventually this caused Exceeded maximum execution time errors. I believe this is because when the spreadsheet was calculating all the charge values, it was accessing the configuration spreadsheet range hundreds of times a second.

My next step was to try to cache the configuration data. I used stringified JSON stored in the cache service. Unfortunately, this caused a different issue: Service invoked too many times in a short time: cacheService rateMax. Try Utilities.sleep(1000) between calls.

Third time is the charm: have the function return multiple values. The configuration data is only read once, as is the list of items and names. These are iterated and the charges for all of them are calculated and returned in a double array to fill entire columns. Using this technique avoided the above issues, but created another one. Adding new rows with new items wouldn’t update the charges columns. Apparently there is additional, ill documented caching of custom function values. Luckily StackOverflow had an explanation: spreadsheets “evaluate your [custom] functions only when a parameter changes.” The way I worked around this was to pass a parameter to the custom function of the number of non blank rows of the spreadsheet: =calculateCosts(counta(A:A)). When a new row is added, the custom function is re-evaluated.

In the end I have written unit tested code that works in the way the business wants to work (in Google Spreadsheets), that runs on someone else’s infrastructure (Google’s), that can be configured by non technical employees and that will increase the accuracy of the charge calculations. Wahoo!

Multi threaded push to google drive with Java Executors

I’ve never really written threaded code in java. Sure, I took a java certification class long ago, and reviewed threading and probably wrote some toy code.

But it had always been one of the areas I was aware of but avoided. And it was easy to avoid–after all, I was often writing for a servlet container, and in that case you are supposed to rely on the platform (Tomcat, etc) to provide multi threading magic–you just have to stay in your servlet box. Other times, I was writing glue code where performance wasn’t an issue.

Recently, I was writing integration code that read from google spreadsheets (using the api) and quickbooks online (using the api) and did some calculations on the gathered data and created PDFs (using jfreechart and jasper reports). These PDFs then get pushed to certain folders on google drive.

I had run through some testing in my development and stage environments, and was ready to do a run in production. I started off the run and, after a few minutes, noticed that performance was not very good. About one report every 80 seconds or so.

Now, I’d run this process before I added google drive integration, and it had completed in a normal amount of time. So I suspected the google drive calls were the issue. When pushing the PDFs to google drive in my dev environment, I’d run into some connectivity issues, so as recommended I’d added a exponential backoff to my requests.

This meant that a lot of clock time was spent just waiting for the google services to recover.

So, an easily parallelizable task that spent a lot of time waiting? Seemed like a great place for multiple threads. I googled multithreading in java and found the Executor framework, which made running such tasks in parallel trivial. It even had futures and everything, and had been a standard part of the library since java 1.5.

I set up a thread pool of 15 threads, spent an hour or so rewriting and testing my file push code to fit the framework, and performance is much better. The push to google drive still takes about 20 seconds per report, but that’s a 4 fold increase in speed.

Next steps–use the spring abstraction layer to make the code more maintainable and clear.

Google Spreadsheets as REST API sources

I recently built out a read only JSON API with a Google spreadsheet as the back end data source.

Why do such a thing? I didn’t need to modify the back end, but wanted to make the data available to other software. The people who maintain this data are very comfortable using Google spreadsheets. While I could have written a custom CRUD app, this didn’t seem like a good use of time when Google spreadsheets had served us well in the past.

How did I do this? I first of all created a sanitized spreadsheet, using importrange and regexreplace. This level of indirection assures me that if the source data changes, I can adjust fairly easily. If the user managing the spreadsheet wants to rearrange columns, I can adjust my sanitized spreadsheet easily.

Then I created a Google apps script and used doGet to respond to requests and the spreadsheet service to retrieve the data. The content service lets you serve JSON with the appropriate mime type. I used qunit for Google apps script (invaluable when you are working in the loosely typed javascript world and relying on cloud resources). I also worked with the parameters to build the querying needed for our application.

Then, in order to make it look like a normal API call, I fronted the script with Varnish and did some regsub magic in my VCL file (as well as some light authentication).

This approach has the benefit of keeping everything in Google’s cloud, and allowing you to access the spreadsheet data easily.

This approach has significant limitations, however.

  • Google apps scripts calls are slow, especially when accessing spreadsheet data. Using the cache service can help.
  • You cannot return anything other than a 200 response code. None of the other response codes are available.
  • The actual content is served after a redirect, so caching it at the Varnish level is difficult (though possible), and clients must be able to follow redirects.
  • Google changes the ip of the server running the script. This is not such a big problem, unless your version of Varnish only takes IP addresses in the VCL file, not hostnames. Like ours.

Was this a good idea? Well, it let me build out the API relatively quickly without affecting the users managing the data or finding any other place to put it. But we’ll probably move away from this due to the limitations listed above. One we’ve found particularly painful is the IP address switching, which usually only shows up in our automated testing.

We’ll probably start pushing the data daily (it doesn’t change all that often) to a local JDBC database using the JDBC service and use either RestSQL or DropWizard to generate an API for it. (RestSQL is quicker, but DropWizard lets us maintain format compatibility.)

The trigger creator sends emails when a google form is submitted and email is sent

Google Forms are a great way to collect data.  You can have the form do any number of things using Google Apps Script when the form is submitted, including sending email.

If you want the email to come from a certain person, you can’t manipulate that in the script you write.  (There’s nothing in the mail API doc about a ‘from’ field.)  You can modify the reply to header, but if you want an email to come from one person, you need to have that person create the on form submit trigger, because they are then the active user.

Google Analytics Goals and Forms on Google Sites

I recently put together a website for my father’s new book about World War II, and choosing Google Sites was a slam dunk.  I choose a neutral theme, set up a few pages, uploaded some images and logged into Go Daddy (where my father had purchased his domain name) and set up DNS.  Easy peasy.

Two issues came up.  One minor and one major.

First, there is no easy way to point the bare domain at google sites using Go Daddy’s tools.  So seesaw1942.com can’t be redirected to www.seesaw1942.com.  Minor bummer, but just make sure all the marketing contains www.seesaw1942.com

I set up a form to capture email addresses for people who wanted to be informed when the book was actually published (it is now!).  I was interested in playing around with PPC to drive people to the website, but wasn’t able to set up a goal in Google Analytics to measure signup success.  (So I didn’t end up setting up PPC.)

I looked around and didn’t find anything that would help with this.  Here’s an article about goals and Google Forms, but the writer’s form lives on their own server, which gives them more latitude than someone working with Google Sites.

Anyone have any idea how to do this?

Iterating over a range in Google Spreadsheets

Using the macro facility in Google Spreadsheets can be quite powerful, but I’ve found the documentation difficult to navigate.

For future reference, here is how to iterate over a range of cells that has been predefined (using a named range or in some other manner).

var rangeName = 'mailingdate';
var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(mailingDateRangeName);
var arr = mailingDateRange.getValues();
for (var i = 0; i < arr.length; i++) {
var rowVal = arr[i][0];
if (rowVal.length < 2) { // 2 because of spaces
continue;
}
//... process non empty rows.
}

Note that this only processes a range with one column (hence the arr[i][0]. Ranges with multiple columns are left as an exercise for the reader.

Using Google Apps to generate a simple site from a form

I was blue skying with a friend the other day and came up with checklist to build a simple canned website from a form submission, using Google Apps and Google Apps Scripting.

  1. User goes to a form.  (More on creating a form)
  2. User submits images, video links, text, email, background color, etc.
  3. That all goes into a google spreadsheet, which triggers the building of a site.  Here’s trigger info.
  4. You can automatically build a google site.  Here’s the api: site creation, page creation.  If you want your own domain name (example.com) I think you can do that, but you’d need to integrate with a DNS service to set that up–this part bears a little more investigating.
  5. If desired, you can generate the QR code for the site.
  6. If you need to tell the form submitter that the site is created, you can email them a link to the QR code so they can print it out.
  7. You can even remove content from the site via cron (see triggers link up above), though you can’t automatically delete the site.
  8. It’s conceivable you could even use google checkout to charge users beforehand (though this is a bit less supported).

What’s great is that you don’t need to have any tool other than a browser to build this entire stack.  Google is truly creating a toolkit/macro language in the cloud.

Running a Google Apps Script Once a Month

I needed a way to email a Google spreadsheet to my boss once a month, for some reporting purposes.  I could have put an entry in my calendar reminding me to do it, but I thought it would be a great time to try out the Google Docs scripting that I had read about for a year or two, and seen an AppSumo video about.  (I got the AppSumo video for free, from an ad on HARO.)

It was laughably easy to get write the actual script (here’s a great set of tutorials).  The only rub was Google doesn’t allow you to run scripts in month intervals, only hourly, daily or weekly.  A small bit of scripting got around that.

Here’s the final script (edited to remove sensitive data):

function myFunction() {
  var dayOfMonth = Utilities.formatDate(new Date(), "GMT", "dd");
  if (dayOfMonth == 05){
    MailApp.sendEmail("email@example.com", "Spreadsheet Report Subject", 
'https://spreadsheets.google.com/a/mydomain.com/ccc?key='+SpreadsheetApp.getActiveSpreadsheet().getId());
  }
}

I set up a daily trigger for this script and installed it within the spreadsheet I needed to send.

I really really like Google Apps Script.  I think it has the power to be the VB of the web, in the way that VB made it easy to automate MS Office, reduce drudgery, and allow non developers to build business solutions.  It also ties together some really powerful tools–check out all the APIs you can access.

Once you let non developers develop, which is what Google Apps Script does, you do run into some maintenance issues (versioning, sharing the code, testing), but the same is true with Excel Macros, and solving those issues is for greater minds than mine.