— Simon Ensor (@sensor63) January 15, 2014
Happy New Year to you too Simon! Having worked with Twitter and Google Maps API I was aware that their terms are becoming increasingly restrictive making the environment for 3rd party services for doing this increasingly difficult. There is a solution for doing using a modification of my Twitter Archiving Google Spreadsheet (TAGS) project (the guerrilla approach so to speak). The result for #rhizo14 is here (only viewable in non-mobile app versions of Google Maps and not the current preview version) and this post outlines how it was done.
How it was done
If you want to shortcut this here's a fork of TAGSv5.0 which already has the extra columns sheet, script and formulas
You take your off the shelf version of TAGS v5.0 and in the ‘Archive’ you add the columns with the headings user_location and user_description. Where doesn’t entirely matter, in this example they end up as columns P and Q respectively (if you have different you’ll need to amend the formula in the next bit). You can add these columns to an existing archive but only new tweets can be resolved to a location.
After collecting data for a while next you create a new sheet in your copy of TAGS with the name ‘CacheMap’. In cell D1 of this sheet enter the formula
=QUERY(Archive!B:Q," Select P, Q, B, M, COUNT(J) WHERE P <> '' GROUP BY B, P, Q, M ORDER BY COUNT(J) desc LABEL B 'Top Tweeters', COUNT(J) 'No.'",TRUE). This creates a couple of columns of data extracting the unique screen name, user_location, user_description and count of tweets in the archive.
Next open Tools > Script editor and paste the following code:
Save the script and run ‘cacheMap’ from the Run menu. What this does is read the list of data imported to the ‘CacheMap’ sheet and then use the geocoder in Google Apps Script Maps Service to try and resolve location data from the users Twitter profile. We cache the location data in Google Apps Script noSQL storage (ScriptDb). The script gets the first 100 hits to prevent timeouts. If you have more than 100 users in your list running the script again will read cached results and top-up with new data. You can also schedule the script to run automatically from the Resources > Current script triggers to keep it fresh. The results are outputted to the same sheet in KML format. To render as a map in the File > Publish to web menu we can build a TXT link for the CacheMap sheet specifying the A column as our source.
Pasting the url into the Google Maps search box and hitting return gives us a map (a trick I picked up from Tony Hirst in the days when a simple Twitter map was simple). Like Tony’s solution this hack also has a self life. Google are ‘previewing’ a new version of Google Maps which doesn’t currently support the ‘paste a kml url’ trick. Also last time I looked the New Google Sheets doesn’t have a publish to web option. <sigh>
Enjoy … while it lasts!