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!