Use Heroku Dataclips in Google Sheets with Faster Refreshes

Dataclips from Heroku is a great feature that enables you to expose the result of a query in various formats over the Internet.

I like to use Dataclips in combination with Google Sheets to expose data in a format that is friendly to business users. Unfortunately, Google caches the results of the =IMPORTDATA(URL) function call for an hour for a given URL. This means that the data will be stale if it updates frequently which can make the integration much less valuable.

But, there's a workaround to the problem. We just need to modify the URL.

Here's an example. I made a simple application called lucky-minute that selects a winning set of six numbers every minute. I added a Dataclip for the application with a simple query that returns the winning numbers, most recent at the top.

The Dataclip is available at

I then created this Google Sheet that imports the data from the Dataclip using the =IMPORTDATA(URL) function.

To force the spreadsheet to redownload the data before the cache expires, we just need to change the end of URL. I added a cell that is periodically updated with a new value (getTime() of the current time in a trigger). Then we append that cache-busting value to the URL before adding a .csv extension.

Any time the AS_OF value in the sheet changes, the data will be re-imported. Now we can use a Google Sheets spreadsheet when we require less than 1 hour of staleness.

Stare at the example for a minute and you'll see the winning numbers update.

Gifts of Blank Middle Names

Dear Violet and Ivy,

You'll be born any day now. Or any hour.

Our first gifts are your blank middle names. We hope you love them as much as we loved the process of choosing.

Without middle names during your childhood, you may occasionally feel like you're missing out. But give it until 25.

By then you'll learn that when you plant mature landscaping you leave no room for life to grow. And at that point you can pick the middle names that suit you best.

Between now and then, every time a parent at the playground turns their head in horror when I holler "VIOLET BLANK DEVINE" or "IVY BLANK DEVINE", you'll learn a bit more about what it means to be funny.

Love you lots,

Sean Michael Devine

Add Explicit Warnings To F&*%ing Slack Messages

I don't like to curse very much, but sometimes I slip in Slack.

I feel bad every time that it happens, so I've trained Slack to nudge me towards cleaner communication.

Here's how to add parental advisories to your Slack team:

Step 1: Add an :explicitwarning: emoji.

Follow these instructions to add custom emoji to your Slack channel.

There are tons of explicit warning images to borrow on Google Images. The resized image that I used for my warning above is included below.

Step 2: Customize your team with Slackbot Responses

Follow these instructions to customize your team with "Slackbot Responses". Slack will scan messages for the curse words that you provide and respond with the emoji that you just created. Your screen will look something like this.

In case you'd like to copy that list of curse words:


Or, you may want an expanded list of words if your team is super terrible.

There is no Step 3.

Unexpected Behavior of #none in ActiveRecord Query Conditions

ActiveRecord provides a none method that "returns a chainable relation with no records". It's useful when you need a scope that would return zero results.

def pretty_three_point_shots
  return Shot.none if name == "Derek Rose"
  shots.where(points: 3)

Now, let's compose a query using this relation and another relation.

def pretty_shots
    "id in (?) OR id in (?)",,

If the pretty_three_point_shots returns Shot.none, you'll receive an error.

PG::SyntaxError: ERROR:  syntax error at or near ")"
  LINE 1: ... OR id in ())

The reason for the error is that Shot.none does not create SQL that can be used in a subquery.

> Shot.none.to_sql
=> ""

If we change the pretty_three_point_shots method to use an alternative to Shot.none, everything will behave as expected.

def pretty_three_point_shots
  return Shot.where("1=0") if name == "Derek Rose"
  shots.where(points: 3)

Is this the expected behavior? According to the documentation, it might be. But, it seems to me to defeat some of the purpose of the none method.

Anyhow, if you need a subquery-friendly none alternative, this approach works fine.

Understanding Ember Data and RSVP Promise Chaining

With Ember Data, most relationships are asynchronous - promises that eventually resolve. You can treat promises in views as if they're the object that the promise will eventually resolved with. However, dealing with promises outside of views isn't so simple because you have to make those components, models, or whatever else "promise-aware".

Here is an example that's extracted from a real application that shows how to make a component promise-aware. The component allows us to define an area of a view that will only be visible to users that can administer an organization.

This is the component being used in a template:

