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
tResult = xml.responsetext
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.