Monday, September 27, 2010

Using Excel as Twitter client with OAuth

Update July 22, 2012: We have created a Twitter account and associated website to make your Excel experience a lot easier. If you understand VBA but do not want to go through all the oauth hassle, this may be for you.

Many users of twitter have found out that Excel is an excellent client for Twitter, and I am sure that through this post on Chandoo.org, a lot of curious people opened their VBA editor, tried it, and subsequently posted their first automated tweet. For me this was just the beginning, and after studying the Twitter API documentation, I wrote functions for just about all API methods. Using Excel proved to be great for all kinds of tasks, like scheduling tweets, sending tweets for different accounts from one dashboard, archiving tweets, mentions and direct messages, and many more bigger or little tasks.

The reason that it was so simple to use Excel was because of the use of basic authorization where with just a username and password it was possible to “talk” to the Twitter API. Unfortunately Twitter has switched off Basic Auth since the beginning of September, and many Excels sheets that required authenticated requests, will no longer function. Since transitioning from Basic Auth to OAuth looks complicated, many users of Excel sheets may have given up, and accept that their work is now lost. I also started and stopped looking at the documentation a couple of times, until I finally decided it just had to be possible to make it work. And indeed after a couple of days of hard work (and plenty of frustration) I was able to transition to OAuth. This means that I can take all my Excel sheets and just change the VBA code where the API requests are made, leaving everything else intact.

I will explain here how I did it, so hopefully other Excel users can take advantage of my efforts. I will use a straight forward example, and if you have some understanding of VBA, you should be able to follow the process and later take the code and optimize it for your own needs. In the example I will build an Excel sheet that allows me to send a tweet, going through the process step by step. You can see all the VBA code I used in the Excel sheet which you can download for your own programming pleasure.....

Step 1: Register an application with Twitter

There is no way around this. In order to use OAuth you need application codes and user codes. By registering an application with Twitter you will obtain a consumer key and a consumer secret. For your Twitter account you will get an access token and an access token secret. You will need these four codes before you can continue.

Step 2: Get all required data

For our example the following information is required:

API method = POST
API resource = http://api.twitter.com/1/statuses.update.xml
Oauth consumer key = your_consumer_key
Oauth consumer secret = your_consumer_secret
Oauth signature method = HMAC-SHA1
Oauth version = 1.0
Oauth token = your_token
Oauth token secret = your_token_secret

The API method and resource relate to what you want to do, which in this case is to send a tweet. The signature method and version are given as per the current requirements.

Step 3: Get a nonce (number used once) and a timestamp

You can calculate this yourself by writing a couple of simple functions. The timestamp simply calculates the number of seconds passed since the unix epoch which seems to be January 1, 1970. For the nonce I observed in the Twitter examples that it is 41 characters long and only uses letters and numbers. I just created a string which holds all possible and legal characters. Then I randomly picked a character 41 times from this string and put them all together in an output string which becomes the nonce. Twitter seems happy with that solution. A shorter nonce will probably also be ok, as long as it is unique.

Step 4: Calculate the base string

This is where thing started to get ugly, because it is so easy to do it wrong, and it is not so easy at first to figure out what went wrong. The base string is used to calculate a signature which Twitter requires as part of the API request. If you follow the instructions from Twitter exactly, you should get the correct signature. Important is to keep in mind that there are three parts connected with an ampersand (&), where the second and third parts need to be url encoded. While I was testing my output, I found a very useful tool on http://quonos.nl/oauthTester/ to check the base string.

Step 5: Calculate composite signing key

In this case, where you want to send a status you need to create a string, where your oauth_consumer_secret and oauth_token_secret are combined through an ampersand (&). No url encoding is required here.

Step 6: calculate oauth_signature

After mastering the base string, this was the next part which proved to be tricky. First I needed to understand (a little) what a HMAC-SHA1 signature is and than figure out a way to calculate it. Luckily I found all I needed on http://www.cryptosys.net/, which has a library that you can include as a VBA module. Make sure you do the installation, because there is a DLL file required, and please also check the license document.
When I calculated the signature, it did not match the required signature. The issue was that the obtained signature needed to be converted to bytes first, than base64 encoded and subsequently url encoded. I already had the code for the url encoding but did not have a function to perform the other two tasks. I wrote a simple function to convert the output string to bytes, and also found the correct answer for the base64 encoding. When all this finally worked, I had my moment of joy when the signature was exactly identical to the one calculated in the example in the twitter documentation.

Step 7: calculate authorization header

Creating the authorization header is quite similar to creating the base string. No url encoding is done here, and besides reading the Twitter documentation, I also carefully read Appendix A.5. from the Oauth Core 1.0 document.

Step 8: Send the API request

Finally everything has to be brought together in an API request, which look very similar to the one you probably used before, when basic auth was still working. In my Excel sheet it looks like this:

Set xml = CreateObject("MSXML2.XMLHTTP")
xml.Open cApi_method, cApi_resource & "?status=" & cStatus, False
xml.setRequestHeader "Authorization", cHeader
xml.Send
tResult = xml.responsetext
Debug.Print tResult
Set xml = Nothing

And that's all there is to it!

Please note that I my goal was to get to my required end result, which was to send a tweet. I have not attempted to create the most efficient and robust code and process to get there. I am convinced that there are far more talented programmers out there, who will be able to take this code and improve it. Please go ahead and share this with the rest of us.....

You can download the Excel sheet I created here.

