Fetching tweets to Excel/Word/PPT/Google Docs

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 mikael.thuneberg@gmail.com 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).

17 Responses to “Fetching tweets to Excel/Word/PPT/Google Docs”

  1. Barry

    Love this.. Thank you! Is there a way for the function to monitor for new tweets?

    Reply
  2. Mikael Thuneberg

    Hi Barry,

    I don’t think it’s currently possible to get it to update automatically. You can for example retype the search term to get it to fetch the new tweets – or you can insert a button on the sheet that will do this, to make it easier.

    Reply
  3. Dolores Clean

    Thanks, Good information, and thanks for sharing. If I wanted to pull an url from the tweet, would the approach be parsing text from the column, or further can it be done through the gettweet ?

    Reply
  4. Mikael Thuneberg

    Hi Dolores,

    You can either parse the text with functions in the Excel sheet, or do a small modification to the VBA code that would return just the URL to the cell – the latter would be a more convenient solution but you need some VBA skills to get it working.

    Reply
  5. zavvi

    Thanks this is great.. Is there anyway to make it return tweets for another date apart from today?

    Reply
  6. Anonymous

    I know your function does not require authentication. Do you know how to handle OAuth now required by Twitter to authenticate, still using VBA?

    Reply
  7. Mikael Thuneberg

    Hi zavvi,

    If I remember correctly, the API only allows fetching the latest 1500 tweets, or tweets from the last seven days. So if your keywords matches 1500 tweets posted today, then it’s not possible to fetch other days.

    Reply
  8. tushar

    Hi

    it would be very helpful if you can build up a google doc template/ or excel script oor twitoaster conversation api as well. As that can be directly put for semantic analysis.

    Reply
  9. Piru

    This is amazing. Only thing, would be good to fetch tweets from a specific date, rather than the latest ones only. Welldone anyway.

    Reply

Leave a Comment to zavvi

  • (will not be published)