Skip to content

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.

Google Apps | moore |

What is the difference between a developer and a programmer?

In my mind, developers have skills that are a superset of the skills of a programmer.

Programmers:

  • understand how software works
  • think about the best way to solve problems they are presented with
  • write good code
  • learn new techniques, languages and manners of creating software
  • write documentation
  • write tests

Developers:

  • do all of the above
  • work with the business to define requirements
  • test beyond basic unit tests, up to and including user acceptance testing
  • understand basic system administration
  • do basic database tuning
  • deploy software and solutions
  • can do some UX design
  • can manipulate graphics
  • support and maintain what they’ve written

In other words, developers can span the gamut of needs–they are jack of all trades.  Not all positions are fit for developers because they can be a mile wide and an inch deep, but in general having someone who can handle the entire software lifecycle is more useful in more situations than having someone who is handed a spec and can crank out code.

(Note that I’ve never worked in a huge company for longer than a few months, so perhaps that is where programmers are more useful.)

Programming Technology and Society | moore |

How do you decide to pull the plug on a project?

Over the past six months, I’ve been involved with two projects that, to put it politely, didn’t go exactly as planned.

Project A was cancelled, after a significant amount of preparatory work had been done, and project B was just released, after a much more significant amount of effort was expended.

What caused one to be cancelled and the other to be continued?

  • Timing: A had a tight schedule, with a deadline that made sense from the business’ perspective.  When it became clear that the deadline would be missed, cancellation was a logical option.  B had goals but no deadline, which made it harder to make a go/no go decision
  • Risk: A required some server rejiggering that would have made it extremely difficult to roll back.  B, on the other hand, would have been a simple software re-release to roll back to previous, somewhat working, software.
  • Business impact: A was a ‘nice to have’ project, whereas B was addressing something that had caused employees and users pain for years.
  • Sunk costs: A had fewer sunk costs than B, which paradoxically made it easier to cancel (paradoxically, because you should not consider sunk costs when looking at an investment).

What is the difference between pushing through to finish a difficult project and polishing a turd that you really should abandon?  It’s a fine balance, and as project B dragged on, I wasn’t sure at times which path we were headed down.  As someone who loves to ship, it’s hard for me to give up on a project that I had a hand in building, but no business writes software in a vacuum, and those business needs can and do serve as valuable checkpoints on the software process.  Because of the huge business value, it made sense to push through on project B.

Sometimes business priorities change, or, as in the case of project A, deadlines impose a different set of priorities (beyond the purely technical).

Regardless, I think that the more I consider any projects I have dragging on through the lenses of timing, risk, return and sunk costs, the easier it will be to make a go/no go decision.

8z Business | moore |

Real estate public records data sources

If you are looking for public records data sources (deeds, sales transactions, assessor records) in an electronic format, you have four options.  Normally this data is kept at the county level, so there haven’t been too many companies that have rolled it up into a nationwide database.  Even for the companies that have, there are timing and data integrity issues that you should be aware of before you commit.

I listed options here because I had to do some digging to find them, and wanted to spare you that.

First, here are some possible solutions that don’t work.

So, what are your four options?

  1. If you are focused on a narrow geographic area, or want to take on a huge project, you can contact each county and see if they have an offering.  I contacted one local county and they wanted $700 for a CD of assessor data.  This has some obvious downsides, but may be enough for your needs.  (I expect some counties won’t offer electronic versions, but that is speculation.)
  2. Core Logic
  3. Data Quick
  4. LPS SiteX

In the latter three cases, you want to call and ask to talk to someone about ‘public record licensing’ if you want to do anything interesting with the data (remix it, aggregate it, display it to your customers, etc).  Public record licensing is not as straighforward as MLS IDX data licensing (and that is saying something!).  It looks like each deal is semi-custom.

You’ll want to speak to a sales representative for full details like pricing, update timing and geographic coverage.  Hope this helps!

Real Estate Technology | moore |

Leanpub: write a ‘lean book’

Ever wanted to write a book?  Me too!

Leanpub lets you write a book, but with a twist.  They’ve built a system where you can write portions of a book, and easily publish to the major ebook formats (PDF, .mobi, epub).  You write the book with Markdown, and can include code samples, images, tables, and sections.  Nothing really new there, though.

