Google Apps Script: Using a Google Spreadsheet to populate a Twitter list [Hashtag Communities]

Update: I’ve revised this idea in Populating a Twitter List via Google Spreadsheet … Automatically! [Hashtag Communities]
Having recently rediscovered the joys of Google Apps Script I was looking for something to do after my Event Manager Spreadsheet. A couple of ideas I had were: something to do with linked data (possibly a email subscription list); or having read the Google Apps Script Twitter Approval Manager Tutorial something around an automated timezone retweet system.
But while wandering through the Virtual coffee shop at Innovating e-Learning 2010 I noticed a post from my colleague at RSC Eastern, Shri Footring asking:

Does anyone know whether there an automated way to create a twitter list of everyone who has used the tag #jiscel10 in a tweet?

I’ve been closely following Tony Hirst’s work on visualising community and user networks using Gephi (partly to play ‘Where’s Wally’ as I vainly try and spot myself in the various network visualisation). I also new Tony had released his Twitter Community Grabbing Code – newt.py, which if I had ever coded in Python would let me solve Shri’s problem in a blink of an eye.
Instead I thought it would be completely insane interesting to see if I could replicate some of Tony’s newt.py functionality in Google Spreadsheets. And guess what you can! So here’s how. [You can shortcut some of the steps in 1, 3–5 by copying this Spreadsheet]

How-to create a Twitter list from Google Spreadsheet

  1. Follow the steps in the Google Apps Script Twitter Approval Manager Tutorial up to and including authorising your spreadsheet with Twitter (if you start Tweeting you’ve gone too far)
  2. Generate a list of twitter usernames you would like to add to a list. I did this by exporting the jiscel10 hashtag archive from the Twitter archiving service Twapper Keeper (I used the export and download button. If you use Excel Permalink (beta) option increase the View Limit)
  3. Import this data to a sheet of the Twitter Approval Manager Spreadsheet. You can edit this data the important thing is that there is a column with the heading from_user and preferably it’s in column C
  4. Insert a new sheet called ‘To add to Twitter List’ and in the first cell enter ‘=UNIQUE(Sheet1!C:C)’ (this strips out an duplicate twitter usernames)
  5. Open the Spreadsheets script editor by clickingTools > Scripts > Script Editor and copy the code at the end of this post at the end of the spreadsheet script. Click on Run > onOpen (this should save changes to the script), then close the Script Editor.

In the Twitter drop down menu on the main spreadsheet  you should have 2 options Add to a List and Purge a List.
Add to a List
When you select add to a list you’ll be prompted for a list name. This list must already existing on your Twitter account even if it has no follower. Once you enter the list name the spreadsheet should start adding usernames. This process can take some time depending on the size of your list (the maximum is 500) and you will be prompted when it is done. You can always open a separate browser window with the twitter list to check if names are being added.
Purge a List
I’ve included a purge option to allow you to rebuild the list without deleting it entirely. This means followers of a list won’t be lost. Again this process can take some time so be patient.

The code

Note: For this code to work you also need the code from the Twitter Approval Manager

