SharePoint as a Twitter Client. Sort of – Part Five

In part four I mentioned that there would be a problem in the future with this solution.

In the second part of this series we created a simple function that generated a HTTP request to send to the Twitter API.

As part of that request we sent a username and password.

This known as “basic authentication”

Unfortunately just after I got this working the Twitter development team announced on google groups they are dropping support for basic authentication.

our plan is to turn off basic authorization on the API by june 30, 2010 — developers will have to switch over to OAuth by that time.  between now and then, there will be a *lot* of information coming along with tips on how to use OAuth Echo, xAuth, etc.  we really want to make this transition as easy as we can
for everybody.

Twitter even created a site called “Countdown to oAuth”

I can understand why Twitter have done this as it’ll make the system far more secure.

Unfortunately this breaks my solution so I’ll need to take a look to see how i can use a different authentication method.

I’ve got till the end of June so hopefully I’ll get something sorted soon!

Just one final post on the way to wrap this up.

SharePoint as a Twitter Client. Sort of – Part Four

In part three I said there was bit of functionality left to clear up.

We have to run the process manually.

We actually did a little bit of the ground work in part two.

One of the pieces of code was the main function that brings all the bits and pieces together – it was called “Fetch_Tweets”

Public Function Fetch_Tweets() As String

Dim strID As String
Dim strXML As String

If Get_Settings = False Then
    Debug.Print "Couldn’t get settings"
    End
End If

strID = Get_Last_Tweet_ID
strXML = Get_Latest_Tweets(strScreenName, strUserName, strPassword, strID)

SaveTmpXML strXML

Application.ImportXML strTempFile, acAppendData

Tidy_User_table

End Function

Take a look at the first word of this code block.

PUBLIC

By adding this we can reference the function outside of Access so I put together a vbscript to run the function. (essentially…programmer types will argue this isn’t what Public means but for the layman it’ll do here!)

Dim TweetDB

TweetDB = "C:\TweetPoint\Twitter.ACCDB"

Dim myAccess
Set myAccess = CreateObject("Access.Application")

myAccess.OpenCurrentDatabase TweetDB, False
myAccess.AutomationSecurity = msoAutomationSecurityForceDisable

myAccess.Run("Fetch_Tweets")

myAccess.CloseCurrentDatabase
myAccess.Quit acQuitSaveNone
Set myAccess = Nothing

So just breaking this down.

The vbscript is essentially doing exactly the same as if you were running this manually.

First we create an instance of Access. (The same as opening the application)

Then we open the database we need.

Then we tell Access to run our “Fetch_Tweets” function.

The we close the database and close the application.

Simple!

The only caveat here is macro security. If you have your security in Access set to high or prompt then the vbscript won’t work.

Since I’m just doing this a proof of concept I turned the macro security off.

You’ll need to make your own decision about how to deal with this.

One suggestion though is to look at something called “Trusted Locations”

This article explains how to set that up.

We can then take this vbscript and run it as a scheduled task to run as often as you need.

The major downside to this is that the system that will run the vbscript will need to have Access installed.

I don’t think the Access runtime would be enough (I only tried it very briefly)

So that’s all of our basic functionality problems sorted

We can ask Twitter to return the tweets for a given person, have them displayed in a SharePoint list automatically and it will be intelligent enough to not give us duplicates.

I’ll follow this up with a couple of posts that shows how to add a little more polish and cover a problem that I’ll need to look at going forward. I haven’t figured out what to do about it yet though!

SharePoint as a Twitter Client. Sort of – Part Three

Following on from part two I commented that there were a couple of issues outstanding,

1. It doesn’t automatically update the SharePoint list. We have to repeat the list creation process every time.
2. We have to run the process manually. 

This post will cover the first item on the list and is really straight forward.

Go back to the first post and make sure that you have created your list on your SharePoint site by exporting it. (Go now, I’ll wait).

