twitteralytics v2.2 - using oAuth for increased rate on search results

gistfile1.js

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sumSheet = ss.getSheetByName("Readme/Settings");
var REFERER = sumSheet.getRange("B9").getValue();
var SEARCH_TERM = sumSheet.getRange("B10").getValue();
var SEARCH_DURATION = sumSheet.getRange("B11").getValue();
var NUMBER_OF_TWEETS = sumSheet.getRange("B12").getValue(); 
var RESULT_TYPE = sumSheet.getRange("B13").getValue(); 

function onOpen() {
  var menuEntries = [ {name: "Test Collection", functionName: "testCollection"}, {name: "Run Now!", functionName: "collectTweets"}  ];
  ss.addMenu("Twitter", menuEntries);
}

function collectTweets() {
  // if continuous sheetname = archive else make a name
  if (RESULT_TYPE == "continuous"){
    var sheetName = "Archive";
  } else {
    var sheetName = Utilities.formatDate(new Date(), "GMT", "dd-MMM-yy hh:mm"); //make a new sheet name based on todays date
  }
  // if sheetname doesn't exisit make it
  if (!ss.getSheetByName(sheetName)){
    var temp = ss.getSheetByName("TMP");
    var sheet = ss.insertSheet(sheetName, {template:temp});
  } else {
    var sheet = ss.getSheetByName(sheetName);
  }
  // else if already have archive get since id
  var stored = getRowsData(sheet);
  if (stored.length >0 ){
    var sinceid = stored[0]["id"];
  } 
  // prepare search term
  var myUntil=new Date();
  myUntil.setDate(myUntil.getDate()-1);
  var mySince = new Date(myUntil);
  if (SEARCH_DURATION == " weekly"){
    myUntil.setDate(myUntil.getDate()+1);
    mySince.setDate(mySince.getDate()-6);
  }
  //if no since id grab search results
  if (sinceid){
    var data = getTweets(SEARCH_TERM, mySince, myUntil, NUMBER_OF_TWEETS, sinceid); // get results from twitter sinceid
  } else {
    var data = getTweets(SEARCH_TERM, mySince, myUntil, NUMBER_OF_TWEETS); // get results from twitter
  }
  // if some data insert rows
  if (data[0]["from_user"]){
    sheet.insertRowsAfter(1, data.length);
    setRowsData(sheet, data);
  }

  /*
  // This is a very greedy grab (the limit of since id is it has max of 100 results, this method gives you 1500)
  var data = [];  
  if (sinceid){  
    for (var i = 0; i < objects.length; i++){
      var row = objects[i];
      if (row.id > sinceid){
        data.push(row);
      }
    }
    if (data.length > 0){
      sheet.insertRowsAfter(1, data.length);
    }
  } else {
    data = objects;
  }
  */
}

function testCollection(){
  var myUntil=new Date();
  myUntil.setDate(myUntil.getDate()-1);
  var mySince = new Date(myUntil);
  if (SEARCH_DURATION == " weekly"){
    myUntil.setDate(myUntil.getDate()+1);
    mySince.setDate(mySince.getDate()-6);
    Logger.log(mySince);
  }
  
  var data = getTweets(SEARCH_TERM, mySince, myUntil,  NUMBER_OF_TWEETS); // get results from twitter
  Logger.log(data[0]["from_user"]);
  if (data[0]["from_user"]){
    Browser.msgBox("Found "+data.length+" tweets. Here's an example one from "+data[0]["from_user"]+" which says: "+data[0]["text"]);
  } else {
    Browser.msgBox("Twitter said: "+data);
  }
}