{{#if-can-admin user=currentUser organization=customer}}
  You're an admin!

The handlebars template for the component simply yields if the canAdmin property is true.

<!-- app/templates/components/if-can-admin.hbs -->
{{#if canAdmin}}

The logic for the component is defined in the JavaScript file, and this is where we'll need to deal with the promises. I've simplified the real code a little bit to put more focus on the point, and I've added comments inline to explain how it works.

import Ember from 'ember';

export default Ember.Component.extend({
  user: null, // will be a promise
  organization: null, // will be a promise
  canAdmin: false,
  tagName: 'span',

  setCanAdmin: function() {
    var self = this;
    var canAdmin = true;

    // hash of promise chains that must be resolved before
    // we can calculate if the user can administer the organization
    var promises = {
      // first, we get the user (which is a promise)
      members: self.get('user').then(function(user) {
        // then, when the user resolves we return the members
        // property of the user which is also a promise
        return user.get('members');
      }).then(function(members) {
        // then, when the members resolve we collect the organization
        // property of each member (which are promises) and then
        // wait until they have all resolved
        var organizations = members.getEach('organization');
        return Ember.RSVP.all(organizations).then(function() {
          // return the members so that we can continue chaining
          return members;
      }).then(function(members) {
        // then, we use the same technique to resolve the role property
        // of each member (which is also a promise)
        var roles = members.getEach('role');
        return Ember.RSVP.all(roles).then(function() {
          // return the members so that the value of the promises
          // object matches the key
          return members;
    // the hash function will wait until the hash of promises
    // resolves before passing the hash of results to the callback
    Ember.RSVP.hash(promises).then(function(results) {
      // simple function to determine if the user role is an admin
      var isAdmin = function(member) {
        return member.get('') === 'admin';
      // simple function to determine if organizations are the same
      var isSame = function(first, second) {
        var isSameId = first.get('id') === second.get('id');
        var isSameType = first.get('modelTypeKey') ===
        return isSameId && isSameType;
      // user can admin if it has an admin membership
      // for the organization
      canAdmin = results.members.any(function(member) {
        return isAdmin(member) && isSame(
          member.get('organization'), self.get('organization')
      // set the canAdmin property on the component
      // which is what the handlebars template uses
      self.set('canAdmin', canAdmin);
  // execute the function when the component is initialized
  // you should also call observes(...) if you want it to respond
  // to changes in the models

.This component has been simplified and doesn't handle cases where the user or organization aren't promises, and doesn't handle if there are errors while resolving the promise chain. But, it should show pretty clearly how you can deal with a hierarchy of Ember Data relationships that must resolve before you can calculate a property.

Complete! - The 2014 Rails Rumble Podcast Series

Back in August, I contacted Tom Mango, one of the organizers of the Rails Rumble, to see if they'd be interested in collaborating on a series of podcast episodes related to the 2014 Rails Rumble. They agreed, and I worked with Tom and his brother Nick on the project from August through November.

I'm so happy to have done it! I met many interesting people, learned a lot about hackathons, and produced some pretty good episodes.

Here's the complete 10 episode collection:

  1. #166: Tom & Nick Mango - Rails Rumble 2014 Preview
  2. #167: Kelli Shaver - 2013 Rails Rumble Solo Winner
  3. #168: Josh Owens - 2007 Rails Rumble Winner; Bonus Meteor Intro
  4. #169: CallerKey - 2013 Rails Rumble Winner
  5. #170: Reid Carlberg - Salesforce $1 Million Hackathon @ Dreamforce 2014
  6. #171: Tales of My Solo Hackathon Entry
  7. #172: Nick Mango - and Rails Rumble countdown
  8. #173: Rails Rumble 2014 Entries
  9. #176: Rails Rumble 2014 Solo Winner - Flora Saramago (Pack Pal)
  10. #177: RefactorCop - Winner of the 2014 Rails Rumble

Thank you to everyone that participated in the series. It was great fun.

Rails Routing and the JSON API URL Format

JSON API is a "standard for building APIs in JSON".

The JSON API specification describes the following format for URLs for multiple individual resources.

The URL for multiple individual resources SHOULD be formed by appending a comma-separated list of resource IDs to the collection URL.

For example, the photos with IDs of "1", "2" and "3" will collectively have the URL:


By default, Rails applications would declare the routes for the photos resource in the following way:

# config/routes.rb
resources :photos

But, this would route the request /photos/1,2,3 to the show action in the photos controller and the following spec would fail.

# spec/routing/photos_routing_spec.rb
it "routes to the index action if multiple ids are provided" do
  expect(get: "/photos/1,2").to route_to(
    controller: "photos",
    format: :json,
    action: "index",
    ids: "1,2"

So, we need to change the routing for photos.

# config/routes.rb
  to: "photos#index",
  ids: /(\d,)(\d,?)+/
resources :photos

If you're new to regular expressions and want to play around with the constraint, you can fiddle with it here.

Now the spec passes and URLs using the JSON API format for multiple individual resources will route correctly.

Strategy for Handling ActiveRecord Validation Race Conditions

Earlier today, I was working with an ActiveRecord validation in a Ruby on Rails project that performed a calculation involving other records. Because those other records could change between the validation and the save, there was a race condition.

# app/models/person.rb
class Person
  has_many :nice_thoughts, -> { where(nice: true) }, class_name: "Thought"

# app/models/thought.rb
class Thought
  belongs_to :person
  belongs_to :comment, dependent: :restrict_with_error

# app/models/comment.rb
class Comment
  belongs_to :thought
  delegate :person, to: :thought
  validate :dont_say_anything, if: :person_has_nothing_nice_to_say
  def dont_say_anything
    if person.nice_thoughts.empty?
      errors.add :person, "if you don't have something nice to say..."

See the problem?

If the Person's only nice thought is destroyed between when the :dont_say_anything validation is executed and when the Comment is saved, an invalid record will be persisted.

To prevent the race condition, lock the person record.

# app/models/comment.rb
class Comment
  [:save, :save!].each do |method_name|
    define_method method_name do |*args|
      person.with_lock do

# app/models/thought.rb
class Thought
  [:destroy, :destroy!].each do |method_name|
    define_method method_name do |*args|
      person.with_lock do

This should work fine, but how can we test it?

# spec/models/comment_spec.rb
describe Comment do
  it "is saved before any nice thoughts are destroyed" do
    person = Person.create
    thought = person.thoughts.create(nice: true)
    comment = thought)
    Comment.class_eval do
      # pause after validation to allow for the race condition
      after_validation -> { sleep 0.2 }
    [ do
      end, do
        # ensure that the comment starts saving first
        sleep 0.1
    # the mutex should have prevented the thought from being destroyed
    expect(thought).to_not be_destroyed

This test simulates a Thought attempting to be destroyed after a Comment is validated but before the comment has been saved. If the lock works, the Comment won't be destroyed.

So, if you ever have a validation that includes a calculation involving other records, you could have a race condition. But, no big deal. Just create a lock to ensure that nothing related to the validation can change between when you validate the record and when it saves.

Note: The original version of this post used the with_advisory_lock gem by @mrm to create an advisory lock. But Matthew was kind enough to tweet me a recommendation to switch to row locking on the person. But, that gem is great if you need to create a mutex on something that isn't a row in the database.

Tracking Orders With Google Sheets

I was excited to see Cards Against Humanity announce Holiday Bullshit for this holday season. has a counter that shows their remaining inventory. It updates periodically in the background.

I took a look at page's resources to see how they were updating that counter.

Given how quickly it was selling, I decided to make a Google Sheets spreadsheet to automatically track the progress. It's nothing fancy, but updates charts that show the slots remaining and sales rate over time.

The spreadsheet includes a Google Apps Script function that is triggered each minute to update the sheet with the latest stats from the endpoint.

This is the script in its entirety.


// return the slots remaining unless the
// server doesn't return JSON
function getSlotsRemaining() {
  try {
    return JSON.parse(
  } catch(e) {
    return null;

function recordSlotsRemaining() {
  var sheet = SpreadsheetApp.
  var newRowNumber = sheet.getLastRow() + 1;
  var slotsRemaining = getSlotsRemaining();
  // don't record a new row if slotsRemaining is null
  if (slotsRemaining) {
    sheet.getRange(newRowNumber, 1).setValue(new Date());
    sheet.getRange(newRowNumber, 2).setValue(slotsRemaining);
    for each (var column in [3, 4]) {
          newRowNumber - 1,
    return true;
  } else {
    return false;

Finally, I just triggered the script to run each minute.

This is a super simple example, but it gives you an idea of how Google Sheets can be used to create a live "dashboard" using any web service.

Daily Fantasy Sports (FanDuel) NBA Optimization Model

I love the NBA. And while I don't like to gamble on games, I'm interested in the idea of gambling on games. So, I've taken a bit of interest in Daily Fantasy Sports (DFS) sites like FanDuel which are a legal form of sports betting. From their website:

Yes, Fantasy Sports is considered a game of skill and received a specific exemption from the 2006 Unlawful Internet Gambling Enforcement Act (UIGEA). FanDuel uses exactly same rules as season long fantasy sports game, the only difference is that our games last only a day.

I also like optimization models, so when I saw that Google added an optimization solver to Google Sheets and Google Apps Script, I made a note to build an example application when I saw a good opportunity.

A DFS entry is a perfect candidate for an optimization model. For those that are unfamiliar, here's the idea. The goal of DFS is to score the most points. Points are based on a combination of individual player statistics. Each player is assigned a salary by the league and there is a salary cap for a roster. In addition, your roster must contain a specified number of players in each position (2 at point guard, 1 at center, etc) and each player is assigned their position by the league.

Creating a DFS entry is a two step problem. First, you need to forecast how many fantasy points each players will score on a given night. That is a complicated problem that entire businesses are built around. Then, once you have created this forecast, you need to optimize your allocation of salary to players to maximize the total points scored while staying under the cap and filling out your roster.

I created a Google Sheets template to perform the optimization step. To use it, enter the players available in the draft with their position, projected points, and salary. Choose Solve from the Optimization menu in the toolbar and it'll output the optimal roster.

If you want to play with the source code, just open Tools > Script Editor and edit away.

How does it work?

function optimizeRoster() {
  // this sets engine to an instance of Google's LP/MIP solver
  var engine = LinearOptimizationService.createEngine();
  // clear previous selections from the Players worksheet
  // add the variables (players and their salaries)
  // to the optimization problem
  // add the contraints that ensure that the correct number
  // of players are on the roster for each position
  // add the constraint to ensure that the total
  // of the salaries on the roster is under the salary cap
  // solve the problem and set the solution
  var solution = engine.setMaximization().solve();
  // export the solution to the Players worksheet
  // copy the optimal roster to a new worksheet

Let's go through each of the methods that build up the optimization problem:

function addVariables(engine) {
  // for each player
  for (var row = 1; row <= PLAYERS.getNumRows(); row++) {
    var player = PLAYERS.getCell(
    var projectedPoints = PLAYERS.getCell(
    // add a variable for the player that enables them
    // to be selected (1) or not selected (0)
    // set the projected points as the coefficient for
    // the player's variable
    engine.setObjectiveCoefficient(player, projectedPoints);
function addPositionConstraints(engine) {
  // for each position
  for (var row = 1; row <= POSITIONS.getNumRows(); row++) {
    var position = POSITIONS.getCell(
    var positionSpots = POSITIONS.getCell(
    // add a constraint that sets the number of spots
    // required for the position
    var constraint = engine.addConstraint(
    // for each player
    for (var playerRow = 1; playerRow <= PLAYERS.getNumRows(); playerRow++) {
      var player = PLAYERS.getCell(
      var playerPosition = PLAYERS.getCell(
      // if the player plays the position
      if (playerPosition == position) {
        // add the player to the position contraint
        constraint.setCoefficient(player, 1);
function addSalaryCapConstraint(engine)  {
  // add a constraint for the overall salary cap
  var salaryCapConstraint = engine.addConstraint(0, SALARY_CAP);

  // for each player
  for (var row = 1; row <= PLAYERS.getNumRows(); row++) {
    var player = PLAYERS.getCell(
    var playerSalary = PLAYERS.getCell(

    // add the player's salary to the constraint
    salaryCapConstraint.setCoefficient(player, playerSalary);

Finally, there's a method that extracts the solution and adds it back to the Players worksheet.

function setSolution(solution) {
  // for each player
  for (var row = 1; row <= PLAYERS.getNumRows(); row++) {
    var playerId = PLAYERS.getCell(
    // set the selected column based on the optimization solution

Does it work?

The optimizaiton model works. If you forecast fantasy points by player accurately (and that's no small 'if'!), you'll play the optimal roster.

I tried it tonight in a 30 entry 50/50 contest. It looks like I have a pretty good chance to win.