The revolutionary idea of Leanpub, and the reason it is ‘lean’, is that you can build your book piece by piece, and sell it whenever you have ‘minimum viable content’.  (You can also include sample content to let people see what they are buying.)  Once someone purchases your book, they receive all further updates.  This type of interaction with users can be very helpful–it spurs you on to finish your book (after all, someone paid for it) and also lets you know if your book idea has traction in the marketplace (did anyone buy the book), and builds an audience for your book slowly over time (going to a publisher with a list of people who’ve already bought your book is a lot stronger than going there with a first draft).

After you finish your book, you can submit it to all the other epublishing vendors (Amazon, B&N, etc) or a print on demand store.  (They also have support for building a book directly out of a blog, if you have written anything that structured.)

I have built 4 books on Leanpub.  Using CakePHP to generate Markdown, I was able to combine some articles about CSA I had written with a directory of relevant farms.  You can see the Denver guide.  I found the process fun, if not lucrative.

This is a great concept.  It really makes you, the author, focus on two areas of publishing that technology can’t help with: writing and marketing.  The best book in the world won’t have any sales if people don’t find it, and the most promoted book won’t sell if you don’t write it.

I did see some minor issues around footnoting, and not everyone will enjoy writing in plain text and ‘compiling’ the book to PDF (a process that takes around 30 seconds each time you do it), but the platform seems to be evolving (one of the founders is pretty active in the support google group).  All in all I think Leanpub is worth a look, especially if you are writing a book where a chapter or two will save people time in their job.

If you want to know more about lean publishing ideas, check out the manifesto.

Books Technology and Society | moore |

List of online real estate solutions

I recently was involved with a extensive survey of online solutions for my employer, a real estate brokerage.  Some were open source, some commercial, some inexpensive and some expensive.  Basically, any full solution that might have worked was added to the list.  Some of these are aimed only at real estate agents (as opposed to a brokerage), but others definitely scale up to brokerages.  (I say full solutions, so you won’t see anything on the list like Onboard Informatics or Altos Research.)

I just wanted to list all of them here for posterity and to help anyone else looking for a real estate solution.

8z Real Estate Web Applications | moore |

Why I’m using a RSS reader (again)

Many moons ago, I moved from a personal handwritten RSS reader to Bloglines.  Then, a few years ago, I stopped using Bloglines (before they were bought).  I had too many feeds on Bloglines and was spending less time on the computer after work.  I also had some big personal happenings taking a lot of time, and was burnt out on learning new technologies.

So, for a couple of years, I read blogs occasionally, but didn’t subscribe to them in a reader.  The ones that really spoke to me were either visited regularly (by memory) or subscribed to via email (and often unsubscribed quickly).

But recently, I have been consuming a lot more content.  I think there are a number of reasons for this, but the biggest is that the household has an iPad. The iPad is crappy for creating content, but is fantastic for consuming it.  I also enjoy the ability to email articles with the tap of the finger.

Last week, I found myself visiting the same seven or eight sites over and over again, to see if there was anything new posted.  After the third go-around, I mentally kicked myself and said, ‘that’s what an RSS reader is for!’.

By happenstance, I saw an article on HackerNews about the NewsBlur founder around the same time, and decided to check NewsBlur out.  I actually appreciate the limited number of blogs available on the free version of NewsBlur–I hope that will help me avoid the blog overflow that occured last time.

Blog Useful Tools | moore |

More details on moving away from Google Maps

Well, our move from Google Maps to MapQuest is pretty much done (the start was mentioned here), so I thought I’d give a more exhaustive overview, including some more insight into our thinking.  For context, we, in this post, refers to 8z Real Estate, a real estate brokerage with a great website that drives a huge chunk of business.  (Of course, errors and opinions expressed here are all mine.)

First, if you are considering switching from Google Maps, definitely think about not leaving!  Depending on your customizations, it can be a real pain.  You can also think about it in terms of spending dollars with Google or spending dollars with your developers.

A good place to start is to make sure you know how many map views you are using.  I found three good ways to do this.

  • estimate using your web analytics tools (page A uses 1 map, and is viewed X times a day, page B uses two maps and a streetview and is viewed Y times a day).  This has the benefit of being free and in your control, and not requiring you to make any code changes.  It has the detriment of being imprecise, especially if you are displaying maps via javascript that might not be tracked by your analytics tool.  I used six months of our highest traffic days to get a feel for maximum map views per day.
  • ask a Google sales rep.  This has the benefit of being very precise–they can tell you how many map views they recorded for you on a given day, and, again, doesn’t require any code changes.  It has a number of downsides, though.  First, you have to get ahold of Google sales (using the ‘contact sales’ button here).  I had to submit this form at least twice before I got a call from them.  Second, you have to interact with a live person.  And third, they only have information for a single day.
  • install the new Google Maps API key and look at the API console.  The upside to this is that reviewing your usage doesn’t have any external dependencies and the console  is very precise in tracking your usage.  The downside is that you are now on Google’s radar, and it may require a bit of work (depending on when you added Google Maps to your site).