function getTweets(searchTerm, since, until, maxResults, sinceid, languageCode) {
    //Based on Mikael Thuneberg getTweets - mod by mhawksey to convert to json
    // if you include setRowsData this can be used to output chosen entries
 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"
      };
   
    try {
        var pagenum = 1;
      var data =[];
      var idx = 0;
      var sinceurl ="";
        if (typeof maxResults == "undefined") {
            maxResults = 100;
        }
        if (maxResults > 1500) {
            maxResults = 1500;
        }
        if (maxResults > 100) {
            resultsPerPage = 100;
            maxPageNum = maxResults / 100;
        } else {
            resultsPerPage = maxResults;
            maxPageNum = 1;
        }
        if (typeof sinceid != "undefined") {
            if (sinceid.length > 0) {
                resultsPerPage = 100;
                maxPageNum = 1;
                sinceurl = "&since_id=" + sinceid;
            }
        }
        Logger.log(twDate(since)+" "+twDate(until));
        searchTerm = encodeURIComponent(searchTerm);
        for (pagenum = 1; pagenum <= maxPageNum; pagenum++) {
            var URL = "http://search.twitter.com/search.json"
            URL = URL + "?q=" + searchTerm;
            URL = URL + "&since=" + twDate(since);
            URL = URL + "&until=" + twDate(until);
            URL = URL + "&rpp=" + resultsPerPage;
            URL = URL + "&page=" + pagenum;
            URL = URL + "&result_type=recent";
            URL = URL + sinceurl;
            if (typeof languageCode != "undefined") {
                if (languageCode.length > 0) {
                    URL = URL + "&lang=" + languageCode;
                }
            }
            
            var response = UrlFetchApp.fetch(URL, requestData);
     
            if (response.getResponseCode() == 200) {
              var objects = Utilities.jsonParse(response.getContentText()).results;
              
              for (i in objects){ // not pretty but I wanted to extract geo data
                if (objects[i].geo != null){
                  objects[i]["geo_coordinates"] = "loc: "+objects[i].geo.coordinates[0]+","+objects[i].geo.coordinates[1];
                }
                objects[i]["status_url"] = "http://twitter.com/"+objects[i].from_user+"/statuses/"+objects[i].id_str;
                data[idx]=objects[i];
                idx ++;
              }
            }      
          }         
    return data;
    } catch (e) {
        return e.message;
    }
} 

function twDate(aDate){
  var dateString = Utilities.formatDate(aDate, "GMT", "yyyy-MM-dd");
  return dateString;
}
// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
//   - sheet: the Sheet Object where the data will be written
//   - objects: an Array of Objects, each of which contains data for a row
//   - optHeadersRange: a Range of cells where the column headers are defined. This
//     defaults to the entire first row in sheet.
//   - optFirstDataRowIndex: index of the first row where data should be written. This
//     defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
  var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getMaxColumns());
  var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
  var headers = normalizeHeaders(headersRange.getValues()[0]);

  var data = [];
  for (var i = 0; i < objects.length; ++i) {
    var values = []
    for (j = 0; j < headers.length; ++j) {
      var header = headers[j];
      values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
    }
    data.push(values);
  }

  var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(), 
                                        objects.length, headers.length);
  destinationRange.setValues(data);
}
// 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
//       This argument is optional and it defaults to all the cells except those in the first row
//       or all the cells below columnHeadersRowIndex (if defined).
//   - 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) {
  var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
  var dataRange = range || 
    sheet.getRange(headersIndex + 1, 1, sheet.getMaxRows() - headersIndex, sheet.getMaxColumns());
  var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
  var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  return getObjects(dataRange.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. 
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
//   - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
  var keys = [];
  for (var i = 0; i < headers.length; ++i) {
    keys.push(normalizeHeader(headers[i]));
  }
  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)) { // I removed this because result identifiers have '_' in name
    //  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';
}

// The first part of this code was developed by google and has a copyright statement. 
// Everything after // Archive Twitter Status Updates is by mhawksey and released under CC


// Copyright 2010 Google Inc. All Rights Reserved.
 
/**
 * @fileoverview Google Apps Script demo application to illustrate usage of:
 *     MailApp
 *     OAuthConfig
 *     ScriptProperties
 *     Twitter Integration
 *     UiApp
 *     UrlFetchApp
 *     
 * @author vicfryzel@google.com (Vic Fryzel)
 */

/**
 * Key of ScriptProperty for Twitter approvers.
 * @type {String}
 * @const
 */
var APPROVERS_PROPERTY_NAME = "twitterApprovers";

/**
 * Key of ScriptProperty for Twitter consumer key.
 * @type {String}
 * @const
 */
var CONSUMER_KEY_PROPERTY_NAME = "twitterConsumerKey";

/**
 * Key of ScriptProperty for Twitter consumer secret.
 * @type {String}
 * @const
 */
var CONSUMER_SECRET_PROPERTY_NAME = "twitterConsumerSecret";

/**
 * Key of ScriptProperty for Twitter consumer secret.
 * @type {String}
 * @const
 */
var SERVICE_URL_PROPERTY_NAME = "serviceUrl";

/**
 * Key of ScriptProperty for tweets and all approvers.
 * @type {String}
 * @const
 */
var TWEETS_APPROVERS_PROPERTY_NAME = "twitterTweetsWithApprovers";

/**
 * @return String All approver email addresses that are required
 *                for a tweet to be posted.  Comma-delimited.
 */
function getApprovers() {
  var approvers = ScriptProperties.getProperty(APPROVERS_PROPERTY_NAME);
  if (approvers == null) {
    approvers = "";
  }
  return approvers;
}

/**
 * @param String Approver email address required to give approval
 *               prior to a tweet going live.  Comma-delimited.
 */
