Timothy Coxon

A little while back I spent a considerable amount of time trying to find the best way to get a list of pre-written tweets and have them published at set times during the day. For example you could recycle the content of your blog and revitalise the traffic for older posts. Or you could have a list of your favourite bookmarks and have them add value to your Twitter feed. There are solutions out there for this very purpose but I have yet to find one for free. However with a bit of fiddling with code it’s possible to do this for free using nothing more than a Google Spreadsheet.

Stage 1

Make a new App in Twitter. This is probably the trickiest bit. You need to set up a new application for your spreadsheet and this process is more than just authorising a pre-built application to make use of your account.

First you need to goto https://dev.twitter.com/apps/new (sign in with your twitter login if necessary).

Now fill out the details. Name and description can really be anything you wish. For the Website I’d put in the root of your own website e.g. for me this was timothycoxon.co.uk. Next we have the Callback URL. For this you need to add a specific URL so that after the Google script asks Twitter for authentication Twitter knows to send you back to the correct page on Google, So for Callback URL add “https://spreadsheets.google.com/macros”

After this section you will need to read and accept the Terms of Service and then fill out the Captcha code. Finally click “Create your Twitter Application”.

If you have filled everything out correctly you should now be on the Dashboard for your new Application. As a new application you will have a Read Only application and obviously if you are looking to have the spreadsheet post tweets then you are going to need to change your application to Read and Write. To do this then click on the Settings tab near the top of the page. Scroll down to the “Application Type” section and switch the radio button from “Read Only” to “Read and Write”.

Next you need to grab your secret keys. Both Consumer Key and Consumer Secret. These can be found on either the Details Tab or the OAuth Tools Tab from your Twitter Application dashboard. They are rather long random alphanumeric strings. Once you have these this is all you need from Twitter. The Keys you now have are needed in stage 3

Stage 2

Make a new spreadsheet.

Personally I have 4 columns. Column 1 is for the tweet itself. Column 2 is “Last Tweeted” this is where I have the script put the last date that the script ran for a particular row. Column 3 is “Count” this is where the script will keep track of how many times a particular row has been tweeted. Column 4, I use as a helper column and use “=len(a2)” so I can have a character count for the first column, remember that tweets should be 140 characters or less.

(I would suggest adding a couple tweets into the first column for testing in stage 3/4.)

Stage 3

Now we finally add the Google Apps Script to the Spreadsheet to make the magic happen. From the Google Spreadsheet goto the Tools section on the menubar and select “Script Editor”. On the next page select “Create New Project” and from the next screen click “Blank Project”.

Select all the script below and paste that into the editor.

function authorize() {

var oAuthConfig = UrlFetchApp.addOAuthService("twitter");
oAuthConfig.setAccessTokenUrl("http://api.twitter.com/oauth/access_token");
oAuthConfig.setRequestTokenUrl("http://api.twitter.com/oauth/request_token");
oAuthConfig.setAuthorizationUrl("http://api.twitter.com/oauth/authorize");
oAuthConfig.setConsumerKey("**************************");
oAuthConfig.setConsumerSecret("************************");
  
  var requestData = {
    "method": "GET",
    "oAuthServiceName": "twitter",
    "oAuthUseToken": "always"
  };
  var result = UrlFetchApp.fetch(
      "https://api.twitter.com/1/statuses/mentions.json",
      requestData);
}

function postTweet(tweet) {
// Authorize to Twitter
    authorize();
// Tweet must be URI encoded in order to make it to Twitter safely
    var encodedTweet = encodeURIComponent(tweet);
    
    var requestData = {
// Twitter API requires us to send an HTTP POST request
      "method": "POST",
// This should be the name of the service we configure in authorize()
      "oAuthServiceName": "twitter",
// Always send an OAuth token along with this API request
      "oAuthUseToken": "always"
    };
    try {
// Actually make the request that posts a tweet!
      var result = UrlFetchApp.fetch(
          "https://api.twitter.com/1/statuses/update.json?status=" + encodedTweet,
          requestData);
    } catch (exception) {
// An error occurred!  Log it, so that it’s visible in the script editor’s log
      Logger.log(exception);
    }
}

function getTweet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getDataRange().getValues();
  var postNo =   Math.ceil(Math.random()*(dataRange.length-1));
  Logger.log("random numer:" + postNo);
  Logger.log(new Date());
  var tweet = dataRange[postNo][0];
  Logger.log("tweeting:" + tweet);
  postTweet(tweet);
  sheet.setActiveCell("B"+(postNo+1));
  sheet.getActiveRange().setValue(new Date());
  sheet.setActiveCell("C"+(postNo+1));
  var no = sheet.getActiveRange().getValue();
  sheet.getActiveRange().setValue(++no);
}
									

Now we make use of the Consumer Key and Consumer Secret from Stage 2. On lines 7 and 8 replace the **** with the details and save your script.

Finally for this section you need to make sure that this script is authorised by Twitter. Click the Play icon on the toolbar to run your script, this will try post a random row to Twitter to which Twitter will ask you for authentication. It’s important to run the script manually first so that you can set the authorisation. When the script runs later via triggers it will need to already have the authentication.

Stage 4

Finally the automation part. On the Script Editor Window. Goto the Resources section on the Menubar and select “Current project’s triggers..”. This will bring up a fairly simple interface for adding time based triggers for when to run your script. In the first column switch the pulldown from “authorize” to “getTweet”, Next switch “From Spreadsheet” to “Time-Driven”, Finally Select when you want this script to run on your spreadsheet and tweet to twitter.

Personally I have three triggers running at 9am, 12am and 4pm to hit good times for people in the UK.

 

Finally if anyone has any other tips or tricks along these lines please leave a comment as I am always looking for new and interesting things like this.