Using Google Apps Script with Kynetx

I posted a few weeks ago about aggregating stats for TomatoFlix or any other Kynetx app. I set up an endpoint on ktest.heroku.com to let me see those stats.

But that only gives me aggregate data. What if I want to observe trends over time? For example, is the popularity of the Redbox website changing? What is the most frequently used movie website during the weekend? On week nights?

In order to get that kind of information, I need a way to retrieve and store my stats regularly. It turns out that Google Apps Script can do just that. In this post, I’ll show you how to combine a time-based trigger that runs an Apps Script function. Then I’ll show you how to make a RESTful endpoint to expose the stats from your app. Finally, I’ll mention how to combine the two to get the data we want.

Starting with Apps Script First, go create a new spreadsheet in Google Docs. Then, create a new script (click on Tools -> Script Editor -> Create a new script). That takes you to the Script Editor. Name the function whatever you want and have it do something simple. Here’s an example that increments cell A1’s value every time is is called:

Now set up a trigger to run that function every minute. From within the Script Editor, go to Triggers -> Current Script’s Triggers. Then click on “Click here to add one now.” That will give you a screen like this:

Have it run your function every minute. Now leave the spreadsheet and come back in a few minutes to see that it worked.

Setting up a RESTful endpoint to expose data I wrote about using Kynetx to create RESTful web services a couple months ago. We’re going to use the same principles here, so go read that post if you haven’t already.

In the case of TomatoFlix, all the stats are tracked using app variables. Implementing this web service endpoint is then a simple matter of writing a rule that responds to a webhook event, creating a JSON object with the data, and returning it. For this example, I’m using Sam Curren’s webhook module to make things easier. That means I need to add one line to the meta block:

use module a8x157 alias webhook

Here’s the rule that does all the hard work:

That produces a JSON document that lives at this URL: http://webhooks.kynetxapps.net/h/a163x53.dev/stats.

Tying it all together Now we’ve got the pieces we need. Go back to the Google Docs spreadsheet you created. Add another sheet. Set up a header row that has one column for the date/time and one column for each of your data points. For TomatoFlix, it looks like this:

Now create a new script (or a new function in your existing script). This one is going to be more complicated than the previous one, but not by too much. Basically we’re going to be appending rows instead of changing the same cell every time. Additionally, the data we put into those cells will come from a UrlFetchApp.fetch() call to the webhook URL of the rule we just wrote. Here’s what it looks like for TomatoFlix:

The last piece is to set up a trigger to call that function. I’m having mine run every day at 3 AM.

That’s it! Now your stats will be nicely aggregated into a spreadsheet, and you can crunch numbers to your heart’s content.

Conclusion I’ve never used Google Apps Script before today, but I’m very impressed with the power it gives you. I’m glad to see that they have native event handlers and triggers, which is what made this all possible.

Triggers also make it easy to raise KNS events on a schedule, something that has been on the developer wish list for a little while now.

What cool ideas do you have for using Google Apps Script with Kynetx?