Export Twitter Followers and Friends using a Google Spreadsheet

Note: Twitter recently wrote to me reminding me that

methods of obtaining and exporting Twitter content obtained via the Twitter API is prohibited by section I.4.A of our API Terms of Service (https://dev.twitter.com/terms/api-terms ), specifically:

You will not attempt or encourage others to sell, rent, lease, sublicense, redistribute, or syndicate access to the Twitter API or Twitter Content to any third party without prior written approval from Twitter. If you provide an API that returns Twitter data, you may only return IDs (including tweet IDs and user IDs). You may export or extract non-programmatic, GUI-driven Twitter Content as a PDF or spreadsheet by using “save as” or similar functionality. Exporting Twitter Content to a datastore as a service or other cloud based service, however, is not permitted.

Following clarification from Twitter I making this template available again but please note.

By using this template you agree it is for personal use only and the data is not made publicly available.

Recently I’ve noticed a growing number of people arrive at this blog having searched for ‘export twitter followers’. Rather than them leaving disappointed here’s a Google Spreadsheet I threw together which allows you to grab a copy of your friends/followers:

*** Google Spreadsheet to Export Twitter Friends and Followers – NEW Version ***
Update 27/11/2014: This has been updated to a new version that uses the TwtrService Library (one time authentication)

Benefits of using Google Spreadsheet

  • Control – You register for your own API key with Twitter so you have full control of the account
  • Playing with the data – as you are importing straight into a spreadsheet you can do all of your own data manipulation like sorting, filtering and creating your own formula for things like follow/follower ratios
  • Backup – Google Spreadsheets allow you to download copies of spreadsheets in different formats
  • Share – You can make your lists of friends/followers easily viewable

Where’s this all going?

Having already done other things with the Twitter API 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 and truly broken. You’ll probably see fewer posts one this area with new stuff instead I’ll probably start properly documenting the little code snippets I use (but if you have any interesting ideas you want help with get in touch).

This doesn’t mean I’ll be walking away from Google Spreadsheets. As recent posts like Turning Google Spreadsheets into a personal or group bookmarking service, show there is huge scope in using Spreadsheets as a very flexible rapid development platform.

Below are some bits of the code used in my new spreadsheet (all the code is viewable via the Script Editor in the Spreadsheet):

Update: This was the old way data was collected from the Twitter API

function tw_request(method, api_request){
  // general purpose function to interact with twitter API
  // for method and api_request doc see http://dev.twitter.com/doc/
  // retuns object
  var oauthConfig = UrlFetchApp.addOAuthService("twitter");
  oauthConfig.setAccessTokenUrl(
      "https://api.twitter.com/oauth/access_token");
  oauthConfig.setRequestTokenUrl(
      "https://api.twitter.com/oauth/request_token");
  oauthConfig.setAuthorizationUrl(
      "https://api.twitter.com/oauth/authorize");
  oauthConfig.setConsumerKey(getConsumerKey());
  oauthConfig.setConsumerSecret(getConsumerSecret());
  var requestData = {
        "method": method,
        "oAuthServiceName": "twitter",
        "oAuthUseToken": "always"
      };
   try {
      var result = UrlFetchApp.fetch(
          "https://api.twitter.com/1/"+api_request,
          requestData);
      var o  = Utilities.jsonParse(result.getContentText());
    } catch (e) {
      Logger.log(e);
    }
   return o;
}

Using the TwtrService Library it’s replaced with:

function tw_request(method, api_request){ 
  return TwtrService.get(api_request);
}

288 Comments




  1. Awesome. Used Ur link @ the top and it worked perfectly. Thanks!


  2. Thank you for this tutorial! is there anything more simple that will just count the number of followers for a list of twitter accounts? not xml export, because google docs only allows 50 of these per sheet, i need a few more than 50…. any tips very helpful

    thank you!



  3. Hey,

    Thanks for that, it works perfectly. I’m only having trouble to enable the other possible column headers in Advanced Options. Could you please help me?


    1. Hi – it should just be a case of copying the names into a cell at the top of a blank column (unless twitter have changed the mapping)
      Martin


  4. Hi! I’m trying to use your spreadsheet, but it fails. I copied both keys to the configure menu. After that, I click on “test connection” aand a msgbox replies “Authorization is required to perform that action.”. Do you know what could be working wrong?


    1. Hi – in the spreadsheet of you go Tools > Script editor then on the dialog box that opens select Run > authorize this should valid your twitter account with the spreadsheet. You’ll then be able to run the sheet functions. Of not let me know ;)
      Martin


      1. Hi, I am having the same issue but when I Run > authorize it tells me “Unexpected error: (line 566)”

        Btw it’s very generous of you to create and share tools like this, and to keep replying to poor sobs who ask you for help this far down the line! :) Thanks!


        1. Hi Noah – haven’t been able to conclusively trace the problem but think google occasionally makes a bad copy that doesn’t handle authentication properly. Solution is to make a fresh copy of the spreadsheet and start again. Let me know if still a problem


          1. I had the same issue (566 line error), however when I checked my OAuth Settings I noticed that I forgot to put a proper Callback URL, which should be: https://spreadsheets.google.com/macros

            Once I did that, saved it, and returned to the spreadsheet, I was able to select Tools > Script > Script Editor and then Run > aTest successfully. I also made sure I was signed OUT of Twitter before doing this.

            Hope this helps,
            Jon


  5. Hi – I really like your tutorial. The twitter stuff works well. Have you had success with facebook? I am trying similar approaches but haven’t yet cracked it.
    Thanks
    Luke


    1. Hi Luke – not tried or seen anything yet but it an area I think I’ll be looking at in the next 3 months. If you beat me to it interested in how you do it ;)

      Thanks,
      Martin


  6. Thanks for this!

    Any chance you’ve run into this error message when running the get others followers scripts? And if so, do you know of any resolution?

    Error message: “Exceeded absolute-time timeout in milliseconds”


  7. Yes, they do – 2k+.


    1. hmm that’s probably what’s causing the problem. Need to see if there is a way to break the task up. Have to have a think short that one.

      Thanks for the feedback

      Martin


    2. @charlton and others – I’ve added a function to get lots of friends/followers. If you are already using the spreadsheet more information on upgrading here . The version linked to in this post has been upgraded. Let me know if there are any problems
      Martin


  8. Hi, when I run aTest, I got an error “TypeError: Cannot read property “statuses_count” from undefined. (line 240)”, what is this about?


    1. Hi Ryan – It essentially means that a connection wasn’t successful with the Twitter API (I’ve updated the script to catch this error better). In the Script editor if you run aTest then View > Log what message do you get?
      Thanks,
      Martin


  9. Thank you Martin, I have sorted out, I entered the wrong consumer key. Also, I am wondering if twitter API allows me to extract all tweets from my followers and friends?


  10. So, I’m Brazilian and not quite understand how it works, how to have a basic tutorial to show where I make my login, that holds the account you want followers, and so on. A hug.


  11. Hi, Thanks for the apps. It surely helps a lot. One quick question though, how to add advance option in the spread sheet? Do we just add it in column header?



  12. Awesome! I will give it a shot. Many thanks, going to have my students make use of this in the fall. It will be a great help! One last Q – any chance there’s a way to get friends/followers of more than one person at a time?


    1. @charlton hmm it might be possible the main issue is you only get 350 requests to the twitter API per hour so you will need to do some load balancing (working on another project right now, will come back to this depending on demand)
      martin


  13. Thanks a lot for this! Do you know if there is some easy way to turn this spreadsheet into a gdf-file or some other file that I can read into Gephi?

    All the best,
    Richard


  14. @richard Gephi will accept a CSV input file. Simplest case is two columns, representing node connected to node. If you just open the CSV file in Gephi it will create the nodes and and the edges for you.


  15. Thank you for the response, Tony!

    I will try that. Can I load additional information about each node from a separate file?

    Thank you for a very interesting blog!

    Richard


  16. Awesome tool! I was wondering about exporting data from twitter lists in a similar way. That would be really useful to analyze selected groups of tweeps.


  17. That’s a very interesting tool. However I haven’t been able to download the data as it says you cannot export data from external sources, and a list – also if managed by you – is intended as an external source. Nevertheless that’s a very nice desktop application, ty for sharing!


    1. Didn’t know about the export limitations on SocialBro – thanks for sharing.

      Another tool I’m just about to write about is a MS Excel add-on NodeXL (only available for Windows PC). NodeXL is an advanced network visualisation tool so probably more than you need but it does have a built-in option to get list memberships (one of the Import options) which are saved to a spreadsheet (vertices sheet)

      Martin


  18. I’m afraid I’m a Mac user but I’ll give it a try anyway on a PC. I have to say I’m interested in visualizations too and I’ve been playing with Gephi for several months. Although I’ve a background as a statistician I’m not a coder and one of my main issues has been data export from social networking services. And your blog has revealed to be very useful. Thanks!


    1. @Angelo you’ll like my latest post then on NodeXL – Getting started with the @WiredUK friends network (no coding required ;). It’s a rerun of a Gephi recipe by Tony Hirst. It might be worth firing up the PC just to get the data and then export to Gephi. It is possible to run NodeXL on a virtual machine in Mac – you’ll obviously will need a copy of Win and Office though to do this).

      Thanks!
      Martin


  19. Already read it :) Thank you for your suggestions, that seems a clever workaround


  20. Hi, I love the concept but when I run the aTest I get a pop-up on the spreadsheet that reads: “OOPS – it didn’t work” and when I check the log it reads: “Exception: Unexpected error:” Any suggestions?


  21. This worked without a hiccup! Utterly amazed by the simple approach (using Google Docs) to get out info. I have been using Twitter Ruby Gem (it works too) but this was way simpler for many export tasks. Thanks a bunch.


    1. @John – thanks glad it worked for you. Loads more other stuff you can do with Google Spreadsheets and Apps Script – its a great tool for rapid development ;)


  22. Great work Martin, thank you! Easy to use and runs beautifully. Been looking for something like this for awhile now.

    If you do develop the spreadsheet to include list data as well, just a column saying which lists you have put them in that would be fabulous.

    Cheers,
    Mark.


  23. Thanks Martin, this is amazing. Exactly what I needed. Great work.


    1. Actually, I’ve suddenly got an error saying, “The coordinates or dimensions of the range are invalid. (line 204)” yet I haven’t changed anything in the script. Nothing works. Any ideas?


      1. @Mitch sometimes this error is a temporary fault because Google servers are playing up. One thing you could try is open the Tools > script editor run one of the menu options then check View > log to see if it says anything
        Thanks,
        Martin


  24. Thanks for this.
    However, when trying to authorize the spreadsheet with twitter, I did what you said Script Editor -> Run -> aTest (there was no ‘authorization’ function). I get the message saying ‘Apps Script – Oops. It did not work’. I’ve followed all the other steps and am not sure what is going wrong.
    I am running in Firefox on a Mac.
    Thank you for your help.


    1. @John L I think its due to turbulence on either Google or Twitter servers preventing the spreadsheet to connect properly. Afraid I’ve got no better solution than trying again later
      Martin


      1. I tried it again on a different day and time using Chrome instead of Firefox. Still had the some problem. Oh well.

        Anyway, thanks so much for replying. I really appreciate it.


        1. Hi John and Martin,

          I just had the same problem with the ‘Apps-Script Opps’ message. I finally went to Tools -> Script Editor, Run -> Authorize and then it fixed the problem. I guess I must have missed that in the original instruction.

          Hope that helps for others with the same error message.
          Heather


          1. ps this is absolutely awesome. I just downloaded 23,000 twitter followers of a company I want to analyze – amazing!


  25. Thanks a lot for your great job. I did everything as you explained above, and I have no error message at all.
    Nevertheless I can not find where is my list.
    I might sound stupid, but all I have is a small yellow message saying: “Running script getFollowers”
    I have more than 30K followers, and I guess it will take some time to show the full list, but after 2 hours, there is nothing else but the yellow message.
    Do you think I did something wrong?
    Thank you in advance for your help.
    Alix.


  26. Oh, Now I got the list. This is awesome!
    Thank you very very much!
    :D


  27. Wow, this is brilliant. Thank you so much. I was desperate seeing that tweetake.com was down. Rhis replaces it perfectly.



  28. Whenever I run aTest, I get an error saying, “Oops it didn’t work” or “Opps, Authorisation is required to perform that actions” but it doesn’t give me the option to authorize it.


  29. i don’t see authenicate, just configuration and get follows and friends types of scripts


    1. Hmm there should be two athenicate functions in there (either will work) not sure why you can see other code and not these – usually if its a bad copy none of the code is there


      1. I just deleted and made another copy of your spreadsheet. Still not seeing any athenicate functions.


          1. Yes it is.


          2. Hi,

            The authenticate function is gone.

            I copied the spreadsheet a couple of times and saw same symptoms as Clay Hoffman.

            I reviewed the code of the script and it has no authenticate or similarly named function :-(


          3. Hi – I’ve put a new version of this spreadsheet up. It has improved code for getting the data from Twitter. If there are still problems let me know
            Martin



  30. I see the authorize script now, but when I run it I keep getting “Unexpected error: (line 580) Dismiss.

    This is whats on line 580-585

    var result = UrlFetchApp.fetch(
    “http://api.twitter.com/1/account/verify_credentials.json”,
    requestData);
    var o = Utilities.jsonParse(result.getContentText());
    ScriptProperties.setProperty(“STORED_SCREEN_NAME”, o.screen_name);
    }


      1. Sometimes I think google doesn’t copy the authorisation tokens from twitter properly which leaves you in limbo with a half authenticated script. I’m afraid the only way I’ve found to get around this is to make a fresh copy of the template.
        Martin


  31. This script is great, and I appreciate all the work that you have done.
    I looked through the comments, and it looks like other people aren’t having this issue, so I’ll see if I have something set up incorrectly. But, when I run the “getLotsOfFriendAndFo”, I get an error that says “ReferenceError: “users” is not defined. (line 172)”. Line 172 says: “var chunks = chunk(users,100);”. I am using V2 of the spreadsheet. I have tried on a couple of copies of the sheet. I am also trying to pull in someone elses followers. It works when I use the “get other persons followers” from the twitter menu, but it’s been timing out because of the volume. Any help would be appreciated, although I completly understand that you don’t want to spend a ton of time troubleshooting a free script you made.


    1. Hi Joel – As you asked so nicely I had a look and there was a problem with the script. I’ve updated the code so if you grab a new copy of v2.1 it should work (fingers crossed – and if it doesn’t let me know ;)
      Martin


  32. Martin, for advanced, do I just need to add the field name to a new column in the spreadsheet? I want to add

    follow_request_sent

    to my export. Thanks!


    1. I think I’m actually looking for

      following_request_sent

      (the timestamp for the follow request YOU sent to a friend / someone you want to follow)

      I think follow_request_sent is the timestamp for follow requests from YOUR followers?

      Does the following timestamp exist?


      1. Hi Jonah – you are right in that putting follow_request_sent as one of the headers means that data is populated. As you have probably discovered now this only returns true or false. The data returned by the bit of the API used in this solution unfortunately doesn’t include a following request timestamp (if this data is important to you, you might want to look at the socialbro.com app)
        Martin


  33. Nice stuff, works like a charm!

    In order to visualize the network in gephi I’d like to also include the friends/followers relation among all friend/followers for a Twitter account. (Ie not get the complete list of all their F/F, but only the interconnection between the listed ones)

    Is this something you have looked at?