Skip to content

All posts by moore - 46. page

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.)

APIs Google Apps Java Javascript | moore |

Guest posts about Cordova CLI

I’ve recently had the good fortune to write a couple of guest posts at some blogs to promote my book about the Cordova CLI.

First, I wrote a post on the CloudFour blog: “PhoneGap makes mobile development more accessible”.

But PhoneGap, a four year old project, now lets developers leverage standard web technologies such as CSS, HTML and JavaScript to build mobile applications. Designers who know CSS and HTML can create fantastic mobile friendly user interfaces (leveraging frameworks like Topcoat and Junior) and developers can focus on functionality and performance.

CloudFour is a consultancy based in Portland OR that my company has worked with in the past. They focus on the mobile web, and are very sharp.

Second, I wrote a post about hooks on Devgirl’s weblog: “Three hooks your Cordova/PhoneGap project needs”.

These hooks automate three common scenarios that will take place in almost any Cordova application–adding plugins to an app, switching between deployment environments, and adding custom icons and splash screens.

Write a hook anytime you have a build process that is tedious, repetitive, or required to build your app. Look carefully at any documented manual build processes. If you need to perform steps to build your app (copying icon files to various directories under platforms, for example), see if you can automate them in a hook script to make your build process quicker, easier, and more repeatable.

Devgirl, aka Holly Schinsky, is a developer evangelist for Adobe who has written about a wide variety of topics, including push notifications and Flex.

Cordova CLI Mobile Technology | moore |

Coursera online MOOC: one student’s experience with Stats 101.

I have been taking Statistics 101 from Coursera. This course is taught by a Princeton professor. I have been interested in stats for a while, but have never taken any classes. As a bonus, a work project I’m focused requires a lot of linear regression. (I’m using a library for the linear regression, of course, but wanted to understand some of the limits of linear regression before applying it for a business purpose.)

It was very easy to sign up for the class. I was a bit early, (the lectures are put up weekly starting on a given date), so I just added my email address to the wait list. When the class started, they emailed me and I registered.

It was free.

There are some changes coming to the university world. I have some friends who are professors and I’ve been sharing articles like this one and this one for years.

So, that was an additional reason to take the course. What would an actual online class be like?

The class is divided up into 12 weeks, 1 midterm and 1 final. Each week there are between 4 and 6 lecture videos to watch (the longest was approximately 20 minutes, the shortest is approximately 5 minutes), a lab video and a homework assignment. The lab typically examines the lecture concepts and puts them into practice using R, an open source stats tool/language. The homework is an untimed quiz that I have 100 tries to finish. Each quiz has 10 questions, some text input, some multiple choice, and typically is due 2 weeks after the initial lecture on the topic. I can complete the quiz later for reduced credit.

I’m over halfway through the course.

Am I learning something? yes. Definitely. I’ve learned basic concepts of statistics. There has been some handwaving on some complicated concepts and single letter concepts are occasionally introduced with little explanation (t, Z, and F values, for example), but this is an intro course, so I am unsurprised. I definitely have become comfortable with basics of R.

Am I learning what I would be in a normal college classroom? Nope. There’s been no collaboration (because of my time constraints, I don’t participate in the forums, which are the only form of collaboration I have seen). All R scripts are provided in the labs, which means you sometimes just cut and paste. Questions on quizzes are constrained by the online test format. Because I’m jamming it into my schedule, I don’t review the material as much as I should. There’s no opportunity to stop the instructor and ask exactly what he meant.

But….did I mention it was free? And that I’m not in college and don’t have the time for a normal college class?

If you are thinking of taking an online course through a MOOC like this one, here are some tips.

  • block out time to watch the lecture videos–I spend about 1-2 hours a week doing this. You can double book this with mild exercise (treadmill), sometimes. Sometimes the concepts were complex enough I could not multi task.
  • Coursera has a button on the video player to run the videos faster. I just started using this and find running the lectures at 1.5x is doable.
  • plan to spend some time on the labs and quizzes–I spend about 1-2 hours every week.
  • know what you want. If I wanted a deep understanding of statistics, I would probably need to spend an additional 2-4 hours each week working on understanding all the concepts covered in the lectures, and get an additional statistics book. (This one looks nice.). But I want a conceptual overview that lets me dig into third party libraries and learn the domain jargon enough to search the internet for further resources. This class is letting me do that.
  • get a tablet–these devices are perfect for consuming the lecture videos.
  • be flexible in your viewing, but try not to view a week’s worth of material in one day–that much academic knowledge transfer is no fun.
  • no credit is available. This might be an issue for some students.
  • be committed. It is very easy to sign up for these courses and then drop out, because there really are no consequences. But there are a huge variety of courses from a number of sources: udacity, udemy, edx, Khan Academy.
  • enjoy the free world class instruction.  Did I mention this was free?

All in all, I’m happy with this course and will come out of it more grounded in statistics.

From my experience in this class, I think that the business of teaching, especially introductory material that lends itself to video lectures, is going to undergo a change as radical as what newspapers have been through over the past 20 years. I don’t know if MOOCs will augment or supplant universities, but the scale and cost advantages are going to be hard to beat.

Lifehack Technology and Society Video | moore |

PhoneGap Usage Survey Results

A few weeks ago, I asked the PhoneGap google group members to fill out a simple usage survey. I was interested in versions of Cordova/PhoneGap used, as well as what device platforms were targeted. I had 30 responses over just under two weeks. Note that this survey closed on the 29th, before Cordova 3.1 was released.

