Wednesday, March 14, 2012

Update on March Madness Pool Templates (How I fixed it)

It seems that the =googletournament() function will most likely not be updated in time.  For those still wishing to use Google Docs for your own bracket or Pool, however, you still have an option.  I've updated the templates I created to still work.  Instead of getting your information from Googleland you will be getting it from me.  As long as you don't change any grey background fields on the spreadsheets, your brackets will update with the winners automatically, assuming I am near a computer at the completion of a game (I probably will be as I'm a huge basketball fan).

Since this blog is about Google Docs, I thought I might quickly step into that I did on the backend of these templates to make it work.

I decided that my method would be to create a master spreadsheet that I update.  My template then uses =importrange() to pull various ranges from the master spreadsheet.  As long as you have viewing rights to a spreadsheet, you can pull data from it using importrange(). So this should work.

I divided the Full Bracket tab for both the Single and Group Template into 3 sections; the left side of the bracket, the right side of the bracket and the middle.  This way I only had to write 3 importrange() functions. I decided against using one.  Now, the Full Bracket tab is pulling in the data from my master spreadsheet in three sections.  As soon as I update the master sheet, anybody who references my master sheet (anybody using my templates) will see their data update within a few minutes.  The blank bracket templates simply reference the Full Bracket so that we don't end up with a million importrange() function (you can only actually have up to 50).

If anything, the spreadsheets might actually be quicker because 3 importrange() functions should be lighter than a billion =googletournament() functions.

Thanks for being patient.  Hopefully next year Google will be able to fix the function so that it works (I'll make sure of it this time).

Cheers and good luck with your brackets!

6 comments:

  1. Hi Ted, we fixed the GoogleTournament function today. The API we are using unexpectedly changed this year. The templates from last year should now work for this year as well, but impressive workaround! We'll post a 2012 template before the first game on Thursday.

    ReplyDelete
  2. Hi Daniel,
    Thanks for the update. There is still a problem with the function that I'll escalate through our normal channels. I'll be curious to see what the 2012 template looks like.

    ReplyDelete
  3. Sorry but still an issue (Murray State = Murray) and points aren't updating.

    ReplyDelete
  4. I'm updating now. I didn't know what Google's shortnames would be so I have to manually update a few functions on my back end. It should be updated soon.

    ReplyDelete
  5. Still no points updating. Do I need to enable that somehow?

    ReplyDelete
  6. See my latest post that solves the points issue.

    ReplyDelete