Can you help keep my newspaper Twitter account data up to date?

Update: I think the Google Docs idea is a non starter. The Twittercounter API DOES have a 100 calls an hour limit. Google Docs has a 50 functions limit. I think I need some sort of PHP solution, and possibly the full Twitter API (as there are 130+ newspaper Twitter a/cs, which is > 100. So, anyone know PHP and MySQL well enough to help me?!? I’m currently thinking of rentacoder.com …

I recently counted all the official national newspaper twitter accounts. It took a long time. So I want the figures to update automatically. Here’s what I’ve done so far. Can you help me do it better?

What I’ve done so far …

Thanks to some tips from @UncleWilco of Reader sheds, I’ve set up a google spreadsheet. Column G has the name of the Twitter account. Column H contains this formula (with the username replaced with the relevant one, via the magic of Excel):

=importxml(“http://twittercounter.com/api/?username=GuardianTech&output=xml&results=3″,”//followers_current”)

This goes off to TwitterCounter.com and gets an XML document back for the relevant user, and extracts the figure for <followers_current> and displays it. Amazing! Thanks to labnol.org for the instructions.

Then the spreadsheet takes the data, which is stored in columns G and H, and autosorts it in column A and B, so the accounts stay in numerical order.

Why this doesn’t work so well

The problems I have are:

• The big one The Google spreadsheet will only contain 50 XML imports. But there are 130+ official national newspaper twitter accounts.
• API limit? I’m not sure if there’s a limit on calls via the twittercounter API to get the data. Maybe there’s not so I don’t need to worry.
• Annoying footnote A footnote appears in square brackets to show the source XML. I’d like this not to be here as it makes the numbers hard to scan.That problem vanished when I set a number format!
• SEO! I’d kind of like this to run on my blog rather than just sit on Google. Obviously I can use an iframe. But I’d rather it sat here in an HTML table. Would PHP be a better idea …?

So, can you make this better? As it is, I’ll have to check it occasionally to make sure that one of out of the top 50 isn’t missing. And it’s a bit annoying only being able to see 50.

You might also like

1. National newspaper Twitter account growth gets ever slower …
2. UK newspaper Twitter followers: 1 August
3. Growth of Newspaper Twitter accounts running out of steam
4. Newspapers on Twitter – how the Guardian, FT and Times are winning
5. UK newspaper twitter followers up 17% from August to September

Leave a comment!