⌙ Google , Google Apps , Google Apps Script , Google Sheets , Twitter

Collect/backup tweets in a Google Spreadsheet [Twitteralytics v2]

Martin Hawksey

*** NEW VERSION – I’m keeping this post up for reference. You should get the very latest version TAGS v3 HERE! ***

Back in June I hastily threw together Using Google Spreadsheet to automatically monitor Twitter event hashtags and more. It kind of worked most of the time but there were a lot of rough edges. So here is:

*** TAGS v2.4 Google Spreadsheet ***
BTW Here’s How to archive event hashtags and create an interactive visualization of the conversation

Sticky: Looks like Google Spreadsheet is misbehaving when copying the template. If the Twitter menu isn’t appearing even when you click the button check that when you open Tools > Script editor … that there is more code than just function myFunction()... (this indicates that Google didn’t copy the Spreadsheet template properly for you). If you just see function myFunction then:

  1. try recopying the template until it works; or
  2. copy and paste the code here into the Script editor and save it

If you are still having problems leave a comment 😉
Update: I recently added the option to collect results in a single sheet. Update is included in link above. More info about this here.
Update2 (v2.3): Changes to get real user ids and entities (some other easter eggs too). Update is included in link above.More info about this here.
Update3 (v2.4): Changed code slightly to play more nicely with Twitter Search API
Update4 (v2.4.4): changed wording for weekly/daily and added option to get tweets from specific day within last 7 days

Here is a short video showing setup:

The concept is the same, you setup a search and Google Spreadsheet updates the results using a time driven trigger. The main difference is where the old version just collected date, author and tweet text this version has the option of pulling everything back available from the Twitter GET Search API. Even better you define what a where information is pulled by matching your column heading name with the Twitter search result schema, so if Twitter adds more data it’s easy for you to pull (one small caveat is it can only pull top-level data, anything nested in the results is lost apart from geo-tagging, which I thought was too useful to loose).
This was easily achieved by modifying Mikael Thuneberg getTweets() function which I originally used in version 1. This function collects search results using XML/ATOM. To achieve more flexibility I converted this to use the JSON results from Twitter. Because Google App Scripts are written using a JavaScript syntax it makes it easy to through the JSON results around and given that there is already handy bits of code for writing spreadsheet data using JavaScript Objects I was able to achieve more flexibility easily.
As my focus this time around is to just harvest tweets I dropped some of the original functionality of summarising ‘top tweeter’ and emailing results, but it’s easy enough to drop you own functions or gadgets to help you process the data. For example you could use the Map Gadget to plot geo-located tweets. This spreadsheet has some examples of what you can do with the data.
The from:briankelly sheet includes a map gadget whilst the first 5 columns of #purposed contain functions for extracting top tweeters and a Google Spreadsheet readable timestamp.
With services like twapperkeeper.com and downloadable apps like ThinkUp why have a Google Spreadsheet version? Not entirely sure I’ve got a good answer to that one. The biggest advantage is maybe its a quick way to collect tweets, make publically available and collaborate exploring the data.
Mainly for SEO here is the code for the modified version of getTweets(), the rest of the script is available in the spreadsheet.

function getTweets(searchTerm, since, until, maxResults, languageCode) {
    //Based on Mikael Thuneberg getTweets - mod by mhawksey to convert to json
    // if you include setRowsData this can be used to output chosen entries
    try {
        var pagenum = 1;
      var data =[];
      var idx = 0;
        if (typeof maxResults == "undefined") {
            maxResults = 100;
        }
        if (maxResults > 1500) {
            maxResults = 1500;
        }
        if (maxResults > 100) {
            resultsPerPage = 100;
            maxPageNum = maxResults / 100;
        } else {
            resultsPerPage = maxResults;
            maxPageNum = 1;
        }
        Logger.log(twDate(since)+" "+twDate(until));
        searchTerm = encodeURIComponent(searchTerm);
        for (pagenum = 1; pagenum <= maxPageNum; pagenum++) {             var URL = "http://search.twitter.com/search.json"             URL = URL + "?q=" + searchTerm;             URL = URL + "&since=" + twDate(since);             URL = URL + "&until=" + twDate(until);             URL = URL + "&rpp=" + resultsPerPage;             URL = URL + "&page=" + pagenum;             URL = URL + "&result_type=recent";             if (typeof languageCode != "undefined") {                 if (languageCode.length > 0) {
                    URL = URL + "&lang=" + languageCode;
                }
            }
            var response = UrlFetchApp.fetch(URL, {method:'get', headers: { "User-Agent": REFERER}});
            if (response.getResponseCode() == 200) {
              var objects = Utilities.jsonParse(response.getContentText()).results;
              for (i in objects){ // not pretty but I wanted to extract geo data
                if (objects[i].geo != null){
                  objects[i]["geo_coordinates"] = "loc: "+objects[i].geo.coordinates[0]+","+objects[i].geo.coordinates[1];
                }
                objects[i]["status_url"] = "http://twitter.com/"+objects[i].from_user+"/statuses/"+objects[i].id_str;
                data[idx]=objects[i];
                idx ++;
              }
            }
          }
    return data;
    } catch (e) {
        return e.message;
    }
}
chevron_left
chevron_right