Once it’s done in the Access Database delete the “status” table (or rename it if you want to play it safe).

Then from the “External Data” tab select the option to “Import SharePoint List”.

The wizard will start – enter your site name and select the option.

“Link to the data source by creating a linked table”.

Click Next to display the lists you can import.

Choose the list (I’ve called mine “TweetPoint” – think it’ll catch on?)

Click OK to link the list.

You’ll then see the SharePoint list alongside the other Access tables.

User Information List is also linked as this holds user information which will be entered onto the list for things such as the “Created by” field.

Now the clever bit to hook it up to our Twitter API calls.

Bear with me because this is really complex

Right click on your newly imported list and select the rename option

Change the name to “status”

Done! (ok I may have exaggerated how difficult this was)

So what’s actually happened here?

We’ve changed the name that Access uses to reference the table.

In the background it’s still hooked up to SharePoint via it’s URL so any changes we make on the table will automatically be updated on the SharePoint side!

The import process we created in the first post is expecting to append the data to a table called status (since that is what the Twitter API returns) so that is exactly what we’ve provided!

Only one thing left to clear up in the next post!

SharePoint as a Twitter Client. Sort of – Part Two

I intended to follow on from part one a bit sooner but it’s been really busy at the office!

In part one I gave a brief overview of what I was trying to accomplish and how to get a rough prototype working.

While it did the job I highlighted the following limitations.

1. It only fetches the last twenty updates – if there have been twenty five updates since you last did it then you’re out of luck.
2. Conversely, if there have only been a few updates it’ll still fetch the last twenty giving you duplicate items.
3. There is also the problem of needing to run this manually. 
4. It doesn’t automatically update the SharePoint list. We have to repeat the list creation process every time.
5. Finally, it doesn’t deal with protected Twitter accounts at all!

This post will deal with the items 1, 2 and 5 and is actually pretty easy.

We’re going to use a bit of VBA to make Access a little smarter. I’m not a programmer/developer so if the code looks silly or inefficient….there is a good reason for it!

Also you’ll notice a distinct lack of error handling. This is just an exercise to see if it would work so code is kept to a minimum!

The Twitter API method I used in the previous post had a parameter where you can specify a tweet ID and the API will return all tweets since then.

http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=AndyParkes&since_id=13081868261

So the only difficulty now is actually figuring out what tweet ID to use.

If only we had some sort of list we could query to do this.

Again this is really easy as we’re using Access which is built for the job!

Here’s the bit of SQL I crafted

"SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"

I’m using the VAL function to convert the tweet ID to a number as I had some weird sorting issues.

I wrapped this is in a VBA function called “Get_Last_Tweet_ID”

Function Get_Last_Tweet_ID() As String
Dim db As Database
Dim strSql As String
Dim rstData As DAO.Recordset
Dim strID As String

Set db = CurrentDb()
strSql = "SELECT TOP 1 VAL(status.id) as TwitID FROM status ORDER BY VAL(status.id) DESC;"

Set rstData = db.OpenRecordset(strSql)

    If rstData.EOF = False Then
        strID = rstData("TwitID")
    Else
        strID = ""
    End If

    rstData.Close
    Set rstData = Nothing

Get_Last_Tweet_ID = strID

End Function

So this function will simply return a tweet ID we can plug into the API call and it’ll only return the most recent tweets. If the function returns an empty string we just don’t bother to run the API call.

The next question you’re probably asking is HOW do you run the API call?

Remember the wizard we ran in the last post to import the XML? All that wizard does is use a VBA function called ImportXML

The syntax is simple

Application.ImportXML(DataSource, ImportOptions)

The parameters are the same options asked for by the wizard.

A data source and whether you want to import the data, the structure or both.

That’s the first part – but since I want to be able to take protected accounts into consideration we can’t just drop a URL straight in as the data source (where would you put your username and password!)

So we’ll just fashion the request ourselves.

