Chris L Keller ...

UPDATE TO THE UPDATE: I’m not aware of a straight-up-set-it-and-forget-it sync between a Google Spreadsheet and a Fusion Table. But using the script and setting a trigger to run the script will get you close though.

John’s script will allow you to update a Fusion Table from a spreadsheet.

Then you can trigger the script in time increments by setting up a trigger, but there are still restrictions with using the trigger method.

There is also a script that will allow you to take data from a Google Form, which is sent to a spreadsheet and then synced to a Fusion Table:

Either of these methods are more efficient than updating spreadsheet, downloading a csv of the spreadsheet, deleting Fusion Tables rows and then importing the csv to the Fusion Table.

UPDATED: Clarified the need to authenticate your email address before running the Update Fusion function in the script.

Last week Kathryn Hurley of the Google Fusion Tables team offered up an Apps script that basically allowed a user to submit data to a Fusion Table from a form, using Google Spreadsheets as a conduit.

This week, John M. from the Fusion Tables Google Group has offered up his version of an Apps script that updates a Fusion Table from a spreadsheet.

I’ve also posted the script to a gist here. And an updated version — that works off the active sheet — lives here.

Then John McGrath posted an Apps script to GitHub that updates a Fusion Table from a spreadsheet.

The two methods sound similar, but they do operate in different ways. First I’ll walk through how I got John M.’s script up and running. Then I’ll illustrate some differences.

• For illustrating the script, I set up a simple spreadsheet with three, imaginatively-titled columns.

• Then I create the Fusion Table based off that spreadsheet.

• Once created I grab the Fusion Table’s numeric ID by going to File —> About. This ID is 2793906.

• Back at the spreadsheet now, and go to Tools —> Script Editor. I’ve only just learned about the potential here, but needless to say there is a high ceiling. Anyway, paste your code here and click save. You will be prompted to give the project a name.

• Go back to the spreadsheet again and head to Tools —> Script Manager. Here you’ll get a window will all of the functions available to you. Highlight onOpen and click “Run” and then “Close.” You should now have a menu item Called “Fusion Tables” staring at you.

• Click the menu item you find three options: Update Fusion Table; Change Email Information (authentication) and Change Range of Data to be Sent.)

• The second option allows you to authenticate or “grant access” to send data from a spreadsheet to a fusion table. This is a must do before proceeding and is necessary for the script to work

• After you authenticate you will want to to “Change Range of Data to be Sent.” To do this, simply highlight the cells you will be POSTing and click the menu item.

• From there, you are ready for Update Fusion Table which should be self explanatory. That will send the data housed in the spreadsheet to Fusion Tables. 

• You will get a confirmation box. Go ahead and click OK. You will get another cofirmation box saying the range has been changed.

• Now just add some information to your spreadsheet and click the Update Fusion Table menu item. If I wanted to change something around I can just make the changes and click Update Fusion Tables.

• Sit back and enjoy this moment.

So what are the differences between these two scripts. After a cursory runthrough of both, here’s what I’ve found.

Apps Script to use Form —> Spreadsheet —> Fusion Tables

• Requires “a role account” because script editor will display unencrypted password.
• Data is sent via a form to the spreadsheet and then POSTed to the Fusion Table.
• Data added via the form can be updated in the spreadsheet and synced with Fusion Tables. But the script does not support adding new data to the spreadsheet and POSTing it to the Fusion Table.
• A sync between the spreadsheet and the table can be added — minute by minute, or hourly — and those changes will be reflected on the table.
• It’s not possible to modify the form — adding another question — or modify the table’s schema once the script is being used.

Apps Script to use Spreadsheet —> Fusion Tables

• Authenticates against user’s account.
• If I add columns to the spreadsheet, I then need to add a column to the Fusion Table, which isn’t difficult, and once completed the script works fine. Perhaps we can figure out a function that would do that and add it to the menu items?

The difference between a writer and a moderated writer: the latter is not allowed to publish directly to the blog. Instead, each post they produce is placed into a moderation queue.

via blog.scribblelive.com

Live blog and chat platform has rolled out a couple solid features for users.

The new permission level — moderated writer — fills a solid need. You don’t want to think too hard about permissions, but at the same time, options are good.

The second features further build out LiveArticle, which is a writing tool used to create digital articles during a live blog or live chat. Writers can now add subheadlines and can create ordered and unordered lists.

Updated: A Fusion Tables Google Group user posted a script that eliminates the need for a form. I wrote about it here. It allows you to update a Fusion Table from the Google spreadsheet. You still need to trigger it, but it works well.

Among the many feature requests/issue tickets for Google’s Fusion Tables and the API has been the ability to submit information to Fusion Tables via a form, and to keep a spreadsheet and a table in sync.

If you want a basic setup — grab user information and POST it — the wait is somewhat over, though some restrictions apply.

