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
- 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)
- 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)
- 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
- 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)
- 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';
}