function setApprovers(approvers) {
  ScriptProperties.setProperty(APPROVERS_PROPERTY_NAME, approvers);
}

/**
 * @return String OAuth consumer key to use when tweeting.
 */
function getConsumerKey() {
  var key = ScriptProperties.getProperty(CONSUMER_KEY_PROPERTY_NAME);
  if (key == null) {
    key = "";
  }
  return key;
}

/**
 * @param String OAuth consumer key to use when tweeting.
 */
function setConsumerKey(key) {
  ScriptProperties.setProperty(CONSUMER_KEY_PROPERTY_NAME, key);
}

/**
 * @return String OAuth consumer secret to use when tweeting.
 */
function getConsumerSecret() {
  var secret = ScriptProperties.getProperty(CONSUMER_SECRET_PROPERTY_NAME);
  if (secret == null) {
    secret = "";
  }
  return secret;
}

/**
 * @param String OAuth consumer secret to use when tweeting.
 */
function setConsumerSecret(secret) {
  ScriptProperties.setProperty(CONSUMER_SECRET_PROPERTY_NAME, secret);
}

/**
 * @return String URL where this script is published as a service.
 */
function getServiceUrl() {
  var url = ScriptProperties.getProperty(SERVICE_URL_PROPERTY_NAME);
  if (url == null) {
    url = "";
  }
  return url;
}

/**
 * @param String URL where this script is published as a service.
 */
function setServiceUrl(url) {
  ScriptProperties.setProperty(SERVICE_URL_PROPERTY_NAME, url);
}

/**
 * @return bool True if all of the configuration properties are set,
 *              false if otherwise.
 */
function isConfigured() {
  return getApprovers() != "" && getConsumerKey() != "" &&
      getConsumerSecret != "" && getServiceUrl() != "";
}


/** Retrieve config params from the UI and store them. */
function saveConfiguration(e) {
  setApprovers(e.parameter.approvers);
  setConsumerKey(e.parameter.consumerKey);
  setConsumerSecret(e.parameter.consumerSecret);
  setServiceUrl(e.parameter.serviceUrl);
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

/**
 * Configure all UI components and display a dialog to allow the user to 
 * configure approvers.
 */
function renderConfigurationDialog() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle(
      "Add and Remove Twitter Approvers");
  app.setStyleAttribute("padding", "10px");
  
  var consumerKeyLabel = app.createLabel(
      "Twitter OAuth Consumer Key:");
  var consumerKey = app.createTextBox();
  consumerKey.setName("consumerKey");
  consumerKey.setWidth("100%");
  consumerKey.setText(getConsumerKey());
  var consumerSecretLabel = app.createLabel(
      "Twitter OAuth Consumer Secret:");
  var consumerSecret = app.createTextBox();
  consumerSecret.setName("consumerSecret");
  consumerSecret.setWidth("100%");
  consumerSecret.setText(getConsumerSecret());
  
  var saveHandler = app.createServerClickHandler("saveConfiguration");
  var saveButton = app.createButton("Save Configuration", saveHandler);
  
  var listPanel = app.createGrid(2, 2);
  listPanel.setStyleAttribute("margin-top", "10px")
  listPanel.setWidth("100%");

  listPanel.setWidget(0, 0, consumerKeyLabel);
  listPanel.setWidget(0, 1, consumerKey);
  listPanel.setWidget(1, 0, consumerSecretLabel);
  listPanel.setWidget(1, 1, consumerSecret);


  // Ensure that all form fields get sent along to the handler
  saveHandler.addCallbackElement(listPanel);
  
  var dialogPanel = app.createFlowPanel();
  dialogPanel.add(listPanel);
  dialogPanel.add(saveButton);
  app.add(dialogPanel);
  doc.show(app);
}


/** Controller to render approvers UI and apply configuration. */
function configure() {
  renderConfigurationDialog();
}
  

  
/**
 * Authorize against Twitter.  This method must be run prior to 
 * clicking any link in a script email.  If you click a link in an
 * email, you will get a message stating:
 * "Authorization is required to perform that action."
 */
function authorize() {
  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());
  ScriptProperties.setProperty("STORED_SCREEN_NAME", o.screen_name);
}
  


// Archive Twitter Status Updates
// coded by @mhawksey 
// Available under Creative Commons Attribution-ShareAlike 2.5 UK: Scotland License

function tw_request(method, api_request){
    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": method,
        "oAuthServiceName": "twitter",
        "oAuthUseToken": "always"
      };
   try {
      var result = UrlFetchApp.fetch(
          "https://api.twitter.com/1/"+api_request,
          requestData);
      var o  = Utilities.jsonParse(result.getContentText());
      Logger.log(o);
    } catch (e) {
      Logger.log(e);
    }
   return o;
}