Managing long Twitter lists can be a pain, especially when you need to manually check who is or is not a member of a particular list. @JuliaBramble, a social media manager, mentioned this as a particular problem when trying to organise a client’s followers and I thought I could put together something using Google Docs and the Twitter API to solve the problem. As a result, with a bit of setup, you can use the tool below to help manage twitter membership lists.
This article and tool could not have been put together without information and code from @mhawksey and his many great articles on Google Docs and Twitter API integration: http://mashe.hawksey.info/2011/03/export-twitter-followers/
If you’re so excited that you can’t wait to get your hands on the tool (or already know about Consumer Keys and APIs) you can get hold of the Google Docs spreadsheet here (http://bit.ly/twitterlistmanage) – remember to make a copy and follow the instructions.
Managing Twitter lists manually – the problem
Well, it’s something like this:
Of, course, the problem applies equally to Twitter friends as well as followers.
So how does this tool help manage lists?
In short, it shows you a breakdown of which follower (or friend) is in which list.
It also works with any user, though it only shows membership of private lists for the ‘authorised user’ (more about that in a moment). Here’s an example of the kind of output you get:
- Make a copy of the google spreadsheet that you can find here: http://bit.ly/twitterlistmanage (File > Make a copy…)
- Register for an API key with Twitter at http://dev.twitter.com/apps/new (if you’ve already registered a Google Spreadsheet/Twitter mashup you can reuse your existing Consumer Key/Consumer Secret).
In the form these are the important bits:
- Application Website = anything you like
- Application Type = Browser
- Callback URL = https://spreadsheets.google.com/macros
- Default Access type = you only need Read-only for the list manager to work
- Once finished filling in the form and accepting Twitter’s terms and conditions you’ll see a summary page which includes a Consumer Key and Consumer Secret. Take a copy of these as you’ll need them later.
- In this spreadsheet from the menu, select Twitter > Configure. You may need to run this twice, the first time to authorise the script.
- In the Configure Spreadsheet box enter your Consumer Key/Consumer Secret saved from step 3. Be careful not to add any spaces at the end of the values you’re entering.
- Select Tools > Scripts > Script Manager … and from the Script Manager window select authorize (it should be the first entry in the list) then click Run.
- This should bring up an authorisation message box, click on Authorize.
- In the Twitter authorisation window enter your login details if required and select ‘Allow’. Note, if you want to manage private lists, make sure you log in here with the Twitter user for whom you wish to manage private lists (otherwise you’ll only be able to work on public lists until you go through the steps here)
- Close the Script Manager window then select Twitter > Verify Twitter auth in the menu of the main spreadsheet. You should get a popup indicating that you have successfully connected to Twitter
- Now select Twitter > Get Lists from the menu, enter the twitter user for which you wish to retrieve lists. The results should appear in the Lists sheet along with some information like member count, public/private status
- Now select the ‘followers’ sheet and then select Twitter > Get Friends or Followers from the menu and enter the user for whom you wish to get the followers/friends. (Note the name you enter here can be different to the name you used in step 10.) After a moment, the list of followers should appear in column A and a ‘TRUE’ against which lists they’re members of. (You may have to copy the formulas for the TRUE calculation if you have many followers)
- You can repeat step 11 with the ‘friends’ tab for friends.
- 13. If you start seeing odd things, or no results, select Twitter > Check rate limit from the menu to check whether you have gone over the Twitter API rate limit (350/hour). The popup shows you how many calls you have remaining and when that count will be reset: wait till the reset time to try again (note the time shown may be GMT rather than BST)
- The easiest is to make a copy of the spreadsheet and run through steps 1-13 above, or
- To re-use the same spreadsheet, the account you used authorised in step 8, log into the Twitter web application then go to Settings. Under Applications you should see your app listed, simply revoke access and go through steps 6 onwards again.