Export Twitter Followers and Friends using a Google Spreadsheet

Published on March 8, 2011 at 1:57 pm in Google, Google Apps, Google Apps Script, Google Spreadsheet and Twitter. 153 Comments By Martin Hawksey
Share this post on:
| Facebook | Twitter | LinkedIn

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

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

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

function getFriendAndFo(sheetName){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  sheet.getRange(2, 1, sheet.getLastRow(), sheet.getMaxColumns()).clear({contentsOnly:true}); //clear sheet
  var cursor = "-1";
  while(cursor != "none"){ // while twitter returns data loop
    try {
      var o = tw_request("GET", "statuses/"+sheetName+".json?cursor="+cursor); // note using sheetname to build api request
      var data = o.users;
      for (i in data){ // extracting some subobjects to top level (makes it easier to setRowsData)
        if (data[i].status){
          for (j in data[i].status){
            data[i]["status_"+j] = data[i].status[j];
          }
        }
        if (data[i].screen_name){ // also build url to jump to profile page
          data[i]["profile_link"] = "http://twitter.com/"+data[i].screen_name;
        }
      }
      var headRange = sheet.getRange(1, 1, 1, sheet.getMaxColumns());
      var rowIndex = sheet.getLastRow()+1;
      setRowsData(sheet, data, headRange, rowIndex); // dump data for this loop to sheet
      if (o.next_cursor!="0"){
        cursor = o.next_cursor; // get next cursor
      } else {
        cursor = "none"; // break
      }
    }  catch (e) {
      Logger.log(e);
    }

  }
}

Last updated by at .

153 Responses to “Export Twitter Followers and Friends using a Google Spreadsheet”

Feed for this Entry Trackback Address

Leave a Reply

About

This blog is authored by Martin Hawksey Google+

JISC CETIS Learning Technology Advisor (OER Programme Support)
jisc cetis logo

The MASHezine (tabloid)

It's back! A tabloid edition of the latest posts in PDF format (complete with QR Codes). Click here to view the MASHezine

Preview powered by:
Bluga.net Webthumb

The MASHebook

You can also download this post as:

Subscribe to monthly email digest of posts

Loading...Loading...


Subscribe to per post email updates

Enter your email address:

Delivered by FeedBurner

Copyright License

Creative Commons Licence
This work is licensed under a Creative Commons Attribution 3.0 Unported License. CC-BY mhawksey

Privacy /Cookies

This blog uses Google Analytics (which makes use of 'cookie' technologies) to provide information on usage. Here's an overview of Google Analytics Privacy and how to opt-out (other 3rd party services like Twitter might also be tracking you via this site, but as far as possible I try and prevent this by removing official tweet buttons).

Badges

. . .