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 .
Loading...
Awesome. Used Ur link @ the top and it worked perfectly. Thanks!
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!
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?
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
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?
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
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!
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
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
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
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
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”
Does the other person have a lot of followers?
Martin
Yes, they do – 2k+.
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
@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
Hi, when I run aTest, I got an error “TypeError: Cannot read property “statuses_count” from undefined. (line 240)”, what is this about?
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
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?
Hi Ryan – glad it worked out. As it happens I have another sheet for capturing peoples tweets (you can use the same API keys you’ve already got). I think the Twitter API limits the return to the last 3400 tweets.
Enjoy,
Martin
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.
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?
Yes adding the advance column headers automatically pulls extra info
Martin
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?
@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
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
@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.
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
@Richard tony has lots of gephi recipes your answer might be in http://blog.ouseful.info/tag/gephi/
Martin
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.
@Angelo definitely possible (I’ve already done some work populating lists through spreadsheets ). While I take my time to get around this you might want to check out SocialBro which is able to do this.
Martin
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!
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
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!
@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
Already read it :) Thank you for your suggestions, that seems a clever workaround
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?
@R Wood did you authenticate the spreadsheet with Twitter okay?
Martin
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.
@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 ;)
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.
Thanks Martin, this is amazing. Exactly what I needed. Great work.
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?
@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
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.
@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
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.
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
ps this is absolutely awesome. I just downloaded 23,000 twitter followers of a company I want to analyze – amazing!
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.
Oh, Now I got the list. This is awesome!
Thank you very very much!
:D
Wow, this is brilliant. Thank you so much. I was desperate seeing that tweetake.com was down. Rhis replaces it perfectly.
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.
Have you done the ‘open tools > script editor and run authenicate’ bit?
i don’t see authenicate, just configuration and get follows and friends types of scripts
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
I just deleted and made another copy of your spreadsheet. Still not seeing any athenicate functions.
Is it the same problem if you run aTest in the new spreadsheet as well?
Yes it is.
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 :-(
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
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);
}
Me too :-)
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
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.
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
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!
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?
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
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?
Hi Johan, I do have a script which constructs friend follow relationships but its very restricted in how many relationships it can manage (e.g. ignores relationships over 5000 and can only manage base networks of 300). What I’ve done in the past is use NodeXL. The import via twitter list lets you paste a list of username you want to get relationships for and generates an edge list, which can either be analysed further in NodeXL or exported to gephi)
Martin
it is no more working.
Hi Martin, how are you?
I configured everything according to the steps and I have no error message at all, however, can not download my complete list of followers.
After all set performing “getFollowers” and showed that first run in the bottom right a message 100 to 200 of 148000, but when he arrived in 4900 to 5000 of 148000 the system kept running and not download anything. I redid the process a few times and still managed to download about 26,000, but 122,000 still missing. I did something wrong? I left to do something? What do I need to get the 148,000?
But without doubt the tool is fantastic!
Hi Almir, this tool is mainly geared around a 5000 follower max. There is a supplemental script in Tools > Script editor … called getMoreFriendsAndFollowers which can get more but is limited by the google spreadsheet maximum of 400,000 cells (with the number of columns used its around 40,000 twitter account details
Martin, thanks for the help. I looked in Tools -> Script Editor -> getMoreFriendsAndFollowers, but I found this script, it does not exist for me, at least by that name. For me there and getAnotherFollowers getLotsOfFriendAndFo. It is one of these two?
Sorry don’t know my own scripts ;) yes it’s getLotsOfFriendAndFo
Hahahaha … okay! It happens. So, I tried that too, but does not return all my followers. He lowers 5000 and the first stop. Then re-run the scrip getFollowers and returns the message “Oops!
Exceeded maximum execution time. “Then I ran the scrip getLotsOfFriendAndFo you said, but nothing happens. What is the procedure? I’m sorry to bother.
To use this new code you need to enter the following information directly into the script editor where prompted in the getLotsOfFriendAndFo function:
sheetName – sheet name you want to import the data to;
friendOrFo – whether you want ‘friends’ or ‘followers’; and
optScreenName – the screen name of the person
Hi Martin, how are you? Happy 2012!
Man, I’m picking up the same child to extract all the data. For this tool will be to extract that really gives my 150,000 followers?
I was downloading the data and I returned the message limitation 400,000 cells from there I created a new worksheet and the worksheet name added to the scripts and getFollowers getLotsOfFriendAndFo script editor and yet the same message appears.
Any other alternative?
Hugs.
Hi when you reach the 400k limit make a copy of you original spreadsheet then remove the existing imported data and continue running the getmorefriendandfo script (you can rejoin the data by downloading as .csv and pasting together in Excel)
And clearSheet use the function to remove the data?
I just do what you said.
Step 1: I ran getFollowers the first time and fell 5000 followers
Step2: getFollowers ran a second time and fell over 5000 followers
Step3: In the third execution getFollowers returned the message had reached the limit of 400k
Step4: I made a copy of the worksheet and then it performed the function clearSheet to erase the data that had previously downloaded
Step 5: Ran getLotsOfFriendAndFo, however, limit 400k message back to present, once again ran the function clearSheet and yet the message continued … ?
I just do what you said.
Step 1: I ran getFollowers the first time and fell 5000 followers
Step2: getFollowers ran a second time and fell over 5000 followers
Step3: In the third execution getFollowers returned the message had reached the limit of 400k
Step4: I made a copy of the worksheet and then it performed the function clearSheet to erase the data that had previously downloaded
Step 5: Ran getLotsOfFriendAndFo, however, limit 400k message back to present, once again ran the function clearSheet and yet the message continued … ?
Guessing Google doesn’t realise the sheet has been emptied. When you get to step 5 and get the 400k message close the spreadsheet and reopen it – getLotsOfFriendAndFo should remember where it was up to
Martin, could extract here. Thanks again for your help! The tool is amazing!
Where are you from? And it works with what?
Hugs!
Hi Martin,
It is not working for me. Getting error “Oops! exceeded time”
Also, can you please tell how to export it in excel
Thanks,
Sarath
Howdy Martin,
First off, WOW! This is amazing.
I’m running into one problem:
I’m using the “Get other persons or followers” down the Twitter drop-down menu and it queries about 3400 users then returns this error:
Oops
The coordinates or dimensions of the range are invalid. (line 323)
Thoughts?
So it appears the method for creating new sheets in Google Spreadsheets is currently borked, so I’ve come up with a workaround that fixes it. Use the original link at the beginning of this post to get version 2.1.2
I am also trying to download more than 5000. Everything seems to work except it keeps downloading the same set of 5000 over and over again. I rerun GetLotsOfFriendsAndFo and just keeps downloading the same batch. Hmm. Any suggestions.
Hmm not sure why this is happening. Important thing is to run getLotsOfFriendAndFo() from the script editor window. if you don’t this might send you into a loop
This is one of the neatest uses I’ve seen on Google Docs. I was about to sit down to rite API calls to pull lists of followers for a bunch of Twitter accounts, and instead came across this spreadsheet
Great work!
David
Thanks David (I take it it’s working okay for you – other people are having problems?)
Martin
Hi,
Please if this worked for you can you tell me how I can lay my hands on this with detailed instructions on how to make use of this?
I need to get a list of all my followers for my two accounts.
Thanks
Check out socialbro.com Think they have export options
Hello
This is probably a silly question but when I try to get other person’s followers I get ‘This users account is protected. Data is not available’ however the Tweets are not protected. I’m guessing that I am missing something obvious. Please could you help me?
Thanks for a great post – Very useful.
Karen
Hi Karen – not you being stupid but me. There was a bug in a fix I made. If you make a fresh copy of version 2.1.2 (fingers crossed) it should work.
Martin