Google Spreadsheets and floating point errors aka when is 65078736491511804 + 1 = 65078736491511808 (and automatically archiving your Twitter Status Updates)

Update: You can ignore most of this post as I noticed the Twitter API can return ids as a string. Here is the updated Export/Archive Twitter Status Updates Spreadsheet
I used to use my TwitterPad wordpress plugin to archive everything I said on Twitter. The main reason was often I find myself trying to remember links/resources I’ve previously tweeted. It worked well but every month I had to manually paginate my tweets to prevent the post getting too long.
Having got bored with this chore I wrote a Google Spreadsheet which interacts with the Twitter API to pull my timeline grabbing the last 3200 tweets. Part of this API allows you to just grab new updates since a tweet id (a similar parameter is available in the Twitter Search API used in Collect/backup tweets in a Google Spreadsheet on one sheet). Using this means I can setup a trigger in Google Apps Script to refresh my archive.
All was going well until I noticed it was pulling in duplicate entries. Part of the problem is Google Spreadsheet thinks 65078736491511804 is 65078736491511808. This meant the script was passing the wrong since id.
You can test this yourself by creating a new Google Spreadsheet and in the first cell [A1] enter 65078736491511804. With this cell highlighted if you select Format > Number and try to change it to nothing happens. This is because Google know there is a floating-point error and store the value as a text string.
If in the cell beneath the number (uh string 😉 [A2] you enter the formula =A1+1 you get 65,078,736,491,511,808 (if you get 6.51E+16 with the cell highlighted change Format > Number to Normal)
So if you want to start archiving your or someone else’s tweets you can make a copy of my Export/Archive Twitter Status Updates Google Spreadsheet. The health warning is there maybe missing or duplicates tweets. I have posted on the Google Apps Script forum to see if there is a way around this.


Join the conversation

comment 13 comments
  • Emma

    Looks interesting – I’ve got Momento on my iPod which gathers all my tweets (and has got them all … ) however, i’ve not really thought about what to do with them, nor, should I ever export the data, whether or not I can extricate the tweets from the facebook & flickr updates.

    • Martin Hawksey

      Hi Emma – other than the occasional brain fade not sure what I’ll do with my archive, just keeping it for that rainy day 😉

  • Ryan

    what is this error about?
    TypeError: Cannot read property “length” from undefined. (line 332)

    • Martin Hawksey

      Usual means no results have been returned from twitter – really must get around to catching that error

  • Montse

    Hi Martin, thanks for the spreadsheet! I get that error making tries with my own account and tweeting to check, but the script reports “Cannot read property “length” from undefined. (line 332)”.

    • Martin Hawksey

      @Montse there’s a known bug with Google Spreadsheets not using sheet templates properly. I’ve added a workaround which you can try with a fresh copy of the spreadsheet (it still might be misbehaving when it imports data when I had a go myself the timeline seemed out of date order)

      • Montse

        Martin, awesome! Now it works. Thank you so much!

  • Chris

    I’m getting an error in line 224 that says Unexpected Error:
    var result = UrlFetchApp.fetch(
    Do you know why it would be doing this?

    • Martin Hawksey

      looks like something went wrong with you twitter api authentication. Check that you entered your key/secret correctly (without whitespace) and you’ve got the correct callback url

  • Backup Twitter Status Updates to a Google Spreadsheet JISC CETIS MASHe

    […] tweets was something I looked at in 2011 in the cryptically titled Google Spreadsheets and floating point errors aka when is 65078736491511804 + 1 = 65078736491511808 …. With this I’ve got a record of my tweets going back to April 2010 which is triggered to update […]

Comments are closed.