With a simple script from Kathryn Hurley of the Google Fusion Tables team, you can set up a Google Form that your users can use to input data to a Fusion Table. The script takes advantage of Google’s AppsScript tool to transfer new form input into your table.

There are just a few steps to get the script up and running:

• Create a form and set up an empty table.
• Set up the script and activate it.
• Send the form out to your users.

There are some necessary steps, caveats and restrictions:

• Create a new Google account — “a role account” — because, as the walkthrough says, the script editor will display your unencrypted password, which makes me wonder whether this should even be considered a solution.
• That said, it does work. I can send data via a Google Form that is added to a Google spreadsheet and then POSTed to a Google Fusion Table.
• I can also update information in the spreadsheet, and through a sync between the spreadsheet and the table — minute by minute, or hourly — those changes will be reflected on the table.
• The big caveat? As written, it’s not possible to modify the form — adding another question — or modify the table’s schema once the script is being used.

In the end, you get three elements tied together by a pretty good little script:

See the full gallery on Posterous

My takeaway?

• With some of the heavy lifting out of the way — especially for beginner like me who wouldn’t know where to start writing a script like this — I think the Fusion Tables community can work through some of the restrictions and develop solutions for them, especially being to simply add a Fusion Table row from the spreadsheet.
• My personal test of this functionaility didn’t deal with geographic information, geo-coded addresses, location columns or shapes. Though all of that can be used — per the walkthrough — I can’t vouch for how it works in practice. My potential project that would use geographic data at this point wouldn’t need to use a form as much as I’d need the ability to update information housed in the spreadhseet and sync to a Fusion Table.

So I’ll report back once I’ve tried this method and combined it with plotting location… Still, I’m closer to my goal and now I have a foundation to work toward something like this…

It’s easy to point a finger at a student news site that published inaccurate information. It’s humbling to see the managing editor there take responsibility and resign. It’s harder for the pros who run established newsrooms to look in the mirror and acknowledge where we’ve made mistakes. Harder still to share those lessons with our staffs and our community.

via Carl Lavin: The Business of News

Carl Lavin’s list is the makings of a great checklist that should be memorized by anyone committing acts of journalism in the digital age.

I’m left feeling that any news org with a policy on online breaking news and corrections, clarifications and retractions should post, promote and link to it if not doing so already.

Doing so — in this day and age — is as natural as putting contact information in the newspaper.

Those news orgs without such a policy would be right to find a boilerplate solution, customize it and adhere to it.

Thanks to the Government Accountability Board’s Beta Webcam, I can watch — but not hear — folks processing recall petitions in real time… If only we could program some kind of counter that displayed when a signature was approved and when one was rejected.

We’ll save that for the next time around.

Problems I would love to have to talk about: What is the career path for a developer in a newsroom? There isn’t one right now. Who will be the first to hire a developer as an assistant managing editor or above? I ache for the day we have to discuss this instead of the scarcity of talent. I long for the day when we have to debate turning over editorial strategy to someone who came into the newsroom to build apps. That will be a great day.

via blog.mattwaite.com

As the first digital editors have begun to ascend to leadership roles in the newsroom, I think this will become more commonplace. But the sooner the better?

A text message I received last night while asleep… I’m left with so many questions…

Is it something I did?

Is there someone else?

Is that you don’t know how to spell irritated, but know it when you feel it?

Oh, and here’s one… who are you?

No time for cheaters by Chris Keller

In the April/May issue, Thomas Kunkel asks in Above the Fold if the pressure of a non-stop news cycle or the pressure to produce more news with fewer resources has anything to do with the increasing instances of fraud, fabrication and plagiarism in the industry.

As the editor of a weekly newspaper who covers the school board, police and six high school sports in the fall, winter and spring, in addition to my duties as editor, which include layout and coaching a staff of three reporters, I have to both confirm and deny these suspicions.

Some will say that the weekly newspaper gig is just what I have described—a whole lot to do and no time to do it. But does that give those who work at a daily the excuse to fake and make things up? I have a fixed time to do anything I do and do it well. Heck, I even have competition from another weekly newspaper in a readership market of 8,000 people. But competition is not an excuse for dereliction of duty.

While newsrooms across the country are cutting back on staff, perhaps it’s high time the cream rises and the pretenders are discovered and tossed aside. And if anyone has an opening after purging itself of a liar or a cheat, there are plenty of us out here who still believe in the role of the newspaper in society and public discourse.

CHRIS KELLER

Editor

Sauk Prairie Eagle

Sauk City, Wisconsin

via findarticles.com

… and add places you used to work

Thursday — at Hacks/Hackers Chicago — I’m lucky enough to be able to help lead a beginners’ walkthrough of using Google’s Fusion Tables to create wicked-fast maps of data points and merge data points with geographic shapes for mashup maps.