Join the conversation

comment 91 comments

  • Tom

    looks like a great concept. it wont let me copy the spreadsheet though?

    • Martin Hawksey

      Make sure you’ve signed in to your Google account when you open it in Google Docs.
      [I’ve also added it to my public templates]
      Let me know if you are still having problems
      Martin

  • Swan

    Hi Martin. I am trying to provide a public service for people who like to Twitter Chat. I curate a Google Doc called the Twitter Chat Schedule: http://bit.ly/ChatSched for blog post or http://bit.ly/chatlist to go straight there.
    The biggest challenge is staying on top of chats that go inactive. It would help me immensely if there were a way to display the number of tweets in the last month on each of the hashtags listed. When the number dropped low, I would know to take a look or query the owners about its status.
    Thanks for pushing the envelope with this.

    • Martin Hawksey

      Hi Swan – I think if you enter a cell with
      =ARRAYFORMULA(SUM(IF(ISERROR(FIND("your search string in lowercase",LOWER(H:H))),0,1)))
      where H:H is your text column it should work
      Alternatively around line 26 of the Google Apps Script replace setRowsData with something like:
      sheet.getRange("A1").setValue(data.length);
      Thanks,
      Martin

  • Sarath

    Hi,
    I am not able to copy the the Twitteralytics v2.2 Google Spreadsheet.
    Can you please advise?
    Thanks!
    Sarath

    • Martin Hawksey

      Hi Sarath – common mistake is you can’t File > Make a copy without being logged in to Google Docs
      Martin

  • Sarath

    Thanks Mr.Martin

  • David

    Hi Martin,
    I’m looking to use the Twitteralytics v 2.2 to collect the results of a particular chat, after the chat takes place every monday from 8-9pm. What’s the best way to do this? I thought I had it set up right, but it didn’t work out so well. I can still grab them manually after the chat, but I’ll be out of internet range for a few weeks, and would love to have the sheet run itself before then.
    Thanks for your time, and this great resource!

    • Martin Hawksey

      Hi David – increasingly I’m finding the spreadsheet doesn’t work without going down the API authentication route which is much more reliable. The other thing I’d do is within the script editor set a time driven trigger to collectTweets after your chat. Once set this it should happily collect tweets even if you don’t have the sheet open (I think the demo video might cover this)
      Let me know if you need more help
      Thanks
      Martin

  • David

    Thanks Martin.
    Much obliged.

  • Anatomy of an incident: Helicopter crash at UCT | Blue Light District

    […] The latter, Twitter, can be said to be the most open and accessible of networks for analysis.  We collect tweets from Twitter on an ongoing basis whenever someone tweets about #UCT and our dataset is slowly growing.  This incident prompted many […]

  • Ryan

    Can I use this spreadsheet to archive other user’s tweets?

    • Martin Hawksey

      Yes – for the search term use from:the_persons_screenname (not sure how it will handle RTs)

  • charlton mcilwain

    Hi Martin, I’ve had the problem of not returning any tweets. I’ve run the API Authentication, but still get nothing after getting the message up to “Finished Script Collect Tweets”. Any ideas?

    • Martin Hawksey

      Hi Carlton – what message do you get when you test the connection?
      Martin

  • Benjamin Pan

    Hi Martin, I’ve been trying to get the spreadsheet to also work, but I have the same problem as Charlton above. It seems like everything is running fine, I’ve run the API Authentication with my codes, a new page shows up when I “Run Now!” However, when I refresh it, it is completely blank.
    Running Test Connection:
    [Apps Script]
    Twitter said:
    OK (Button)
    I appreciate your help! :]

    • Martin Hawksey

      Hi,
      So it looks like it’s a bug. I’ve updated the template to 2.2.1. To get your existing copy to work open Tools > Script editor and for the lines between
      // prepare search term (line 120ish)
      and
      //if no since id grab search results (line 127ish)
      replace with:
      // prepare search term
      var myUntil=new Date();
      var mySince = new Date(myUntil);
      mySince.setDate(myUntil.getDate()-1);
      if (SEARCH_DURATION == " weekly"){
      mySince.setDate(mySince.getDate()-6);
      }
      //if no since id grab search results
      Hope this helps/works,
      Martin

  • charlton mcilwain

    Works perfect. Many thanks!

  • Ed Thralls

    Martin,
    When I try to “Use Template” I get this error: “We’re sorry, your spreadsheet cannot be copied at this time. ”
    What am I missing?
    Ed

  • Ed Thralls

    Martin,
    Disregard my previous comment… I found a work around.. turns out there is a Google bug with this right now.
    Ed

  • Fuzzy

    I’ve been using v2.1 for a while and decided to check if you’d made any updates. I was happy to see v2.2 available. I made a copy of the template but have found that then I click on the “if the twitter menu is missing click here” button I get the error message: “Script function onOpen could not be found”
    Any tips?

    • Martin Hawksey

      Hi Fuzzy – works for me. I wonder if it was a temporary Google Spreadsheet server glitch or a bad copy? If you try again and still have problems let me know.
      Martin

  • Brick

    Hi Martin,
    many thanks for your wonderful approach! This can help me a lot retrieving interesting tweets.
    A question: I want to add some columns like “location” (adr), “number of followers” (follower_count), “number of following” (following_count) and “name”.
    How can I add these Metadata into TMP sheet?Do I have to reference these in the source code?
    Thank you for your help, appreciate that much!
    Greets Brick

    • Martin Hawksey

      Hi Brick – if you add these as column headers in the TMP sheet it should automatically populate the data.
      Thanks,
      Martin

  • Brick

    Hello Martin,
    the above spreadsheet uses the “GET search parameters”, so it doesnt pull data like followers_count, following_count which are parameters of “GET/users/lookup”.
    I need additionally on this spreadsheet the numbers of followers/followings of the users (user_name) which are shown on this spreadsheet.
    I know you have another brilliant spreadsheet showing “your” persons numbers of followers/followings and much more, which are coming from “GET users/lookup”.
    So is it possible to integrate these two (or more) columns in the above spreadsheet, so that I have for each user_name the numbers of followers/followings?
    Many thanks for your help. This would help me to go on with my thesis.
    Best regards,
    Brick

    • Martin Hawksey

      Hi Brick – Got yah (I’ve got too many spreadsheets doing different things 😉 Would a possible solution be to populate additional user info in a summary sheet or is it better inline with each search result?
      Martin

  • Brick

    Hello Martin,
    thanks for your help. Inline would be better. Where can I find in your Summary Sheet the number of followers/followings, number of tweets of a person, name (real name)?
    I would like to have it in this way a Metadata. I tried it with the ImportXML function but it stopps after 50 cells 🙁 So I cant use it..
    Is it possible to build the last 6 marked columns of my sheet in your sheet?
    Thank you very very much!!
    Greets Brick

    • Martin Hawksey

      Hi Brick – Here’s a modified version of the spreadsheet (File > Make a copy) which also inserts user info: name, location, description (bio), statuses_count, followers_count, friends_count.
      Please share any analysis yo are able to produce 😉
      Martin

  • Brick

    Hey Martin,
    thank you very very much for your help! Of course , I will!
    Could you please briefly explain me the Summary sheet? What does the numbers mean?
    from_user 1 mhawksey 3
    Peak_Sound 1 from_user 1
    Steffenster 1 Peak_Sound 1
    ……..
    I have added a trigger , triggering every 12 hours. The results are then given paged (not continous). Does this have an effect to the Summary Sheet?
    Thank you & greets,
    Brick

    • Martin Hawksey

      Col A is a list of unique usernames that are recorded in the Archive sheet
      Col B is a count of the number of tweets that user has made
      Col C and D contain the same info as A and B but automatically sorted so you can see who has tweeted the most
      😉
      Martin

    • Martin Hawksey

      @Brick – ah the dangers of releasing untested modifications 😉 There was a bug which meant when it didn’t find ant new tweets it inserted 130 blank rows. I’ve changed the code in the modified version of the spreadsheet.
      @charlton – if you open the script editor in your copy of the spreadsheet and insert Logger.log(data); at line 135 and then run collectTweets what does it say when you View > Log ?
      Martin

  • charlton mcilwain

    Hi Martin,
    I’ve returned to focusing on using this. But now that I’m using the latest version, I’m having the same problem I had earlier with tweets not being returned. Same message when I Test Collection – “Twitter Said:” then nothing. Last night I tried for a while, then it worked a couple of times, and then didn’t work and I would get the message when I run Test Collection that said something like: “288 Found, here’s a sample: undefined”. But again when I ran the collecttweets script, nothing returned.
    Any ideas?
    Many thanks as always!
    charlton

  • charlton mcilwain

    HI Martin – if I’ve done what you asked correctly, then what I get when I View Log is one line: 2011-09-05 2011-09-13

  • charlton mcilwain

    I decided to start over with a new copy of 2.2.3 and all worked fine all of a sudden. Not sure if that made the difference or whether my problem will recur. While it is working at the moment though, I did notice that none of the geo data is coming through. I just get blank cells.
    charlton

    • Martin Hawksey

      @charlton – glad it has worked out for you eventually. The blank geo data is because the majority of people don’t include this in their tweets (at a recent event with over 800 people tweeting 1% geo-encoded their tweets)
      Martin

    • Martin Hawksey

      Hi Brick – I know there was problems yesterday getting data into Google via Twitter. What to do is delete the blank rows and try running the script again (the latest version of the spreadsheet 2.3 contains a fix to prevent this. The part of code to look for is just after the comment // if some data insert rows and replace if (data.length>0){ with if (typeof data[0].from_user != "undefined"){
      Cheers,
      Martin

  • David

    Hi,
    Is it possible to configure the spreadsheet to run hourly, or every 10 minutes?
    Thanks, Dave.

  • David

    Thanks Martin. PS, I get an error when I try to run the spreadsheet which is: –
    TypeError: Cannot read property “from_user” from undefined. (line 164)
    I think I have followed all the instructions properly.
    Dave.

    • Martin Hawksey

      @Dave – left some stray debug code – updated the spreadsheet to 2.3.1 to fix
      Thanks,
      Martin

  • ProfHacker - The Chronicle of Higher Education

    […] the adventurous, Martin Hawksey has also developed TAGS, another solution for storing tweets, this one powered by Google Apps. Though more powerful, TAGS […]

  • Tim McCormick

    not working for me — when I run “Test Collection” I get:
    TypeError: Cannot read property “from_user” from undefined. (line 166)
    Version: Twitteralytics v2.3.1
    Settings:
    Who are you tjm.org
    Search term mccormicktim
    Period daily
    No. results 1500
    Continuous/paged continuous
    suggestions?
    thanks,
    Tim

    • Martin Hawksey

      @Tim yes experiencing similar problems myself. Going over the code to try and make it more twitter API friendly. Stay tuned
      Martin

    • Martin Hawksey

      @Tim Updated to 2.4 – it’s working better for me hopefully you too.

  • Matt

    Having issues running this today. Successful with your followers spreadsheet but not getting any results on this. Have authenticated successfully, tried different API key, amended all the boxes at the bottom (who are you, search term etc.) Any hints?

  • Liz

    Hi Martin,
    When I run the Test I just get an error message:
    Apps script
    Twitter said: null
    I have tried changing the search term to FREE as I was sure it would find something but it didn’t.
    Best wishes,
    Liz

  • Google Spreadsheet to NodeXL to Google Spreadsheet to Web – MASHe

    […] serviceCollecting any data in a Google Spreadsheet using GET or POST by publishing as a serviceCollect/backup tweets in a Google Spreadsheet [Twitteralytics v2]Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]The best […]

  • Isaac

    Same problem to Fuzzy, Google Docs kept telling me “Oops
    Script function onOpen could not be found”. I was using v2.4.3 from the link above.

  • charlton mcilwain

    HI Martin – I’ve just copied Tags v2.4.4. When I try to use it however, the Twitter menu us missing. When I click on the box, I get an error message that says “Script function onOpen could not be found”. When I open up the script editor, there appears to be nothing there except a “myfunction” script with the following code: function myFunction() {
    }. That’s it.
    Any ideas?
    charlton

    • Martin Hawksey

      Hi Charlton – Looks like Google Spreadsheets are misbehaving right now. Here’s a note I’ve posted on another thread. Thanks for letting me know Martin
      When you open Tools > Script editor … and only see function myFunction()... this indicates that Google didn’t copy the Spreadsheet template properly for you. You can:

      1. try recopying the template until it works; or
      2. copy and paste the code here into the Script editor and save it

      If you are still having problems leave a comment 😉

  • Alternatives To Twapper Keeper « UK Web Focus

    […] approach: “Martin Hawksey is a master of Google Spreadsheet tools and has created this alternative method of collecting tweets and has provided detailed instructions to archive and visualise Twitter conversations around an […]

  • kat

    Martin,
    This is awesome. Is it possible to pull tweets for longer than the default- like all tweets in the past year?
    Thanks.

    • Martin Hawksey

      Unfortunately not. Twitter search api is limited to last 7 days. Twapper Keeper used to my go to place for longer archives but it was sold to hootsuite.

    • Martin Hawksey

      That method was used in this study . Storage limits of Google Spreadsheets (400,000 cells) mean its probably better to find a different platform to do this with

  • Twitter Archiving Google Spreadsheet TAGS v3 – MASHe

    […] serviceCollecting any data in a Google Spreadsheet using GET or POST by publishing as a serviceCollect/backup tweets in a Google Spreadsheet [Twitteralytics v2]Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]The best […]

  • stefania

    Martin,
    this is great! I created an archive a week ago and everything worked fine for the first few days. Unfortunately, now I can’t open the Archive spreadsheets anymore. I get this message instead: “Service timed out: Spreadsheets (line 167)”.
    Am I doing anything wrong?
    Thanks

    • Martin Hawksey

      Sounds like a temporary google problem to me. Sometime Google Spreadsheets seem to put themselves into a spin but usually they recover

  • stefania

    Thank you Martin. It has been like that for a few days now, and I have tried to open the archive several times. Could it be because the number of lines in the Archive spreadsheet is reaching (or has already reached) the limit?

    • Martin Hawksey

      I find google spreadsheets very occasionally become corrupt and are very slow to open. In those instances I stop the trigger (only possible if you are getting script failure emails), export the data and start again. I use excel to clean data and merge datasets

  • Kelli Burns

    I’m using TAGS v 3.0 to collect tweets to a certain user. Is it possible to also add tweets from this user to my spreadsheet?

  • Amy

    Hi Martin,
    This is great – so useful! I have set up the spreadsheet and got it working for a simple search (from:username). I will try getting it to run itself using the Triggers. I also want to check out your post on Node Xl.
    I was wondering, however, what they best way was for scraping tweets from multiple users, around 100? Is there a way of writing the search command? Something like “From:username! +from:usernameB…” I tried this but did not work…
    I thought maybe a list was one way but I don’t want to make a list.
    Also, perhaps the only way is to make 100 spreadsheets? this is possible but was just wondering if twitter would let me do this?
    Thanks again!

    • Martin Hawksey

      Hi Amy – there’s probably a limit to the number of froms you can put in but in theory it should work. An alternative approach you might consider is this other spreadsheet template I developed to extract user timelines. The benefit of this method is you are not limited to the last 7 days, instead you can extract a users last 3,200 tweets. Also with this you can capture multiple timelines per spreadsheet but you might want to setup a couple of these spreadsheets to spread the load.
      Martin

  • #ScotlandHour Tweetup – How we’re Growing!

    […] Collect/backup tweets in a Google Spreadsheet [Twitteralytics v2] – MASHe Back in June I hastily threw together Using Google Spreadsheet to automatically monitor Twitter […]

  • TAGSExplorer och EdcampGBG -

    […] kör jag en liten screencast från programmet TAGSExplorer för att visualisera hur en konversation på twitter “ser” ut, hur lärare utnyttjar […]

  • #MLA14 network, 6pm Friday to 10am Sunday | John Mulligan

    […] out there, Ernesto Priego’s, which uses d3.js. His visualization, which is searchable, uses Martin Hawksey’s TAGS. Priego is also posting regular updates on #mla14  statistics on his twitter […]

  • Altmetrics | DITAdventures

    […] session we used Martin Hawksey’s TAG’s explorer to archive tweets with #citylis. Here is a tweet linking to the searchable twitter archive (all in […]

  • Export Twitter Followers and Friends using a Google Spreadsheet | MASHe

    […] and Google Spreadsheets (See Populating a Twitter List via Google Spreadsheet … Automatically!, Collect/backup tweets in a Google Spreadsheet, Google Apps Script, Spreadsheets, Twitter and Gadgets) the Twitter/Google Spreadsheet back is well […]

Comments are closed.

css.php