gistfile1.js

/*   
   Copyright 2013 Martin Hawksey

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
*/

/**
 * 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() {  
  if (!isConfigured()){
    Browser.msgBox("Twitter API not configured");
    return;
  }
  try {
    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("https://api.twitter.com/1.1/account/verify_credentials.json", requestData);
    if (result.getResponseCode() == 200){
      var resp = Utilities.jsonParse(result.getContentText());
      if (resp.screen_name != ""){
        ScriptProperties.setProperty("authenticated", "true");
      }
    }
  } catch (e) {
    Browser.msgBox(e.message);
  }
}

/**
 * function to update data files used in Twitter interface
 */
function updateArchive(){
  if (!isConfigured()){
    Browser.msgBox("Twitter API not configured");
    return;
  }
  if (!isAuthenticated()){
    Browser.msgBox("Twitter access has not been authenticated");
    return;
  }
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("Log");
  var js = getJSFolder(getArchiveFolder()); // get the data/js folder 
  var extraTweets = 0; // payload includes overall count which we'll update
  var result = "";
  if (js){
    // search the js folder for user/payload details and index file 
    // (as they are javascript we can fetch and eval)
    // ADDED fix for find title issue found by @cryptecks http://mashe.hawksey.info/2013/01/sync-twitter-archive-with-google-drive/comment-page-2/#comment-158100
    var user_details_file = js.getFilesByName("user_details.js").next();
    eval(user_details_file.getBlob().getDataAsString());
    var payload_details_file = js.getFilesByName("payload_details.js").next();
    eval(payload_details_file.getBlob().getDataAsString());
    var tweet_index_file = js.getFilesByName("tweet_index.js").next();
    eval(tweet_index_file.getBlob().getDataAsString());
    
    // get deatils of last file in archive
    var lastFileMeta = tweet_index[0];
    var file_name = lastFileMeta.file_name.replace("data/js/tweets/","");
    var var_name = lastFileMeta.var_name;
    
    // prepare Grailbird which will hold last set of tweets archived
    var Grailbird = {};
    Grailbird.data = {};
    Grailbird.data[var_name] = [];
    
    // navigate to data/js/tweets/ and eval last archive file
    var tweetsFolder = getSubFolder(js, "tweets");
    var tweet_file = tweetsFolder.getFilesByName(file_name).next();
    eval(tweet_file.getBlob().getDataAsString());
    
    // get id ofthe last tweet in archive
    var sinceid = Grailbird.data[var_name][0]['id_str'];
    // based on this get new data from Twitter API
    var newData = getNewStatusUpdates(sinceid, user_details.screen_name); 
   
    // new data is returned in bins for each monthnewest first, we want to process oldest 1st so flip the keys
    var keys = [];
    for (var k in newData) {
        keys.unshift(k);
    }
    // for each date bin we need to either add to existing file or if new months add new data files
    for (var c = keys.length, n = 0; n < c; n++) {
      var i = keys[n];
      if (i == var_name){ // handling existing months
        Grailbird.data[var_name] = newData[i].concat(Grailbird.data[var_name]); // add new data 
        tweet_file.setContent("Grailbird.data."+var_name+" = \n"+ JSON.stringify(Grailbird.data[var_name], null, '\t')); // replace old file
        extraTweets += newData[i].length; // running total of inserts
        tweet_index[0].tweet_count = Grailbird.data[var_name].length; // update tweet_index count for month
      } else { // creating files for new months
        var new_tweet_filename = i.toString().replace("tweets_","")+".js"; // recycle date bin name for filename maintaining tweets_yyyy_dd.js convention
        var new_tweet_file = tweet_file.makeCopy(new_tweet_filename, tweetsFolder); // make a copy of an exsting file (could have blobbed a new file and set mime)
        new_tweet_file.setContent("Grailbird.data."+i+" = \n"+ JSON.stringify(newData[i], null, '\t')); // replace content with new data
        extraTweets += newData[i].length; // running total of inserts
        var new_tweet_index_meta = {}; // build a new tweet_index record
        var tweetdate = new Date(newData[i][0].created_at);
        new_tweet_index_meta = { file_name: "data/js/tweets/"+new_tweet_filename,
                                year: tweetdate.getYear(),
                                var_name: i,
                                tweet_count: newData[i].length,
                                month: +Utilities.formatDate(tweetdate, "GMT", "MM") };
        tweet_index.unshift(new_tweet_index_meta); // insert at the beginning                           
      }
    }
     
    tweet_index_file.setContent("var tweet_index = "+JSON.stringify(tweet_index, null, '\t')); // rewrite the tweet_index
    payload_details.tweets = payload_details.tweets + extraTweets; // update payload meta
    payload_details.created_at = Utilities.formatDate(new Date(), "GMT", "E MMM dd HH:mm:ss Z yyyy"); // new date stamp
    payload_details_file.setContent("var payload_details = "+JSON.stringify(payload_details, null, '\t')); // update payload file
    result = extraTweets;
    doc.toast("Added "+extraTweets+" tweets to your archive");
  } else {
    result = "Couldn't find JS folder";
  }
  sheet.insertRowAfter(1);
  sheet.getRange(2, 1, 1, 2).setValues([[new Date(), extraTweets]]);
}