I say lucky, because a year ago this time, I didn’t know how to do any of this. But thanks to some detailed tutorials — which I’ve tried to link to — and the time of John Keefe, Jon Davenport, Christopher Groskopf, Ryan Murphy and countless others, I was able to learn how to make these maps, which pushed me further on my way to shedding all vestiages of my former sportswriter/editor self, and traveling down the road of javascript, web scraping with python and figuring out what an AWS instance is. And that requires a whole new round of thank-yous. 

But seriously, it’s my hope to be able to pass along what I’ve learned and continued to be inspired by this merging of journalism and code, with a dash of entrepreneurship.

Below is an outline of what I plan to talk about. If I’m missing anything, let me know in the comments, but remember this is the beginner portion, so I’m skipping the Google Maps API for now and focusing on the iframe embeds.

Derek Eder and Juan-Pablo Velez have the intermediate to advanced — aka code-time — portion of the program.

Also included are a list of bookmarks to the various things I’ll be using in the presentation, in case you want a head start.

I’ve posted a demo map and the basic iframe embed code to make it as well, you know… in case you want to get started.

• Fusion Tables Walkthrough
  • Offer a handy spreadsheet function.
  • Basic intro to some easy geocoding of addresses, which means easy maps.
  • Merge/mashup with additional data/geo sources.
  • API allows access/re-purpose of stored data.