// Add list of twitter usernames to a Twitter list
// coded by @mhawksey
// Available under Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License
var SCREEN_NAME = "";
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Add to a List", functionName: "addToList"},
                      {name: "Purge a List", functionName: "purgeList"},
                      {name: "Tweet", functionName: "tweet"},
                      {name: "Configure", functionName: "configure"} ];
  ss.addMenu("Twitter", menuEntries);
}
function getScreenName() {
  var oauthConfig = UrlFetchApp.addOAuthService("twitter");
  oauthConfig.setAccessTokenUrl(
      "https://api.twitter.com/oauth/access_token");
  oauthConfig.setRequestTokenUrl(
      "https://api.twitter.com/oauth/request_token");
  oauthConfig.setAuthorizationUrl(
      "https://api.twitter.com/oauth/authorize");
  oauthConfig.setConsumerKey(getConsumerKey());
  oauthConfig.setConsumerSecret(getConsumerSecret());
  var requestData = {
    "method": "GET",
    "oAuthServiceName": "twitter",
    "oAuthUseToken": "always"
  };
  var result = UrlFetchApp.fetch(
      "http://api.twitter.com/1/account/verify_credentials.json",
      requestData);
  var o  = Utilities.jsonParse(result.getContentText());
  SCREEN_NAME = o.screen_name;
}
function twitterList(method, list, query){
  if (SCREEN_NAME==""){
    getScreenName();
  }
  var requestData = {
        "method": method,
        "oAuthServiceName": "twitter",
        "oAuthUseToken": "always"
      };
   try {
      var result = UrlFetchApp.fetch(
          "http://api.twitter.com/1/"+SCREEN_NAME+"/"+list+"/members.json"+query,
          requestData);
      var o  = Utilities.jsonParse(result.getContentText());
    } catch (e) {
      Logger.log(e);
    }
   return o;
}
function purgeList(){
  var list_id=Browser.inputBox("Enter list name to purge:");
  while(twitterList("GET", list_id,"").users.length>0){
    var object = twitterList("GET", list_id,"");
    for (var i = 0; i < object.users.length; ++i) {
      twitterList("POST", list_id, "?_method=DELETE&id="+object.users[i].screen_name);
      Logger.log(object.users[i].screen_name);
    }
  }
  Browser.msgBox("List purged");
}
function addToList(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataSheet = ss.getSheetByName("To add to Twitter List");
  var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows(), 1);
  //get list name to add users to (must already exist)
  var list_id=Browser.inputBox("Enter list name to add users to:");
  objects = getRowsData(dataSheet,dataRange);
  for (var i = 0; i < objects.length; ++i) {
    var rowData = objects[i];
    if (rowData.fromuser!="" ){
      try {
        var o = twitterList("POST", list_id, "?id="+rowData.fromuser);
        dataSheet.getRange(i+2,2).setValue("Added");
      } catch (e) {
        Logger.log(e);
      }
    }
  }
  Browser.msgBox("The list now has :"+o.member_count+" members");
}
//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
//////////////////////////////////////////////////////////////////////////////////////////
// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
//   - sheet: the sheet object that contains the data to be processed
//   - range: the exact range of cells where the data is stored
//   - columnHeadersRowIndex: specifies the row number where the column names are stored.
//       This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
  columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(range.getValues(), normalizeHeaders(headers));
}
// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
//   - data: JavaScript 2d array
//   - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
  var objects = [];
  for (var i = 0; i < data.length; ++i) {
    var object = {};
    var hasData = false;
    for (var j = 0; j < data[i].length; ++j) {
      var cellData = data[i][j];
      if (isCellEmpty(cellData)) {
        continue;
      }
      object[keys[j]] = cellData;
      hasData = true;
    }
    if (hasData) {
      objects.push(object);
    }
  }
  return objects;
}
// Returns an Array of normalized Strings.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    var key = normalizeHeader(headers[i]);
    if (key.length > 0) {
      keys.push(key);
    }
  }
  return keys;
}
// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
//   - header: string to normalize
// Examples:
//   "First Name" -> "firstName"
//   "Market Cap (millions) -> "marketCapMillions
//   "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
  var key = "";
  var upperCase = false;
  for (var i = 0; i < header.length; ++i) {
    var letter = header[i];
    if (letter == " " && key.length > 0) {
      upperCase = true;
      continue;
    }
    if (!isAlnum(letter)) {
      continue;
    }
    if (key.length == 0 && isDigit(letter)) {
      continue; // first character must be a letter
    }
    if (upperCase) {
      upperCase = false;
      key += letter.toUpperCase();
    } else {
      key += letter.toLowerCase();
    }
  }
  return key;
}
// Returns true if the cell where cellData was read from is empty.
// Arguments:
//   - cellData: string
function isCellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}
// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
  return char >= 'A' && char <= 'Z' ||
    char >= 'a' && char <= 'z' ||
    isDigit(char);
}
// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
  return char >= '0' && char <= '9';
}
​
chevron_left
chevron_right

Join the conversation

