Creating a Twitter Question/Revision Bot using Google Sheets

My old colleague at Cetis, David Sherlock, posted a nice little  ‘Twitter Question/Revision Bot’. This uses a .csv file of questions and multiple choice answers which get randomly tweeted out using a Python script. David designed the project with a Raspberry Pi in mind but also highlights it can be easily run on any Unix like environment such as Mac OS X or Linux. As not everyone is going to have easy access to this here’s how you can do something similar with Google Sheets (if you don’t want to play copy and paste coding make a copy of this sheet).

1. Setting up a Google Sheets environment to handle it

  1. Start with a new Google Sheet and like David have six columns in each row with question, answer, three options (one of which the correct one) and an extra row to record if the question has been asked.
  2. In your spreadsheet open Tools > Script editor and when asked start a ‘Blank Project’
  3. In the new editor window select Resources > Libraries (this will first prompt you to give your project a name, I called mine TwitBot.
  4. In the ‘Find a Library’ box enter MarIlVOhstkJA6QjPgCWAHIq9hSqx7jwh and click Select
  5. You should now see ‘TwtrService’ listed as one of the libraries. In the ‘Version’ dropdown select the latest version (at time of writing 14), and click Save

TwtrService is a library I’ve written so interact with the Twitter API. You can read more about it here.

In the code window add the following code and click save:

function setup() {
  if (TwtrService.isUserConnectedToTwitter()){
   var result = Browser.msgBox("Twitter Authorisation",
                   "You appear to already be connected to Twitter.\\n\\nWould you like to run the setup again?",
                   Browser.Buttons.YES_NO);
    // Process the user's response.
    if (result == 'yes') {
      // User clicked "Yes".
      TwtrService.showTwitterKeySecret(SpreadsheetApp);
    }
  } else {
    TwtrService.showTwitterKeySecret(SpreadsheetApp);
  }
}

The above code uses the TwtrService to help you set up Twitter access if required

Your script window should look like this:

script editor

2. Create a Twitter App

If you’ve used my other TAGS templates you can reuse your details for this. To see if Twitter App details are required from the script window select Run > setup (if setup isn’t listed you need to first save your code). Running setup will start the first part of the authentication process. Click continue and review the authentication required and ‘Accept’ if you are happy.

Auth required

Going back to the spreadsheet you started there should now be a dialog window asking you to do something. If you haven’t setup a Twitter App before it should look like this:

Twitter app creation

Follow the instructions onscreen to create your app.

Important: The Twitter account you use to authorise access is the one that will send out the tweets

3. Write a Python Google Apps Script

Add the code below to your existing script project and save:

var doc = SpreadsheetApp.getActiveSpreadsheet();
var sheet = doc.getSheetByName('Sheet1');

function tweetQuestion(){
  var ran = Math.floor(Math.random() * (sheet.getLastRow()-1)) + 2;
  var row = sheet.getRange(ran, 1, 1, sheet.getLastColumn()).getValues()[0];
  if (row[5] !== ""){
    tweetQuestion(); // if already asked pulls another random row
  } else {
    var tweet =  "Q: " + row[0];
    var tweet2 =  row[2];
    var tweet3 =  row[3];
    var tweet4 =  row[4];
    
    TwtrService.post('statuses/update', {status: tweet});
    var options = [tweet2,tweet3,tweet4];
    shuffle(options); 
    TwtrService.post('statuses/update', {status: "A: " + options[0]);
    TwtrService.post('statuses/update', {status: "B: " + options[1]);
    TwtrService.post('statuses/update', {status: "C: " + options[2]);
    sheet.getRange(ran, 6).setValue(new Date());
  }
}

// http://stackoverflow.com/a/25984542/1027723
function shuffle(a,b,c,d){//array,placeholder,placeholder,placeholder
 c=a.length;while(c)b=Math.random()*c--|0,d=a[c],a[c]=a[b],a[b]=d
}

4. Time the script to run every hour or so

In the script editor select Resources > Current project’s triggers and click ‘No triggers set up. Click here to add one now.’. Select to run tweetQuestion every hour (or your preference), and also click ‘notification’ so you can get an email if the script fails. Finally click ‘Save’

Timed triggers

What will happen now is the function even if you don’t  have the spreadsheet or script editor open or even your browser.

Important: when this script runs out of questions it will go into an infinite loop. You can go back into the trigger window to remove the function at any point. If you don’t you’ll end up using all of your script runtime quota. You homework is to figure a way to get the script to bail if there are no questions left.

My homework…

function tweetQuestion(){
  var asked_col = sheet.getRange(2, 6, sheet.getLastRow()-1).getValues();
  // get unasked q's
  var unasked = [];
  for(var i=0; i < asked_col.length; i++) {
    if(asked_col[i][0] == "") {
      unasked.push(i+2);
    }
  }
  // randomly pick one
  var ran = Math.floor(Math.random() * unasked.length);
  if (unasked[ran]){
    var row = sheet.getRange(unasked[ran], 1, 1, sheet.getLastColumn()).getValues()[0];
    var tweet =  "Q: " + row[0];
    var tweet2 =  row[2];
    var tweet3 =  row[3];
    var tweet4 =  row[4];
    
    TwtrService.post('statuses/update', {status: tweet});
    var options = [tweet2,tweet3,tweet4];
    shuffle(options); 
    TwtrService.post('statuses/update', {status: "A: " + options[0]});
    TwtrService.post('statuses/update', {status: "B: " + options[1]});
    TwtrService.post('statuses/update', {status: "C: " + options[2]});
    
    sheet.getRange(unasked[ran], 6).setValue(new Date());
  } else {
    // no questions left - do something else
  }
}

4 Comments


  1. Brilliant! Thanks for this. My post for tomorrow is python reddit bot. Maybe I can do a Google reddit bot instead?


  2. I will certainly be giving this a go – we have been using hootsuite to do teaching via social media – this may open up other adaptive opportunities

Comments are closed.