We’re sending a request to a web service to get response back with some XML so the object we’ll use is XMLHTTP and it will send a GET request to the Twitter API which should throw back a chunk of XML.

Function Get_Latest_Tweets(strScreenName As String, strUser As String, strPassword As String, strLastID As String) As String

Dim myXML As MSXML2.XMLHTTP
Set myXML = New MSXML2.XMLHTTP

If strLastID = "" Then

    myXML.Open "GET", "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=" & strScreenName, "False",     strUser, strPassword
Else

    myXML.Open "GET", "http://api.twitter.com/1/statuses/user_timeline.xml?screen_name=" & strScreenName &     "&since_id=" & strLastID, "False", strUser, strPassword
End If

myXML.send

Get_Latest_Tweets = myXML.responseText

End Function

Note: Depending on what you have on your machine you may need to use “Microsoft.XMLHTTP” instead.

This function takes a couple of parameters, the ScreenName of the Twitter account to retrieve, a username and password for authentication and a Tweet ID.

We check to see if we passed an ID. If not then don’t use the “since_id” parameter and just fetch the last 20 tweets (the default). We could add an extra parameter to fetch a specific number of tweets.

We then send the response and the function returns a string containing some XML.

Now that we have our XML from Twitter what now?

Since the ImportXML wizard requires a file to work with we need to save the XML to disk.

Sub SaveTmpXML(strXML As String)
     ‘ Load the XML
     Dim xmlDoc
     Set xmlDoc = CreateObject("Microsoft.XMLDOM")
     xmlDoc.async = "false"
     xmlDoc.loadXML strXML
    xmlDoc.Save strTempFile
End Sub

This is a really simple procedure. We pass the XML to it, create an XMLDOM object, load the XML string and save it disk. I used a global variable for the filename so I can specify a temp path in an options table which I’ll cover later.

Finally we can import the XML into our table,

Public Function Fetch_Tweets() As String

Dim strID As String
Dim strXML As String

If Get_Settings = False Then
    Debug.Print "Couldn’t get settings"
    End
End If

strID = Get_Last_Tweet_ID
strXML = Get_Latest_Tweets(strScreenName, strUserName, strPassword, strID)

SaveTmpXML strXML

Application.ImportXML strTempFile, acAppendData

Tidy_User_table

End Function

This a wrapper function that calls all the code I’ve outlined above.

We find the last Tweet ID, fetch the tweets from Twitter, save them to an XML file and finally import them into our table.

You’ll notice the Get_Settings and Tidy_User_Table procedure calls, I’ll also cover those in a later post.

You could hook this wrapper function up to a button on a form. Once clicked it would update the table in the database with the latest tweets.

I think that’s all three issues solved. We now only fetch exactly the tweets we need and can work easily with protected tweets (Twitter recently announced a change that will break this but I’ll cover that in an upcoming post)

I’ll get part three together a bit sooner!

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.

Access 2010 screenshot – procedure is similar in Access 2007 though

Enter your API URL into the file name field. The URL I’m using will return my last 20 tweets

You’ll be prompted to verify the import

Choose structure and data first time round so you actually create the tables

On subsequent runs you can just append the data

Done…you can save the import steps if you want so you can repeat this without going through the whole wizard again.

I’ve now got my last 20 tweets into an Access database.

You’ll notice there are actually two tables created.

Status contains the tweets.

User contains the details about the user this is being pulled from –it’s part of the XML returned.

So to get it into SharePoint.

Select the status table, click the External Data tab again this time use the Export section and select SharePoint list.

Run through the export wizard.

Choose a destination site.

You’ll see it creating the tables.

Done!

You can then browse to your site and view the list!

You can also create a view to just show the important fields.

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!

I’m on twitter

I’ve finally succumbed and signed up for Twitter (@AndyParkes)

I still don’t quite get it just yet but then again I didn’t get Flickr or Facebook to begin with either so thought it wouldn’t hurt to give it a go

Come say hi (or whatever it is you do there   )