Professional Geek
RSS icon Email icon Bullet (black)
  • 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”

    image

    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”.

    image

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

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

    image

    Click Next to display the lists you can import.

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

    image

    Click OK to link the list.

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

    image

    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

    image

    Change the name to “status”

    image

    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!

  • AMITPRO Invades Microsoft Briefing

    Bank holiday always throws me so this is sneaking up on me quite quickly.

    This Thursday, (6th May), Microsoft are holding a regional Partner Business Briefing in Birmingham to promote the upcoming changes to the Microsoft Partner Network, showcase the current wave of Microsoft products and facilitate inter-partner collaboration (we promoted the event on the AMITPRO site).

    As part of this, a number of partners have been invited to speak briefly around a variety of topics with partner collaboration and growth as a central theme.

    It’s no coincidence a big chunk of those are AMITPRO members since working together and sharing ideas is one of the main reasons we’re all a part of the group.

    I’m fortunate enough to be one of those asked to speak so if you’re going to be there leave a comment as I’d love to hear from you and I’ll introduce you to the AMITPRO crowd!

  • 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!