A free Twitter buffer with Google Apps Scripts

You might know about Buffer, a web service where you can create a stash of tweets which will be posted to your Twitter account at regular intervals. Instead of overwhelming your followers with all the interesting things you have to say, you can configure it such that it will send out a tweet at 9am and 1pm every day (for instance). The only thing you have to worry about is to keep your buffer filled.

However, the buffer size at Buffer is only 10 tweets. If you want more you need to pay (or make your friends sign up as a referral). Because I find $10/mo a bit much for just a buffer, I decided to make my own.

We'll do this with a Google Spreadsheet and a Google Apps Script. The spreadsheet is the buffer, which will contain all tweets you are about to post. The script will be triggered at regular intervals which fetches a single tweet from your spreadsheet, posts it to twitter and removes the tweet from the spreadsheet.
The script is written in Google Script, which is actually Javascript, but it provides access to Google services and other convenience functions for sending email and making HTTP requests (and more). We'll use this functionality below to implement the buffer application.

The spreadsheet

First we'll be creating a spreadsheet: head over to Google Drive, create a new spreadsheet and give it a descriptive name (e.g. Buffer). Next, let's fill in some example tweets in the first column:

The script

The next step is to create a Google Apps Script. In the spreadsheet we've just created, go to Tools, Script editor. We'll start with the following script, which is merely a skeleton of the final version:

function buffer() {
  // open the spreadsheet document
  var doc = SpreadsheetApp.getActiveSpreadsheet();
 
  // get the first sheet
  var sheet = doc.getSheets()[ 0 ];
 
  function getNextTweet() {
    // TODO
  }
 
  function postTweet( pTweet ) {
    // TODO
    return false;
  }
 
  var tweet = getNextTweet();
 
  if ( tweet !== false && postTweet( tweet ) ) {
    // tweet has arrived, remove the first row in the buffer
    sheet.deleteRow( 1 );
  }
}

The script should be fairly readable to anyone familiar with Javascript: we fetch the spreadsheet we've just created and get our hands on the first sheet inside. There are two stubs for fetching the next tweet and posting the tweet at Twitter. Both stubs will return false if something went wrong. The getNextTweet function will return the string with the tweet inside if the buffer was not depleted.
If the tweet is posted to Twitter successfully, it will be removed from the spreadsheet by removing row 1 (note that spreadsheet indices start at 1 rather than at 0).

Getting the next tweet

Let's implement the getNextTweet function first. As mentioned, it will return the value of the top left cell of the document's first sheet. This value is accessed by calling getValue() on a range.
However, before we do this we need to check whether there's a value at all. If the buffer is empty, we'd like to be notified by email. Sending an email is easy in Google Apps Script, just call MailApp.sendEmail() and it will send an email as the user who's running the script. Currently, the quota is 500 emails per day, which should be plenty if this is the only script you run.
All this functionality is implemented with the following code:

function getNextTweet() {
  function sendMail( pSubject ) {
    var to = "me@example.org";
    if ( MailApp.getRemainingDailyQuota() > 0 ) {
      MailApp.sendEmail( to, pSubject, pSubject );
    }
  }
   
  var range = sheet.getDataRange();

  if ( range.getNumRows() === 0 ) {
    // there were no tweets in the buffer
    sendMail( "Buffer is empty!" );
    return false;
  }
   
  // return the value of the first row and first column
  return range.getValue();
}

If the buffer should remain filled at all times, you could also modify the code to send a warning email when the number of tweets drops below a certain threshold. That's up to you.

Registering the Twitter application

Before we are going to send the tweet we've just obtained, head over to dev.twitter.com to register our application. By registering our app we'll be able to authenticate our script with Twitter, which uses OAuth. Fortunately, Google Script provides a service which takes care of all the hairy implementation details behind this authentication scheme.
At the registration it is important to enter a callback URL: by entering an URL we'll be redirected to the Twitter website upon authentication. The authentication will simply fail if we leave this empty. For this script, we'll just enter the script's URL (starting with https://script.google.com/). After filling in the necessary fields you'll immediately obtain a consumer key and a consumer secret, which we'll need in the next step.

After you have registered the application, make sure it is allowed to Read and Write, otherwise we cannot post any tweets. Check the application's settings as shown in the screenshot below:

Sending the tweet

Everything is in place now to write the code which authenticates with Twitter and sends the tweet to the outside world. The code below takes care of the authentication and posting the tweet we got passed as a parameter:

function postTweet( pTweet ) {
  var consumerKey = "key";
  var consumerSecret = "secret";

  var oauth = UrlFetchApp.addOAuthService( "twitter" );
   
  oauth.setConsumerKey( consumerKey );
  oauth.setConsumerSecret( consumerSecret );
   
  oauth.setAccessTokenUrl( "https://api.twitter.com/oauth/access_token" );
  oauth.setRequestTokenUrl( "https://api.twitter.com/oauth/request_token" );
  oauth.setAuthorizationUrl( "https://api.twitter.com/oauth/authorize" );
   
  try {
    data = {
      "method": "POST",
      "oAuthServiceName": "twitter",
      "oAuthUseToken": "always"
    };
     
    var encodedTweet = encodeURIComponent( pTweet );
    UrlFetchApp.fetch( "http://api.twitter.com/1/statuses/update.json?status=" + encodedTweet, data );
  } catch ( e ) {
    Logger.log( e );
    return false;
  }
   
  return true;
}

Google Script has a UrlFetchApp class which can make HTTP(S) requests for you. It also provides access to the OAuth service which will do all the dirty work for us. We simply need to provide the consumer key and secret and the URLs. When we want to post something, we'll need to pass some additional data with the request which tells the service we're using OAuth authentication.

If no authentication has taken place before, the script has no access token and secret. In that case you'll see a popup when you execute this code for the first time:

If we proceed, we are redirected to the Twitter website, where you enter the credentials of the Twitter user whose timeline should receive the tweets from the buffer. If all goes well, the Twitter website closes and in the background our script has obtained an access token and secret. This access token remains valid unless the Twitter user revokes the app's permissions in his or her settings.

We're almost done: each time we run the full script, a tweet is popped from the buffer and posted as the authenticated Twitter user. There's one more thing left: automation.

Setting up the triggers

Of course we don't want to run this script manually, so by adding one or more triggers we don't have to worry about this. In the script editor, go to Resources, Current script's triggers. A dialog appears where we can add time-driven triggers to our script. The screenshot below describes the example where we post twice during the day, one tweet in the morning and one tweet in the afternoon.

We're done

Now we have a fully automated script which reads your spreadsheet and posts them at regular intervals on your Twitter account. The full source can be found here. You only have to worry about creating content in the spreadsheet. You could also extend its functionality to post to Facebook or LinkedIn (which also use the OAuth authentication scheme), or write a more elaborate warning system when the buffer is becoming empty. At least you've saved yourself $10/month by doing the buffering yourself.