/**
 * Get new status updates from Twitter 
 * @param {String} sinceid of last tweet in archive.
 * @param {String} screen_name of archived account.
 * @return {Object} json object of Twitter updates binned in month objects yyyy_mm.
 */
function getNewStatusUpdates(sinceid, screen_name){
  // some parameters used in the query
  var params = {  screen_name: screen_name,
                  count: 200,
                  since_id: sinceid,
                  include_rts: true };
  
  var api_request = buildUrl("statuses/user_timeline.json", params); // make url
  // some variables
  var page = 1;
  var done = false;
  var output = {};
  var max_id = "";
  var max_id_url = "";
  while(!done){
    var data = tw_request("GET", api_request+max_id_url); // get the data from twitter
    if (data.length>0){ // if data returned
      if (data.length == 1 && data[0].id_str == max_id){ // catching if we've reached last new tweet
        done = true;
        break;
      }
      for (i in data){ // for the data returned we put in montly bins ready for writting/updating files
        if(data[i].id_str != max_id){
          var timestamp = new Date(data[i].created_at);
          var bin = "tweets_"+Utilities.formatDate(timestamp, "GMT", "yyyy_MM");
          if (output[bin] == undefined) output[bin] = []; // if bin hasn't been used yet make it
            output[bin].push(data[i]); //push data to date bin
        }
      }
      if (data[data.length-1].id_str != max_id) { // more bad code trying to work out if next call with a max_id
        max_id = data[data.length-1].id_str;
        max_id_url = "&max_id="+max_id;
      }
    } else { // if not data break the loop
      done = true;
    }
    page ++
    if (page > 16) done = true; // if collected 16 pages (the max) break the loop
  }
  return output;
}

function onOpen() {  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [];
  // When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is executed.
  menuEntries.push({name: "Set Archive Path", functionName: "renderSetArchivePath"});
  menuEntries.push({name: "API Authentication", functionName: "renderAPIConfigurationDialog"});
  menuEntries.push(null); // line separator
  menuEntries.push({name: "Update Archive Now", functionName: "updateArchive"});
  menuEntries.push({name: getTriggerStatus(), functionName: "toggleTrigger"});
  ss.addMenu("Sync Twitter Setup", menuEntries);
}

/**
 * Build a querystring from a object http://stackoverflow.com/a/5340658/1027723
 * @param {String} base url.
 * @param {Object} objects to add to string.
 * @return {String} url.
 */
function buildUrl(url, parameters){
  var qs = "";
  for(var key in parameters) {
    var value = parameters[key];
    qs += encodeURIComponent(key) + "=" + encodeURIComponent(value) + "&";
  }
  if (qs.length > 0){
    qs = qs.substring(0, qs.length-1); //chop off last "&"
    url = url + "?" + qs;
  }
  return url;
}

/**
 * Toggles the script trigger to refresh archive 
 */