We actually ended up using all three of these methods at different times, but found the API console to be the best option long term because it is the most accurate.

Once you’ve found your map view usage, check out the usage limits, which will inform your decision.  Two other considerations before you decide to move off the free Google Maps.

  • If you have used the free Google geocoding service, you should re-geocode all your points, as it is outside the terms of service to display these points anywhere other than on a Google map.
  • It is unclear to me when they will start the clock on the new usage limits (as announced here).  I read the new terms carefully, and you have a grace period of three months (you have to be above the free limit for three months straight) before charges start.  Note however, that once you are over for the grace period, you never can get back into it (100 days of over 25K usage followed by 3 years of under it followed by 1 day over it will still result in charges for that 1 day overage).  Additionally, Google will give a months notice of the usage limits enforcement on the Google Geo Developer blog.  That means that you’ll have at least four months of lead time.  And note that they’ve moved the enforcement deadline at least once.

Ok, you’ve decided that it simply isn’t worth the risk, and that you need to take action to avoid being charged for Google Maps lickety split.  If you haven’t, godspeed.

The options for avoiding being charged are outlined very clearly:

  • Decrease your usage
  • Pay for a license
  • Pay for daily overages

Armed with your daily usage information (don’t forget to factor in traffic increases), you can see which of these options makes the most sense.  Contact Google enterprise sales if you feel a license purchase makes sense.  Be aware that the 25K daily limit works out to over 9M map views a year.  So if you are buying a license just to deal with daily traffic (as opposed to wanting the application to live on an intranet or some other non publicly available location, or a seasonal traffic spike), you’ll want to price out at least a 10M view license.

Once I got to talking to the Google representative, it became clear that the map license was pretty expensive.  The price range for a 30M license ranged from the mid 5 figures to the low 6 figures, per year.  We also looked at daily overages as an option.  Unfortunately this turned out to be hundreds of dollars a day.  Neither of these solutions made sense economically, so we decided to spend developer hours instead of dollars to decrease our Google Maps usage.

There are two ways to decrease Google Map usage, of course.  You can decrease use of maps all around (for example, by not having maps show up unless a user takes some action or removing extra maps), or you can migrate some or all of your current Google Maps to a different provider.

We tried both paths forward.  We removed some extra maps from the site (again, determined by our usage data), but that didn’t reduce usage enough, and also impacted users (as determined by email complaints!).

