Skip to content

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.

Business Cloud Computing Dynamic Languages Google Apps Useful Tools | moore |

google_debug parameter: Google Ad Manager Help

I’m using Google Ad Manager because OpenX didn’t get back to me quickly enough when I requested a free hosted account.  Sorry, it’s not Google Ad Manager, it’s Double Click for Publishers.  Whoops.

It’s relatively complicated software, probably because serving ads is relatively complicated business.  I have hacked my way through it, but the single most helpful tip I found was to append <code>?google_debug</code> to the page you’re trying to get the ad displaying on.  This pops up a window with what is basically a stack trace of the javascript execution on your page.  You might not understand all of it, but you can definitely cut and paste the message you find into the Google Ad Manager help search, and find more useful answers.

Useful Tools | moore |

Quick Donation Website Setup

This is based off a question I got from a friend–how can non techies easily set up a website to take donations and publicize a cause?

It’s not hard.

Of course, Google Sites isn’t the most flexible publishing platform (see, for instance, this discussion on how to link to an uploaded PDF), but it will get the job done for most simple sites.

Long live the non expert publisher!

Technology and Society Useful Tools | moore |

On My Brief Experience with Wireframe Tools

I was looking for a tool to quickly and easily build wireframes.  Oh, it had to be free too.  Luckily, I found this useful list of wireframing tools.

The first one I picked was Simple Diagrams.  I like Adobe AIR apps, already have the runtime and it looked nice.  Even had some tutorials, even if they were third party generated.

But then, I’m actually using it, and I go to save the image and there’s no save!  You can export as PNG, but you can’t, using the free version, save a project, shut down your computer, turn it on again, and then open up your project to where you were.  Now, I’m no opponent of freemium business models, but if you can’t save a diagram and come back to it, the program is really crippled.  Call it a demo, but it’s not anywhere near a free functioning version.

I then tried Web Site Wireframe, but wanted something that would work offline.

Finally, I tried Pencil Prject.  It’s not the most polished (one of the tabs in the main screen is “My Stuffs”) but it worked–I could edit files offline, and save projects and re-open them later. It’s also kinda cool because it is built on top of the XUL and Firefox application frameworks (they have a plugin and a standalone app available).

Any wireframe tools you would recommend?

RIA Useful Tools | moore |

Parsing street addresses in a java application

I recently had to find a way to parse a street address into its component parts, and thought I’d share my adventure.

The idea is to take a string like “123 S Main Street” and break it apart into the street number (123), the street direction (S), the street name (Main) and the street type (Street).

At first, I thought that regular expressions would work, but the sheer variety of legal postal street addresses quickly dissuaded me, as did my boss’s misgivings.

Stackoverflow has a nice discussion of the problem, which gave me some additional pointers.  There’s a commercial solution, which is available as a COM component or a web service–I didn’t try this.  There is a free, but application/attribution required, web service provided by a university that did a great job (thanks, California tax payers).  This solution is also available in a for-pay variant.

Neither of these were desirable because we needed to parse a lot of addresses quickly, and calling out over the web can be slow.  Some more digging turned up this stack question and  JGeocoder, which has a fairly robust address parser.  It’s not perfect, but it was free and open source.  I am not sure if it is still in development (the author didn’t respond to my email) but it does what we need it to do.

As an added bonus, we’re using pentaho for the data processing, and you can call java classes directly from your data processing steps, so I didn’t even have to wrap the java call in a shell script or anything.

Java Useful Tools | moore |

Own your social media–install Storytlr

I guess I’m just not very trusting, because I like to have copies of my data.  I host my own blog, rather than use blogger or wordpress.com.  I host my own email (or at least one of my two main accounts).  I prefer to document interesting things on my blog, rather than a site like Quora or Stack Overflow (though I do have an account on the latter).  Heck, even though I use an open ID provider, my own domain is the master, and I just delegate to myopenid.com.

So, since I recently have been putting a bit more effort into my social media presence (you can find me on twitter here), I looked around to find a backup solution.  I did find one–Storytlr–via this article on backing up your twitter feed.  It apparently used to be a hosted service, but now is open source–code here, install instructions here.  (There’s at least one for pay service too, but then, you don’t really own your data, plus I’m cheap.)

It was pretty trivial to install.  I ran into this issue with Storytlr not recognizing that PDO was installed, but the fix (hacking the install script) worked, and I didn’t run into the Zend error also in that bug post.

I also ran into an issue where I chose an admin password of less than six characters on install.  Storytlr was happy to let me do that, but then wouldn’t let me enter the exact same password when I was logging in for the first time.  To fix this, I had to update the password column in the users table with a new MD5 string, created using this tool.

So, what does Storytlr actually give me?

  • Access to my data: I set up feeds to be polled regularly (requires access to cron) and can export them to CSV whenever I want.  And I keep them as long as I want to.
  • One single point of view of all my social content.
  • Really easy way to add more feeds if I join a new social network.  Here are the sites/networks Storytlr supports right now.

The issues I ran into are:

  • Technical issues, resolved as documented above.
  • No support for facebook.  (Well, there’s this experimental support, announced here, but nothing that is part of the project.)  This is big, given how bad Facebook is with respect to privacy.  I am not sure what my next steps are here.
  • Not wanting others to have access to my lifestream.  This was easily fixed with a Auth directive.

If you are depending on social media sites, have some technical chops, a server to host it on, and want to ensure a historical archive, you should look at Storytlr.

Blog Social Media Useful Tools Web Applications | moore |

Writing a munin plugin

Recently, we’ve run into some stability issues with our main web application.  It’s a small company, so even though I’m definitely not the ops guy, everyone is pitching in with ideas and suggestions.  One thing that the ops guy did install that has been super useful is munin.  This graphing software lets you monitor, over time, many different aspects of your web application and/or servers.  It has been invaluable in letting us know what the effects of the various changes we’ve made have been.

