Update: due to changes made by Twitter, this script does not work at the moment. We have published a new version that is now included in our Supermetrics Functions product.
Our project of the day: creating a solution for fetching a list of tweets by keyword from Twitter to Excel. There are lots of Twitter statistics apps out there, but it’s quite nice being able to fetch tweets to Excel, as there you can conveniently do all kinds of additional analysis like sentiment scoring, either manually or with automation, and control all the steps in the analysis process yourself. There you can also merge the results with data from other sources, for instance into a web analytics dashboard created with my Google Analytics functions.
There’s an example workbook where the getTweets function is used available for download here. I put a few Finnish brands there as keywords, change those and it will fetch new tweets. On the second sheet, there’s another example where the optional parameters of the function are used.
There’s also a Google Docs version of the functions – you can try it out here (it isn’t as stable as the Excel version though – every now and then it returns an error complaining about too many queries). You can also add it to your document through the Google Docs script library via Tools: Scripts: Insert: search for “tweets”. UPDATE: Google Docs version now also includes Tweet URL.
The functions work in Windows Excel 2003 and later – macros need to be allowed in Excel’s security settings (after changing the setting, you may need to restart Excel). The functions also work in other MS Office apps like Powerpoint and Word – I’ll post an example file of that kind of use later when I find the time.
The parameters for the function are:
- searchTerm (required): word or words to be searched for, search operators can be used
- maxResults (optional): by default, the function fetches 100 tweets, but this can lowered or increased up to 1500
- includeMetaData (optional): whether to include tweet date and author – by default, this is set as FALSE, so only the tweet content is fetched
- includeHeaders (optional): whether to include column headers
- languageCode (optional): can be used to restrict the query to tweets in a certain language – to do this, type the two-letter 639-1 language code from this list
There are all kinds of possibilities for analyzing and presenting Twitter data in Excel and other MS Office apps – please contact me at firstname.lastname@example.org if you’d like my help for creating this type of solution for you.
There’s also a getTweetTrend function in the file you can test, it counts how many times the keyword is mentioned per day. However, I’m not too happy with that function, since it’s really slow and since the Twitter API only seems to allow fetching seven days of tweets (and even less in the case of popular search terms).