Therefore, we determined it was better to show the javascript maps every time.  We also had a technical wrinkle.  We use GWT  and are locked into Google Maps v2 (due to dependence on this library).  Google Maps v2 will no longer be supported in 2013.  I don’t believe there is a supported GWT binding for Google Maps v3, although that may get written later (here are two open source, non Google supported bindings that I found: http://code.google.com/p/gwt-google-maps-v3/ and http://code.google.com/p/gwt-maps3/ that may solve the problem for others).  Since v2 was deprecated on May 19, 2010, we knew we’d have to revisit Google Maps in the next year (by May 19, 2013, at worst), so that meant a hybrid solution (part Google Maps, part some other provider) was less appealing than otherwise.

We looked at a few different contenders to replace Google Maps.  Our criteria included:

  • quick to set up
  • accurate display
  • free for our usage leverl
  • close in features to Google Maps, at least the features we used
  • looked good

In the end, I prototyped three solutions that met the first four criteria above, and asked the team for feedback on the fourth.

The solutions included:

Some options that might be worth looking at for some, but didn’t work for us were:

  • Bing Maps, which we ignored due to license limits
  • Google Maps v3.  This version has significantly cheaper overages, so if you are right around the 25K usage, that might be worthwhile, but we were way over 25K mapviews, and also, as mentioned above, had GWT ties to v2.
  • Rolling our own maps (ht briantimoney).  I think this wasn’t the right solution for us two reasons.  One, we were looking for something that was a relatively easy ‘drop in’ replacement for Google Maps, and crunching a bunch of map tiles didn’t seem to fit that requirement.  Two, I’m not sure there’s a lot of value in unique maps for our real estate web application (though there is in having points of interest and accurate mapping of properties).

We ended up choosing the Mapquest Javascript API for the best look and feel.

The next step was to update the pages where maps were used.  There were two main types of pages that used maps.  One type of page showed a single property on a map.  The other showed multiple markers on a map, as results of a search.

The single property page was trivial to replace.  There were a couple of interesting wrinkles.  The first was replacing the default marker icon.  I had to tweak the iconoffset and shadowoffset properties of the icon object for correct display.  Additionally, the new map had to be displayable from GWT.  This required a JSNI method run in a deferred command, in order to make sure that the MapQuest javascript has loaded.

Based on map usage data, I was hoping we could get by just replacing the single property maps, but this didn’t decrease our map usage enough.  On to search results! (If you aren’t interested in GWT, you can skip to the bottom of this post, as the next section is all about GWT gyrations.)

Replacing the search results was slightly more complex.  I reviewed all the current code and categorize our existing GWT/Google Maps based functionality into three groups.

  • functionality that the MapQuest API could exactly replicate (putting a marker on a map)
  • functionality that the MapQuest API had an equivalent for (displaying information on click of a marker)
  • functionality that we had previously built but, in the name of simplification, weren’t going to port to the MapQuest based solution

After this categorization was done, it was fairly simple to port the needed functionality from GWT to javascript (jQuery/MapQuest).

One additional twist is formatting code shared between GWT and java (displaytag decorators, among other code).  The MapQuest javascript needed to be able to call that formatting code, as well as other GWT functionality (for example, creating a lightbox).  No sweat, right?  Here’s an example of calling GWT code from javascript.  The javascript that was calling the GWT code was running in a windows.load jQuery anonymous function.

Well, that worked fine in FF, and often in Chrome, but I was getting an error message from Chrome occasionally and IE all the time.  The Chrome error was pretty clear: ReferenceError: gwtListingUtilgetMLSIDLogoandOfficeName is not defined (gwtListingUtilgetMLSIDLogoandOfficeName is the name of one of the GWT methods that was exported for javascript to use).  The issue was that the map javascript was executing before the GWT code was fully loaded, and not finding methods it was expecting.

The solution was to chain the javascript, so that the map javascript was run via a GWT JSNI call after GWT was fully loaded, and all needed GWT functions were exported.  To provide for other GWT on load chaining, I used the onload pattern outlined here and created an addGWTOnLoadEvent.

Then I needed to create an anonymous function that GWT could call after it was loaded .  So, to recap the path of the map creation code (because it is kind of confusing to me):

  • on page load, addGWTOnLoadEvent(startupSearchMap()); where startupSearchMap returns an anonymous function
  • in GWT code, we export needed GWT functions to javascript: $wnd.gwtListingUtilgetMLSIDLogoandOfficeName = function(mlsId, source Id, baseImageURL, officeName, listingStatus, withBreaks) { return @com.cohomefinder.util.sharedwithgwt.ListingUtil::getMLSIDLogoandOfficeName(Ljava/lang/String;ILjava/lang/String;Ljava/lang/String;Ljava/lang/String;Z)(mlsId, sourceId, baseImageURL, officeName,listingStatus,withBreaks);};
  • at the end of the export JSNI function, we call gwtonload: if ($wnd.gwtonload) { $wnd.gwtonload(); }
  • which executes the anonymous function returned by startupSearchMap which then binds to the DOM element and creates the map.  Needed GWT functions are guaranteed to be present.

This would all be a lot simpler if we didn’t have valuable code written in GWT, but that is a sunk cost that I don’t ever see rewriting.

After that, it was all just testing and making a few UI tweaks.  However, the depth and maturity of the MapQuest API and community scared me a bit, with forums that weren’t as deep as the Google Maps groups I had been accustomed too, and with posts like “IE9 not supported” by the latest MapQuest API.  The API documentation was also underwhelming, with plenty of methods undocumented.  But it all worked out.  If I was doing some aggressive, bounds pushing map functionality, I don’t know if these concerns would have been more of a hindrance.

So, to recap, if you are thinking about moving off of Google Maps:

  • find out your map views per day
  • consider usage solutions that allow you to remain with Google Maps
  • if you must move off of Google Maps, consider all other solution providers and find the one that fit
  • make sure you replicate your current functionality with the map provider you choose

In general, I would rather not have moved off Google Maps (it felt like running just to keep in place, and Google Maps is a great product), but the economics dictated this choice.  I hope this post helps you decide if moving off Google Maps is right for you.

8z Google Maps Javascript | moore |

Why you should blog

This is something I tell everyone I run into (because I think everyone has an interesting story to tell), so I wanted to outline my thoughts once and for all.

If there is any area of your life (professional life, hobby, future career) that you care about and of which you want to improve your understanding, you can do no better than to blog about the topic as regularly as you can.

Now, this isn’t the golden age of blogging, when you could be the only tailor/wine merchant/banker/etc blogging, and blog your way to fame, conferences, and riches.  There are countless blogs out there now (almost 1.3 million, according to technorati).  But you can still differentiate yourself from most by blogging.

I believer there are many many reasons to blog, but the strongest are:

  • you demonstrate tremendous credibility when you blog.  This credibility isn’t earned immediately, but after you invest some time (a year or so), you’ll have credibility when someone searchs for you on the web, or when you tell a prospective employer/customer/client “oh, I encountered that problem, let me send you an article about that” and you do.  A blog works for you even when you are sleeping–I’ve had random people contact me about a blog article, and I’ve had people I know say “I was searching for topic yyy and ran across your blog”.
  • you will learn more about the subject you are writing about than you thought possible.  I never know something so well as when you can explain it to someone else.  In addition, as you write post after post, you’ll find new dimensions to your subject, new people to interview (a great way to build content for your blog and to make connections), new questions to ask, and new organizations or companies relevant to you.
  • it is free, except for your time.  Other than opportunity cost and some thought, a blog doesn’t cost you anything.  This makes it a low risk marketing effort.

One bonus benefit: you own all the content you create!  Unlike what you post on twitter, which is ephemeral, or what you post on facebook, which is hard to export, most blogging software lets you import and export with relative ease.  There are services out there that will help you package your blog as a book.

Here are three objections that you may have to blogging.

  • Aren’t I giving my knowledge away for free? — Well, yes, if you solve the exact same problem that someone else has.  In my experience, it is much more likely that someone will have a similar problem, in which case your post is an advertisement for your services instead.  And if you solve someone’s exact problem and never see a dime for it, that’s good karma.  Now, I wouldn’t encourage someone to blog about a trade secret or an entire idea for which they are trying to get a patent, but they might blog about the process of getting there, or about a similar idea that they investigated but found lacking.
  • I can’t write — This is a two part answer.   One, you should get better at writing, since it is a key part of business today.  And practicing on a blog is one great way to do it.  Two, if you can’t write or don’t feel it is applicable to your skill set (you are, say, a photographer) and I can’t persuade you that it is *still* a good idea to write well, then you can blog using other media.  Video blogging, photo blogging and audio blogging are all options to which all of the good points I’ve made above apply, and none of them require you to write a word (I don’t have experience with those typs of blogging, but a quick search should help you find a platform).
  • I don’t have the time — Ah, finally, a good objection!  A blog does not have quick payback; it can be a year of writing a post every ten days before you get 20 visitors a day.  If you choose to invest in your career in another way (writing open source, if you are a programmer, toastmasters if you are a salesman, volunteering at a daycare if you are interested in childcare, etc, etc) I admire you.  I think these other investments shine in other ways, but they lack the scalability, reach and timelessness of a blog.  So, consider your priorities, and if blogging’s benefits aren’t enough to justify a time investment, I wish you well.

Ok, I have convinced you to blog, right?  Three next steps:

  1. Sign up for an account on blogger or wordpress.com.  Spend 10 minutes picking a look and feel and setting up an about me page, but don’t spend more than that.  It’s very easy to get obsessed with how your blog looks and ignore the content!  (You can always go back in six months and change your look.)
  2. Write your first blog post!  Don’t have it be an ‘about me’ post; jump right into the meat of what you want to explore, be it Tanzanian politics, the economics of ‘How I Met Your Mother’ or space technology.
  3. Decide how much time you want to spend on your blog going forward.  How much time should you devote?  Enough so that your blog is active, which I define as a post a week (your mileage may vary).  More if you have time.  Also, realize that you will have good months and bad months.  I’ve been blogging for over 8 years and have had months where I struggled to get one post up (and sometimes failed–I’m looking at you, November 2011), and others where I posted every other day.  I also suggest writing a bunch of posts when you have the time and/or urge.  These you can then schedule so that when you don’t have time, you can still be putting new content out there.

And a bonus step: find other bloggers out there, using Google or your search engine of choice, and comment on their blogs.  This brings you into a conversation and avoids ‘shouting into the wilderness’ syndrome of a blog that no one but you visits, which can be so disheartening.

Go do it!

Blog | moore |