32 comments:

  1. Fell at the first hurdle. When registering my spreadsheet with Twitter, I am given Consumer Key and Consumer Secret, but no oauth_token or oauth_token_secret. Where do I go for these?

    ReplyDelete
  2. On the setting page for your application, don't you see a tab on the right which says "My Access Token"? That is where is see the token en secret for my twitter username

    ReplyDelete
  3. Ahhh, I registered using the main site instead of dev.twitter.com. Thx.

    ReplyDelete
  4. I keep getting "Error code=53: Basic authentication is not supported" even though I'm supplying the four key/secret params in the spreadsheet. Any thoughts?

    ReplyDelete
  5. I'm getting same and BlueKaroo



    Basic authentication is not supported

    ReplyDelete
  6. Got it to work!! Thanks so much. I had to change the timestamp to use GMT. I added 6/24 to Now function since I'm in NY and GMT is 6 hours ahead.

    ReplyDelete
  7. I got around the Basic Authentication is not supported by pressing cancel when prompted for user id and password

    ReplyDelete
  8. I'm no VBA person and getting a "Compile error: Can't find project or library" drive me nuts...

    ReplyDelete
  9. Did you download the cryptosys library?

    ReplyDelete
  10. Alec Berg>
    I cannot around the "Basic Authentication is not supported" by pressing cancel when prompted for user id and password.
    I met error message below
    "Invalid authorization header"

    ReplyDelete
  11. you can get another Oauth access realization in excel
    http://www.turingbook.net/home/excelkara-oauthshiyo-u
    and, you can download SHA-1 Library,in Hyperlink Below
    http://the-workflow.googlecode.com/svn/trunk/the-workflow/client/GCryptInstaller.MSI

    http://the-workflow.googlecode.com/svn/trunk/the-workflow/client/Client.xls
    contains GCryptInstaller.MSI's USAGE

    but I cannot twit by my Excel ㅠㅠ

    아오 샹. 미치겠네 ㅋㅋㅋㅋㅋ

    ReplyDelete
  12. Hello
    I'm looking for something like this for my company
    May you contact me? carolouribe at hotmail dot com.
    Thanks

    ReplyDelete
  13. "End of the Night" is a simple romantic Twitter client, based on Microsoft Excel, by Shiodoki. (@shiodoki on Twitter)

    http://translate.google.co.jp/translate?js=n&prev=_t&hl=ja&ie=UTF-8&layout=2&eotf=1&sl=ja&tl=en&u=http%3A%2F%2Fyoruhate.arcanus.info

    ReplyDelete
  14. This comment has been removed by the author.

    ReplyDelete
  15. I can't seem to find out how that works. I'm happy you shared this step-by-step procedure. It helps in a lot of ways.

    long island document scanning

    ReplyDelete
  16. This comment has been removed by the author.

    ReplyDelete
  17. Thanks for this blog, but more particularly to the comment dated January 18, 2011 10:28 AM.

    Your code in
    http://the-workflow.googlecode.com/svn/trunk/the-workflow/client/Client.xls was a great help for me in writing an Excel VBA client for Gmail (scope https://mail.google.com/mail/feed/atom).

    The GCrypt library was also very useful, without the 30 day trial restriction of the Cryptosys library referenced in this blog.

    ReplyDelete
  18. Hi Adrianus,
    first of all, let me thankyou for this great contribution,
    I have downloaded you excel example, and also the CryptoSys library, and instaled in my computer. I'm using MS visual basic 6.2 and Excel 2007.
    The problem is that I still receive the "Compile error: Can't find project or library" message, and I have no idea of what can happend..
    If it can help, in VB "References" sub menu, under "Tools", "idCryptoSys.dll" thosen't appears..

    Any sugestion? Thanks in advance!!!

    ReplyDelete
  19. I am getting the same error: " Can't find project or library", and i am using Excel 2007. Any suggestions will be of great help.

    Thanks,

    ReplyDelete
  20. Is anybody got through from this post to tweet from Excel!

    ReplyDelete
  21. I can't get this to work. I keep getting the 'Could not authenticate with Oauth' error. I think it's the signature but I'm not sure.

    ReplyDelete
  22. Fantastic work, I have to say. I can get your Excel sheet to run fine. I'm trying to port this to MS Access at the moment, and I'm almost there. I get an OAuth failure, but I need to go through a few things regarding how the key is generated. I've had to substitute a function or two and make some declarations. I'm almost there!

    ReplyDelete
  23. Ok - I now have it working from Access VBA. Thanks a million!

    ReplyDelete
    Replies
    1. Great! what are the steps necessary and where is the sample?? :)

      Delete
    2. Can you give a sample of your MS Access aplicatition?

      Delete
    3. Could be useful if you share, i'm trying to make it work for Dropbox API. Any suggestions?

      Delete
  24. Any help on an "Invalid Unicode value in one or more parameters" error from your example?

    ReplyDelete
  25. >Adrianus

    Всё работает, спасибо. :))

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. For anyone finding this article now, there are *several* problems with attempting to emulate its approach. The most significant are:

    1) The base 64 encoding described... doesn't appear to have registered that function.
    2) Twitter has deprecated the 1.0 REST API, and now relies on 1.1.
    3) The timestamp must be encoded to UTC (as an earlier post indicates. If you're on local, add a UTC Offset times 3600 and add that to the timestamp.
    4) *Most damning*, XML is no longer supported by Twitter. Only json is.

    Thanks for an informative post, but you really should come back, edit it and mention that this system is so deprecated it is unusable as a programming example. Would have saved me many hours.

    ReplyDelete
  28. API resource = http://api.twitter.com/1.1/statuses.update.json

    ReplyDelete