function toggleTrigger(){
var test = getTriggerID();
  if (getTriggerID() == "none"  || getTriggerID() == null){ // add trigger
    var dailyTrigger = ScriptApp.newTrigger("updateArchive")
        .timeBased()
        .everyDays(1)
        .atHour(0)
        .create();
    setTriggerID(dailyTrigger.getUniqueId());
    onOpen();
  } else {
    var triggers = ScriptApp.getScriptTriggers();
    for (var i = 0; i < triggers.length; i++) {
      if (triggers[i].getUniqueId() == getTriggerID()){
        ScriptApp.deleteTrigger(triggers[i]);
        setTriggerID("none");
        onOpen();
        break;
      }
    }
  }
  
}
/**
 * Gets trigger menu option text
 * @return {String} Stats text for menu.
 */
function getTriggerStatus(){
  if (getTriggerID() == "none" || getTriggerID() == null) return "Turn Auto-Refresh On";
  return "Turn Auto-Refresh Off"
}

/**
 * @param {String} set a trigger id.
 */
function setTriggerID(id){
  ScriptProperties.setProperty("triggerID", id);
}

/**
 * @return {String} get a trigger id.
 */
function getTriggerID(){
  return ScriptProperties.getProperty("triggerID");
}

/**
 * oAuth request to Twitter API.
 * @param {String} method get, post, put.
 * @param {String} api request to twitter.
 * @return {Object} json response from twitter.
 */
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.1/"+api_request, requestData);
      if (result.getResponseCode() === 200){
        return Utilities.jsonParse(result.getContentText());
      }
    } catch (e) {
      Browser.msgBox(e.message);
      return false;
    }
}

/**
 * Get the subfolder with passed name.
 * @param {String} get the js folder with path data/js/.
 * @return {Object} folder.
 */
function getJSFolder(folderID){
  try {
    var folder = DriveApp.getFolderById(folderID);
    var data = getSubFolder(folder, "data");
    return getSubFolder(data, "js");
  } catch (e){
    Browser.msgBox(e.message);
    return false;
  }
}

/**
 * Get the subfolder with passed name.
 * @param {Object} folder.
 * @param {String} sub folder name to return.
 * @return {Object} folder.
 */
function getSubFolder(folder, name){
  var sub = folder.getFolders();
  while (sub.hasNext()) {
     var folder = sub.next();
     if (folder.getName() == name)
       return folder;
   }
  return false;
}

/**
 * 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 ARCHIVE_FOLDER_PROPERTY_NAME = "twitterFolderID";

/**
 * @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.trim());
}

/**
 * @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.trim());
}

/**
 * @return {string} Folder ID for archive location.
 */
function getArchiveFolder() {
  var folderID = ScriptProperties.getProperty(ARCHIVE_FOLDER_PROPERTY_NAME);
  if (folderID == null) {
    folderID = "";
  }
  return folderID;
}

/**
 * @param {string} Set Folder ID for archive location.
 */
function setArchiveFolder(id) {
  ScriptProperties.setProperty(ARCHIVE_FOLDER_PROPERTY_NAME, id.trim());
  if (id != ""){
    setArchiveLocationCell("https://googledrive.com/host/"+id);
  } else {
    setArchiveLocationCell("Folder ID not entered yet");
  }
}

/**
 * @return {boolean} if authenticated.
 */
function isAuthenticated() {
  var auth = ScriptProperties.getProperty("authenticated");
  if (auth == "true") {
    return true;
  }
  return false;
}

function setArchiveLocationCell(text){
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = doc.getSheetByName("Readme");
  sheet.getRange("A14").setValue(text);
}


/**
 * Twitter archive path config
 *         
 */