Questions that munin helps you answer can pretty impressive.  For example, does doubling the amount of memory available to your webapp container help stability?  How can you know unless you’re measuring stability?  What happens if you prohibit certain bots from visiting your website?  When during the day or week is your server running hottest?

We have a watchdog that monitors our main application server, and if it is not responsive, restarts it.  The watchdog also records when the restart occurred.  I decided, as a fun project, to write a plugin for munin that would graph the number of restarts per day, as a high level ‘are we more stable yet’ graph.

Writing a plugin was trivial–it’s a shell script that follows certain output formatting. All I really needed was this HOWTO and this explanation of the types of data sources, though the FAQ is, as per usual, worth a scan.

Munin is by no means perfect (my dream feature would be the ability to annotate graphs at a certain moment in time; ‘this is when we released version 2.1’), but it is a huge hammer in the IT toolbox for understanding current and historic behavior of your application.

Useful Tools Web Applications | moore |

Stats mix is out of beta

Statsmix, a cool dashboard builder which I previously reviewed,  is out of beta, according to an email I just received, and an announcement on their blog.

They have have added info about pricing on their site.

I took a brief look at the new release and it seems to address the pricing issue, but not the integration issue.  Still, if I were running a business and needed some custom reporting measured over time, StatsMix would merit a serious evaluation.

Business Useful Tools | moore |

Useful Tools: StatsMix makes it easy to build a dashboard

I haven’t been to a BDNT lately, but still get their email announcements.  In August, all the 2010 TechStars folks presented, and were listed in the email.  I took a look at each company, and signed up when the company seemed to be doing something cool.  I always want to capture my preferred username, mooreds!

One that was very interesting to me was StatsMix; I signed up for their beta.  On Nov 1, I got invited to sign up.  Wahoo!

Statsmix lets users build custom dashboards.  I am developing an interest in web analytics (aside: if you are interested in this topic, I highly recommended Web Analytics 2.0, by Avinahsh Kaushik).  I’ve been playing with Piwik, an open source analytics toolkit, but Statsmix offers a slicker solution.

They have made it dead simple to create a custom dashboard for users.  They offer integration with, at this time, 29 services (twitter, mailchimp, youtube, Google Analytics, etc).  I could not find an up to date list of integration services outside of their webapplication!  The best I could find was this list from September.  While the integration interface is slick, the data integration is rudimentary.  For example, they will let you monitor the number of rows in a Google Spreadsheet, but nothing more (like rows in different columns, or the value in a particular cell–would be nice to see them integrate with Google Apps Scripting); you can track the number of likes on Facebook, but not the number of comments.

The real power of StatsMix comes from the ease of integration with your own custom stats.  They offer an API which is accessible via REST.  This means that you can push information from your database to a beautiful looking dashboard with shell scripts and a cron job.  Very cool!  It would be nice to see a plugin for Magento or other ecommerce vendors; I recently had a client, The Game Frame, that would have been a great fit for this type of dashboard, since it aggregates beyond what the ecommerce software provides.

Other cool features:

  • The whole UI is beautiful and farily intuitive.
  • The dashboard supports custom date ranges.
  • They will send you an email of stats every day, and apparently have some kind of limited version you can pass onto clients.  I didn’t play with the email feature at all, though it is extremely useful.

However, all is not perfect.  Some issues with StatsMix include:

  • As mentioned above, the integration with third party services leaves something to be desired.  What they offer is a nice start, but it’d be great to see them create some kind of marketplace where developers could build solutions.  For example, the twitter widget only tracks the number of followers.  From the TWitter API, it appears to be pretty easy to track the number of mentions, which could be a useful metric.
  • It wasn’t clear how to share a dashboard, though that may be an upcoming feature.
  • The terms of use are, as always, pretty punishing.
  • Once you develop a number of custom metrics, you are tied to their platform.  That wouldn’t be so bad, except…
  • They are planning to charge for the service, but give no insight into what to expect.  There is a tab called ‘Billing’ but all it says is: “During our beta, StatsMix is free to use. After the beta, you’ll be able to manage your billing preferences on this page.”  If I was considering using this as part of my business, I would want much more insight into possible costs before I committed much time to custom metric buildouts.  I’m fine with them making money, just want more insight into this key aspect of their web app.

All in all, it is well worth a try.  If you to, let me know by posting a comment.  I have 5 invites to give out.

Cloud Computing Programming Social Media Useful Tools Web Applications | moore |

Update on GWO for a non profit

Well, after a week or two of data collection, the GWO experiment I had set up for the WILD Foundation caused an issue–apparently it was preventing a javascript shadowbox from working. I didn’t want to get into troubleshooting a javascript component that I had never used on pages I had never seen, so I recommended turning off the experiment to see if that solved the issue.

It did. I knew there was a way to include the GWO javascript just where it was used, on the home page, but I got busy, and by the time I was able to do this, they were hip deep in a website rework. I’ve been involved in website redesigns with too many cooks in the kitchen, so I just asked them to let me know when the dust has settled so I can restart the experiment.

Lessons learned:

  • sometimes volunteer projects take a long time to get to results, and almost definitely will take longer than you plan
  • other opportunities can spring out of volunteering–I showed Emily some space available via another non profit I’m involved in, and she’s looking at possibilities for a fundraiser there.  This never would have happened if I hadn’t volunteered previously
  • the process opened up some ideas for the non profit around changing the home page to highlight things they wanted to focus–sometimes, any perspective from outside an organization is useful

    I’ll let you know when I get a chance to re-enable GWO on the WILD site, but I thought I’d give an update.

    New Tech Meetup Useful Tools | moore |