SharePoint as a Twitter Client. Sort of – Part One

This is going to be quite lengthy so I’ll split into a couple of posts.

Back in March, Mark Wilson of MarkWilson.co.uk (if you’re not reading his blog….go check it out now!) asked the following on Twitter.

Does anyone know how to read XML (i.e. Twitter updates) into a SharePoint List (not the XML webpart reading it dynamically)?

Since I like a challenge I decided to take a look at it.

If you search (using your favourite search engine) for Twitter and SharePoint you’ll find one of two things.

Either, examples of how to use a webpart to grab the RSS feed of someone’s Twitter status or people creating a Twitter-like application using SharePoint (which is pretty cool anyway).

What Mark was looking for was something slightly different.

He wanted to collect posts from a specific user into a SharePoint list. In effect creating an archive that a team could view. They could even use SharePoint to create views to look for keywords.

This meant the webpart wouldn’t do as it only shows a snapshot of the current status updates. If the particular user tweets quite a lot you could miss stuff.

Mark initially pointed me in the direction of the RSS feeds for each user (here’s mine) but this gives us a similar problem to the webpart as it only shows the last 20 tweets.

This was when I stepped out of my comfort zone and started looking at the Twitter API 

It turns out the Twitter API isn’t actually very complicated. All you need to do is craft a URL to pass certain parameters and it’ll throw some XML back at you.

For example,

This url will retrieve an XML file containing my last five tweets.

http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=AndyParkes&count=5 (you can click this even if you don’t have a Twitter account. Authentication is only needed for protected accounts)

So I’d now found a way of grabbing the tweets…but how to get them into a SharePoint list?

IF I were a SharePoint developer armed with a copy of Visual Studio I actually don’t think this would be all that difficult to do. (you’ll see why in one of the later parts to this post)

However, since I’m not I need to make use of the tools at my disposal.

I had to think of something that can make use of XML but also talk to SharePoint and the answer is….

Microsoft Access

Yep..seriously.

Access has the ability to import XML files into a table.

Access also has the ability to link to a SharePoint list and update it.

By putting these two bits of functionality together I was actually able to reach the desired effect.

You can try it yourself.

Open Access and create a new blank database.

Click the External Data tab and choose XML file.

image

Access

 

TaDa!……well not quite.

Currently it’s not very dynamic.

All it does is fetch the last 20 updates. If you re-ran the process above again you’d run into problems doing the SharePoint export as the status table already exists you’d end up with multiple lists (eg. status, status_1, status,2)

Also it’s not smart enough to realise that if we already have some tweets in the database we don’t want to re-add those. If you followed the procedure above for a second time you’d also end up with duplicate tweets.

Finally if you tried to put them into chronological order you may notice the sort doesn’t quite work under certain circumstances.

So in the next part we’ll put Access to work to make this a bit more intelligent and finally automate the whole thing so it’ll fetch the tweets all by itself!

The following two tabs change content below.
Andy Parkes is Technical Director at Coventry based IT support company IBIT Solutions. Formerly, coordinator of AMITPRO and Microsoft Partner Area Lead for 2012-2013. He also isn't a fan of describing himself in the third person.

Latest posts by Andy Parkes (see all)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.