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 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 = ""             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"] = ""+objects[i].from_user+"/statuses/"+objects[i].id_str;
idx ++;
return data;
} catch (e) {
return e.message;


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

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


  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: for blog post or 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. 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:



  3. Hi,

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

    Can you please advise?


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

  4. Thanks Mr.Martin

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

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

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

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

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

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


      //if no since id grab search results (line 127ish)

      replace with:

      // prepare search term
      var myUntil=new Date();
      var mySince = new Date(myUntil);
      if (SEARCH_DURATION == " weekly"){
      //if no since id grab search results

      Hope this helps/works,

  9. Works perfect. Many thanks!

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


  11. Martin,

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


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

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

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

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

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

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

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

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

    1. @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 ?


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


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

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


    1. @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)

    1. 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"){

  20. Hi,

    Is it possible to configure the spreadsheet to run hourly, or every 10 minutes?

    Thanks, Dave.

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


    1. @Dave – left some stray debug code – updated the spreadsheet to 2.3.1 to fix

  22. 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
    Who are you
    Search term mccormicktim
    Period daily
    No. results 1500
    Continuous/paged continuous


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


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

  24. 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,

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

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


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

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


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

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

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


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

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

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

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

Comments are closed.