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

*** 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;
    }
}

91 thoughts on “Collect/backup tweets in a Google Spreadsheet [Twitteralytics v2]

  1. Pingback:

  2. 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.

    1. Post author

      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

  3. Sarath

    Hi,

    I am not able to copy the the Twitteralytics v2.2 Google Spreadsheet.

    Can you please advise?

    Thanks!
    Sarath

  4. 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!

    1. Post author

      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

  5. Pingback:

  6. 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?

  7. 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! :]

    1. Post author

      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

  8. 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

  9. Pingback:

  10. 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?

    1. Post author

      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

  11. 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

  12. 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

  13. 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

  14. 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

    1. Post author

      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

    1. Post author

      @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

  15. 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

  16. 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

  17. 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

    1. Post author

      @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

  18. Pingback:

  19. Pingback:

  20. Pingback:

  21. Pingback:

  22. Pingback:

    1. Post author

      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

  23. 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.

  24. Pingback:

  25. Pingback:

  26. Pingback:

  27. 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

  28. 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?

  29. 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

  30. Pingback:

  31. 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.

  32. 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

    1. Post author

      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 ;)

  33. Pingback:

  34. Pingback:

  35. kat

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

    Thanks.

    1. Post author

      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.

  36. Pingback:

  37. 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

  38. 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?

    1. Post author

      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

  39. 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!

    1. Post author

      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

  40. Pingback:

  41. Pingback:

  42. Pingback:

  43. Pingback:

  44. Pingback:

  45. Pingback:

  46. Pingback:

  47. Pingback:

  48. Pingback:

  49. Pingback:

  50. Pingback:

  51. Pingback:

  52. Pingback:

  53. Pingback:

Comments are closed.