Backup Twitter Status Updates to a Google Spreadsheet

It looks like Twitter are finally rolling out the option to download all your tweets. As well as providing a nice offline search interface it appears that “the archive also includes CSV and JSON files, the latter complete with each tweet’s metadata”. I’m looking forward to see the data visualisations/mashups people come up with around their data. 

The Twitter API has long allowed you to extract a user’s status updates the limitation being you can could only get the last 3,200 tweets. This is something Sheila MacNeill discovered when she tried vizify’s ‘Year on Twitter’ tool.

the archive of meExporting tweets was something I looked at in 2011 in the cryptically titled Google Spreadsheets and floating point errors aka when is 65078736491511804 + 1 = 65078736491511808 (and automatically archiving your Twitter Status Updates). With this I’ve got a record of my tweets going back to April 2010 which is triggered to update itself every week. A reason I do this is often I need to find things I’ve previously said in ‘the archive of me’.

Here’s the template (File > Make a copy) and follow the instructions if you want to try (please be aware of the Twitter Developer Rules of the Road). I’ve updated the code to make it compatibly with version 1.1 of the Twitter API. One of the options I’ve added is a JSON dump which is saved to your Google Drive. It only took two lines of code using Google Apps Script HT +Romain Vialard 

  var blob = Utilities.newBlob(Utilities.jsonStringify(json), "application/json", filename);

[The JSON dump is a bit buggy – some issues with character escaping somewhere]



Join the conversation

comment 5 comments
  • Sheila MacNeill

    Thanks Martin, btw have you been able to get your tweets from Twitter yet?

    • Martin Hawksey

      Still no download button for me yet 🙁

  • jerome

    Hi Martin,
    First of all, thanks a lot for your script!
    Nevertheless, I found a bug when updating a sheet.
    Indeed the update function systematically strips the first row of data (i.e. the newest tweet fetched), and thus, unless re-updating from scratch, this very tweet is lost.
    Just wanted to let you know!

Comments are closed.