• Getting Easy Data
  • Use formula to import html table to spreadsheet
    • Find Data
      • Chicago Park Facilities
    • =ImportHtml()
      • Formula Structure
      • =ImportHtml(“http://www.chicagoparkdistrict.com/facilities/beaches/”, “table”, “1”)
        
        • URL you want.
        • HTML element you want to grab
        • Order in document
    • Copy all and paste as values
    • Data gathered from
      • Chicago Beaches
      • Chicago Outdoor Pools
      • Chicago Indoor Pools
    • Complete Spreadsheet

• Mapping the Data
  • Create Fusion Table
    • Import from Google Spreadsheet
    • Upload from file
    • Create a new table
  • Column Types
    • Location for Geocoding
  • Visualize data as a map
    • Adjust column to generate location
    • Custom Info windows
    • Custom Markers
    • iframe embed code

• Merging shapes and data
  • Where to find Shapes
    • Chicago Facilities & Geographic Boundaries
    • U.S. Census/TigerLine – How to upload Shapes
    • ShapeEscape
  • How to merge Shapes with data

• Other Tools

  • View & organize in Google Docs
  • Fusion Tables Layer Builder

• Mapping Examples
  • Madison Mayoral Vote
  • Madison City Council Map
  • Wisconsin Gov Map
  • Derek’s Bike Crash Map

Data Used in Demo

• Chicago Park District

• Chicago Beaches

• Chicago Outdoor Swimming Pools

• Chicago Indoor Swimming Pools

• Completed Chicago Swimming Spreadsheet

Tables Used in Demo

• Chicago Swimming – Non Custom Markers

• Chicago Swimming – Custom Markers

• Chicago Neighborhood Shapes

• Chicago Community Shapes

Geo Shapes & Geo Shape Upload

• City of Chicago GIS

• 2010 TIGER/Line® Shapefiles

• Upload Shapefiles to FT

Example Maps

• Madison’s 2011 Mayoral Vote

• Madison City Council Map

• How You Voted – Governor

• Chicago Bike Crash Reports

• Example Gallery – Google Fusion Tables

Resources

• Spreadsheet Formulas

• View & organize Fusion Tables and shape files in Google Docs

• FusionTablesLayer Wizard

• Fusion tables Posts

• Paul Bradshaw Links on FT

As a Packers fan looking over the NFC playoff field, there isn’t a single team that I look forward to seeing as an opponent. There are too many potential interlopers into this quest for back-to-back Super Bowl titles … too many chances for other teams to come at the king.

The Saints are an obvious team to want to avoid. They are hot, they are playoff tested, they can score and they have the intangibles on the field and on the coaching staff. Then there is the Week 1 matchup that – for all itensive purposes – was a coin flip away from going in New Orleans favor there at the end of the game. At least this matchup wouldn’t happen until the NFC Championship game. And it would be in Green Bay… where it was nearly 50-odd degrees today.

No need to see the Lions again either. Having faced Detroit twice this season – including the Week 17 shootout, I do not want to face a week of the sure-to-come “No team beats a team three times in a season” talk.

Atlanta? No way… Not only do they have payback on their minds from last year – and from earlier this season as well – but the situation would be nearly reversed from a year ago. In the Divisional round, the Packers went into Atlanta as a wild card and knocked off the NFC’s top seed. Then there is that 2003 playoff game against Atlanta at Lambeau… Sure, Michael Vick isn’t there, but do we need to see the replays?

And previous postseason disasters would also be in the air should Green Bay face the Giants. Hello… 2007 NFC Championship game anyone. Nevermind that New York played the Packers as close as anyone this season, and they matchup really well in the areas they would need to in order to defeat Green Bay. The Giants know how to navigate through the playoffs in these situations… And that doesn’t excite me.

So that leaves San Francisco, and while many of the points above also apply to San Francisco, there is one aspect that scares me more than any other. The 49ers are in the same position now that the Packers were in 1995 when Craig Newsome forced a fumble and Wayne Simmons returned it for a touchdown and sent the Packers on their way to a long-awaited win of San Fran.

And of course the 49ers have enough talent to make this happen.

Ah ya know what… Heck with it. Bring it on. Green Bay is the defending champs until they aren’t. Enjoy the playoffs everyone.

During some training in open data I was doing recently, I ended up explaining (it’s a long story) how to pull a feed from Delicious into a Google Docs spreadsheet. I promised I would put it down online, so: here it is.

In a Google Docs spreadsheet the formula =importfeed will pull information from an RSS feed and put it into that spreadsheet. Titles, links, datestamps and other parts of the feed will each be separated into their own columns.

When combined with Delicious, this can be a useful way to collect together pages that have been bookmarked by a group of people, or any other feed that you want to analyse.

Here’s how you do it:

via onlinejournalismblog.com

I hadn’t seen or used the =importFeed spreadsheet forumla, so that was a big lesson in this Paul Bradshaw post. But also, there are some interesting possibilities with combining Delicious and Google Docs.

Note: It’s interesting to look back at this now, some six years since I wrote it. First, I was finally able to see — and boo Favre — in person the second time he came back to Lambeau Field as a member of the Minnesota Vikings.

But more importantly, to me at least, is knowing that today — Jan. 2, 2012 — the Badgers are appearing in their second straight Rose Bowl game and fifth since the 1993 season.

And yesterday — Jan. 1, 2012 — the defending Super Bowl champion Packers finished a 15-1 season and had a backup quarterback throw for the most yards in a single game and most touchdowns in a single game in team history.

Not bad Wisco… Not Bad at all.

JANUARY 2006: The first two days of 2006 brought fans of Wisconsin sports such a combination of emotions. While wins for the Green Bay Packers and the Wisconsin Badgers rung in the New Year, there’s an unmistakable feeling that the victories signal a changing of the guard and the end of two legendary eras.

Packers quarterback Brett Favre, who likely played his final game for the Green and Gold, and Badgers coach Barry Alvarez, who coached his last game in the Capital One Bowl, didn’t just resurrect their respective teams.

The provided a resurgence in the state. They instilled pride in the state’s fans and inspired countless others across the nation. And while losing both of them in consecutive days would seem like a heartless twist of fate, perhaps it was simply fate.

I was at Alvarez’s first game against California in 1990. I watched his final game on television. In between, I saw and listened to countless others. As a fan, I always had a sense as though we had an unfair advantage over our opponents – the man roaming the sidelines.

I never saw Favre play in person, but I do know his play on the field has been recreated in sandlots and backyards from Ashland to Janesville – never mind from New York to L.A.

Both teams’ rise to prominence coincided with my coming of age – my years in high school when I would clip out articles from the local papers’ sports sections and offer my opinions on the Badgers sniffing roses in 1993, or Brett “the Human Turnover” Favre in a sophomoric sports column.

Those glory years intensified when I was in college, when I began to learn the craft of journalism.

Sitting now in my Chicago apartment, having watched Favre and Alvarez leave the field, I realized they and their teams were as much an inspiration for my career choice as anything.

Poring over those old papers, I learned how to spin a tale, craft a lede and offer connections between seemingly random events. Today, as fans of the Badgers and Packers got to see two legends leave the field with their heads held high, I realize nothing is truly random.

I also realize that in something so dominated by stats – Alvarez’s 118 wins or Favre’s 396 touchdown passes – sports isn’t just about competition.

It’s about self-realization, inspiration and motivation. It’s about memories and looking back at the journey. And it’s about the next legends that not only transform a state, but also transcend the field and allow us in on the ride – the smiles and heartaches that go along with it.

So our little cat — Little Brother — really like to play fetch with the fuzzy pom-pon type balls that you’d put on a child’s winter hat.

When I went to the shelter to find the cat that came to be known as Little Brother, I brought one of the balls with me, and he loved playing with it.

Now, we’ll lay in bed, toss the ball of the door and down the hall, and he’ll dive from the bed, run after and bring the ball back in his mouth, trotting proudly.

But, every month or so for the past year he’d lose his ball and we’d have to break out another one, or head to the store to buy more… My wife and I would sit and wonder where they all go. “There’s got to be a stash of them somewhere in this apartment,” we have said more than once.

Today, Shawn found the stash… beneath a drawer… SIX of them, all tucked away. Like a dog’s bones buried the backyard…