Here are the results. (Some questions allowed multiple answers, so the number of responses may exceed 30.)

“What version of Cordova/PhoneGap do you mainly use?” A solid majority was on a modern version, either 2.9 or 3.0.
Which version of cordova do you use?

“What device platforms do you target?” The vast majority of respondents target iOS and, to a slightly lesser extent, Android. There are a smattering of other platforms being targetted.
what-device-platforms-do-you-target

“What versions do you have in production?” Every version of the 2.x line was represented. I think this shows that upgrading PhoneGap/Cordova projects used to be a pain (and, once an app is finished, there often times is no need to revisit).
Which versions do you have in production?

“Do you use Cordova or PhoneGap?” This is interesting to me because I think there is a lot of confusion around the difference. Most people were pretty clear.
do-you-use-cordova-or-phonegap

The results of this survey was interesting to me and I hope to you as well.

Mobile Technology Phonegap | moore |

10 years on

A decade ago, I wrote my first post about RSS, and how I wished someone would aggregate events via RSS. I’m still waiting for this 🙂

I had recently come back from a trip abroad, and was a young contract programmer living as cheaply as I could. One decade on, I am a married father with a house who is a full time employee. Things change, but I still blog.

A decade of blogging has taught me many things. How much I enjoy teaching, how widely the Internet lets you reach, how much people care about Yahoo Mail and dating software, how powerful Google is. But most of all, how writing about something helps you truly understand it.

I’ve been hot and cold on blogging–sometimes posting every couple of days and interacting with the commenters, sometimes ignoring my blog and treating it is a write only medium. Either way, the corpus of 600+ posts (more than one post a week) and the thousands of visits per month the blog gets, are gratifying.

I look back on proudly on all my blog posts. And I can’t tell you how fun it is to hear from someone that I’ve helped them, or to run across a post of mine when I’m doing a google search, or to see from where people have linked to my articles.

Here’s to another decade.

Blog | moore |

Leanpub ebook: Developing Cross Platform Mobile Applications with Cordova CLI

I took the content from my blog posts about Cordova CLI, and am revising and updating it for an ebook about Developing Cross Platform Mobile Applications with Cordova CLI. I discuss some of the intricacies of Cordova CLI in more depth than either in my blog posts or in the Cordova documentation.

If the topic interests you, I’d love to hear your feedback.

Plus this gives me a chance to use Leanpub, which I’ve written about previously.

Books Cordova CLI | moore |

How to collect usage statistics in your phonegap/cordova application

My company recently wrote a couple of mobile applications. Since one is for consumer use (search for ‘8z neighborhood’ in the App Store/Google Play if you live in Colorado or the Bay area and want to check it out), I wanted to know what type of users were downloading and installing it, what was being used, and other general usage statistics.

I asked a mobile app vendor we’ve worked with what they used for usage stats, and they said Flurry. I also looked at Google Analytics, Mobile–this is a nice q&a explaining the major players in the mobile analytics market. We didn’t want anything complicated, just basic stats with the possibility of collecting further information in the future, so I went with the vendor recommendation. Flurry also works with the two platforms we were targeting: IOS and Android, as well as many others.

Flurry is zero cost, but nothing’s free–they want your data and you grant them a “right, for any purpose, to collect, retain, use, and publish in an aggregate manner” all data collected from your application. I’ve seen similar TOS from most of the free analytics vendors, so this was no surprise.

To use Flurry with cordova/phonegap, and with plugman, I was ready to plugmanify an existing Flurry phonegap plugin. Luckily, someone else had already done it. All I had to do was update the plugin to work with Cordova 2.9, and to use the latest IOS7 compatible Flurry library.

After you install the plugin (I recommend doing so in an after_platform_add hook script), you simply add this to your code after the deviceready event fires: window.plugins.flurry.startSession(sessionkey). I inject the session key using a hook script, because I wanted a different key for stage and production builds, and also for each device platform (Flurry requires the latter). Because hooks only get the root directory as context (although this is supposed to change) I had to put some logic in the javascript to call startSession with the appropriate key:

 App.config.flurryid = "";
    if (window.device && window.device.platform) {
        if (window.device.platform == "Android") {
            App.config.flurryid = /*REP*/ 'notreallyanandroidflurryid' /*REP*/ ;
        }
        if (window.device.platform == "iOS") {
            App.config.flurryid = /*REP*/ 'notreallyaniosflurryidxxx' /*REP*/ ;
        }
    }

Although I have not used any of the more specific event tracking, the basic statistics are a great start (including new users, retained users, device versions, etc).

Don’t fly blind when you release your phonegap/cordova mobile app–use Flurry or something simliar.

8z Android Mobile Technology Phonegap Useful Tools | moore |

Slides from my ‘Transforming Data with Kettle’ talk

Here is a PDF of my slides from the ‘Transforming Data with Kettle’ talk I did last night at the Boulder Java Users’ Group.

I really enjoyed some of the discussion, which centered around topics like how to version control output of graphical code generation tools, error handling and transactions, and who had interacted with the EDI data format. As far as my audience survey questions, I’d say around 40% of people had written scripts to munge data from one datasource to another, and about 15% had used java code and about 20% had used an ETL tool.

Thanks to everyone who attended.

BJUG Pentaho Data Integration | moore |