comment 18 comments
  • Colin Gray

    Great tutorial Martin, thanks very much! Do you know if this can be adapted to extract tweet comments on a hash tag? Interested to take a record of the full twitter stream!
    Colin

    • Martin Hawksey

      Hi Colin,
      Now there’s a thought!
      This post describes a method I came up with for using Google Spreadsheets to monitor a twitter hastag. I should be fairly simply to merge the two concepts.
      I would recommend also using the Twapper Keeper service which is free to backup hashtag tweets as the Spreadsheet solution isn’t always 100% reliable with very popular tags.
      Many thanks,
      Martin

  • Bill

    Hi Martin
    Great tutorial.
    I want to deploy ‘with extreme prejudice’ – however when I try to copy the spreadsheet I get this error message:
    [1, ‘You can no longer view this document. The owner of the spreadsheet has changed the sharing options.’]
    I’ll just try and work thru steps 1, 3-5 and see what happens.
    Many thanks
    Bill

  • Mark Scott

    Hi,
    I’ve recently discovered the true beauty of Google, Twitter and the concept of mashups. Its just amazing how I was just thinking of this idea of pre-populating a google spreadsheet and having google apps automatically send the tweets. Its encouraging though to know that I’m on the right path since clearly you guys are trail blazers.
    Awesome.

    • Martin Hawksey

      Hi Scott,
      Thanks for your kind words. Hopefully you have enough pieces of the puzzle to make your idea work (if not feel free to drop me a line 😉
      Also please feel free to share a link to your finished spreadsheet
      Martin

  • Mark Scott

    Hey Martin,
    I used the google script(Google Apps Script Twitter Approval Manager Tutorial) and have successfully gotten an automatic-tweeter set up. So I just populate my Spreadsheet with the tweets in one column, and in the cell B2, I keep an incremented count of the last tweet that was sent, that way I can use that to identify which row is next that a tweet will be sent from.
    The actual function that I run to do the tweets is documented below. NB, most of the codes that I have in this function are the relevant parts of the function doGet(e) that is already in the script. However, I want to By Pass the whole interface of email verification for the tweet. So
    sendTweet(){ authorize();
    // Tweet must be URI encoded in order to make it to Twitter safely
    var encodedTweet = encodeURIComponent(getRowContent());
    var requestData = {
    “method”: “POST”,
    “oAuthServiceName”: “twitter”,
    “oAuthUseToken”: “always”
    };
    try {
    var result = UrlFetchApp.fetch(
    “https://api.twitter.com/1/statuses/update.json?status=” + encodedTweet,
    requestData);
    } catch (e) {
    Logger.log(e);
    }
    }
    The getRowContent() fucntion I have basically just checks the value of cell b2 to determine which row it should look up for content, then it returns that rows content. So i pass getRowContent into the encodedTweet variable. Here’s my getRowContent, increment and getRowID functions.
    function getRowID(){
    return SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(2, 2).getValue()
    }
    function getRowContent(){
    var content = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(getRowID(), 1).getValue()
    increment();
    return content
    }
    function increment(){
    var rowVal = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(2, 2).getValue() + 1
    SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange(2, 2).setValue(rowVal)
    }
    Cheers.

  • Martin Hawksey

    Hi Matt – Thanks for sharing. There are some interesting directions you could take this in. For example you could put a date in the column to schedule tweets 😉
    Working on something new with Spreadsheet/Twitter. Hopefully I’ll have something to show soon
    Martin

  • Mark Scott

    It is funny that you said that Martin. I actually want to incorporate a date field and tie the scheduled tweets to a google calendar, like a type of dash board interface from which I can add tweet to a day.
    I just need to figure out how to validate that my local date and time is calculated against the google servers date/time.
    Btw, I’m trying to add this follow-me option for twitter to google sites, so someone can follow the my twitter account and I feel like Im running in circles cuz I need to use a gadget apparently and I’m definately new to the whole gadget creating thing. Any ideas or direction you could send me in?
    If anything and I make no progress I thnk I’m just going to add a google form and incorporate the Add-To-List mods you have, may have to tweak it a bit but I guess that’s the next best option to allow someone to follow my twitter account from on my google site.
    Let me know what you think
    Cheers.

  • Mark Scott

    I’ve done some more work on tweeting, but this time it was for using a google sign-up form embedded in my blog to automatically tweet a welcome message to someone that has decided to subscribe.
    I also decided it would be good to automatically follow that person on twitter.
    Check it out and let me know what you think.
    I think I’m going to tackle another aspect, but I’m still seeing so much potential for the google calendar usage and tweeting.
    Cheers.
    Oh, here’s the link
    http://mark-techsavvvy.blogspot.com/2011/02/auto-tweet-part-ii-someone.html#more

  • Using Google Docs to manage Twitter lists : Media Hack

    […] instructions which authorises your spreadsheet with Twitter. Once that is done you need to use this guide to add your spreadsheet details to your already created Twitter list. It looks complicated but […]

  • steve patterson

    Hi Martin,
    I used Export Twitter Friends and Followers v2.1.2 to export my friends which worked very nicely. Thank you for this script.
    Then I wanted to load a list with my friends. I downloaded the “Create Twitter List” spreadsheet.
    I authorized the spreadsheet by going to and adding my email, my consumer key and secret and I added the URL of the “Create Twitter List” spreadsheet set on the “to add to twitter list” sheet to the field “Publish as a Service URL:”
    Then I copied 10 twitter handles to column C under the label “from_user” on the sheet autnamed “Sheet 1”
    Then I went to the menu and when the window opened requesting the list name I added exists as an empty list on the twitter account that was authenticated with the key and secret. Then a small window opens stating “running script addToList” but the list is not populated.
    Can you please tell me what I missed?
    Thank you

Comments are closed.

css.php