function renderSetArchivePath() {
// modified from Twitter Approval Manager 
// http://code.google.com/googleapps/appsscript/articles/twitter_tutorial.html
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle(
      "Twitter Archive Path Configuration").setHeight(250).setWidth(420);
  app.setStyleAttribute("padding", "10px");

  //var dialog = app.loadComponent("GUIComponent");
  var dialogPanel = app.createFlowPanel().setWidth("400px");
  var label1 = app.createLabel("1. Extract and upload the .zip archive to a folder in Google Drive").setStyleAttribute("paddingBottom", "10px");
  var label2 = app.createLabel("2. Open the folder in your web browser and copy the folder id from the url in the address bar (see setup video for help)");
//("<strong>hello</strong><ul><li>one</li></ul>");
  dialogPanel.add(label1);
  dialogPanel.add(label2);

  var archivePathLabel = app.createLabel("Folder ID of Twitter Archive:");
  var archivePathID = app.createTextBox();
  archivePathID.setName("archivePathID");
  archivePathID.setWidth("90%");
  archivePathID.setText(getArchiveFolder());
  
  var saveHandler = app.createServerClickHandler("saveFolderID");
  var saveButton = app.createButton("Save Configuration", saveHandler);
  
  var listPanel = app.createGrid(1, 2);
  listPanel.setStyleAttribute("margin-top", "10px")
  listPanel.setWidth("100%");

  listPanel.setWidget(0, 0, archivePathLabel);
  listPanel.setWidget(0, 1, archivePathID);

  // Ensure that all form fields get sent along to the handler
  saveHandler.addCallbackElement(listPanel);

  dialogPanel.add(listPanel);
  dialogPanel.add(saveButton);
  app.add(dialogPanel);
  doc.show(app);
}

/**
 * Twitter configuration window
 *         
 */
function renderAPIConfigurationDialog() {
// modified from Twitter Approval Manager 
// http://code.google.com/googleapps/appsscript/articles/twitter_tutorial.html
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  var app = UiApp.createApplication().setTitle(
      "Twitter API Authentication Configuration").setHeight(400).setWidth(420);
  app.setStyleAttribute("padding", "10px");

  //var dialog = app.loadComponent("GUIComponent");
  var dialogPanel = app.createFlowPanel().setWidth("400px");
  var label1 = app.createLabel("1. Register for an API key with Twitter at http://dev.twitter.com/apps/new (if you've already registered a Google Spreadsheet/Twitter mashup you can reuse your existing Consumer Key/Consumer Secret).  In the form these are the important bits: ").setStyleAttribute("paddingBottom", "10px");
  var label2 = app.createLabel(" - Application Website = anything you like").setStyleAttribute("textIndent", "30px");
  var label3 = app.createLabel(" - Application Type = Browser").setStyleAttribute("textIndent", "30px");
  var label4 = app.createLabel(" - Callback URL = https://spreadsheets.google.com/macros").setStyleAttribute("textIndent", "30px");
  var label5 = app.createLabel(" - Default Access type = Read-only ").setStyleAttribute("textIndent", "30px").setStyleAttribute("paddingBottom", "10px");
  var label6 = app.createLabel("2. Once finished filling in the form and accepting Twitter's terms and conditions you'll see a summary page which includes a Consumer Key and Consumer Secret which you need to enter below").setStyleAttribute("paddingBottom", "10px");
  var label7 = app.createLabel("3. When your Key and Secret are saved you need to open Tools > Script Editor ... and run the 'authorize' function").setStyleAttribute("paddingBottom", "10px");
//("<strong>hello</strong><ul><li>one</li></ul>");
  dialogPanel.add(label1);
  dialogPanel.add(label2);
  dialogPanel.add(label3);
  dialogPanel.add(label4);
  dialogPanel.add(label5);
  dialogPanel.add(label6);
  dialogPanel.add(label7);

  var consumerKeyLabel = app.createLabel("Twitter OAuth Consumer Key:");
  var consumerKey = app.createTextBox();
  consumerKey.setName("consumerKey");
  consumerKey.setWidth("90%");
  consumerKey.setText(getConsumerKey());
  var consumerSecretLabel = app.createLabel("Twitter OAuth Consumer Secret:");
  var consumerSecret = app.createTextBox();
  consumerSecret.setName("consumerSecret");
  consumerSecret.setWidth("90%");
  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(helpLabel);
  dialogPanel.add(listPanel);
  dialogPanel.add(saveButton);
  app.add(dialogPanel);
  doc.show(app);
}

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

/** Retrieve config params from the UI and store them. */
function saveFolderID(e) {
  setArchiveFolder(e.parameter.archivePathID);
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

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