Import and auto-update RSS feed, and keep history, in Google Spreadsheets

Google Spreadsheets is capable of importing and querying data from other online content, including RSS feeds with the ImportFeed function. However, ImportFeed is limited to retrieving only the 20 most recent items (20 rows of data).  As far as I know, there is no way to get around this limitation. One possible solution is to use ImportFeed to collect RSS data, and as the RSS feed updates, keep older items in the spreadsheet.

The following code is a rough implementation of this solution (and also a great exercise in learning Javascript and Google Apps Script). Also included in the code are options to sort the data (e.g. by the timestamp of each RSS item), convert timestamps to another time zone (e.g. from GMT to local time), as well as an app interface.

There are three scripts :

1. Feed.gs : Contains the RSS retrieval and archival functions.
2. Menu.gs : Displays a menu in the Google Spreadsheets menu bar, containing shortcuts to relevant functions.
3. Interface.gs : Contains the user interface and handler functions.

There are five functions in Feed.gs :

1. manualExample : An example function that manually gets a feed based on the values given inside the function.
2. createTimeTrigger : Removes existing time triggers. Then create a new minutes-based one, calling the function populateSheets.
3. populateSheets : Gets RSS URL, source sheet name, and history sheet name. Retrieves RSS data via ImportFeed. Calls the function updateHistory.
4. updateHistory : Compares the source and history sheets via a key column. If a source item is found that is not in the history sheet, then that item is copied to history. Note that deletions in the source sheet does not affect the history sheet. However, modified items (i.e. modified keys) in the source sheet are treated as new items.
5. convertTimeZone : Converts a column of time data to another time zone. Described in a previous post.

There is one function in Menu.gs :

There are three functions in Interface.gs :

To use this code :

1. Install the code into the Scripts Editor, and refresh the spreadsheet.
2. After a couple seconds, a menu called “RSS Archiver” appears in the spreadsheet menu.
3. Use the interface to create feed and history sheets, with the desired parameters.

Limitations :

1. Accommodates only one feed/history pair.
   1. Cannot edit parameters for existing feed/history. Via the app interface, the user must always add a new feed/history pair.
   2. createTimeTrigger function clears all clock triggers when run. Thus, a new feed/history pair clears the clock triggers for previous feed/history pair.
   3. createTimeTrigger function sets a trigger for the populateSheets function. Probably will cause conflicts if another feed/history pair is added.
2. Integer validation in the app interface accepts zero, when it shouldn’t.
3. Not all options are shown in the app interface.
4. Sorting by time doesn’t seem to work?

Feed.gs

var ss = SpreadsheetApp.getActiveSpreadsheet();

function manualExample() {
  /***
   * An example function that manually gets a feed based on the values below.
   ***/
  var url = "http://www.instructables.com/tag/type-id/rss.xml"
  var rssSource = "RSS_SOURCE";
  var rssHistory = "RSS_HISTORY";
  var options = {primaryKeyCol: 4, leftMostColumn: 2, sortByCol: 4, boolConvertTimezone: true, timeCol: 5};
  populateSheets(url, rssSource, rssHistory, options)
}

function createTimeTrigger(minutesToUpdate, fxToTrigger) {
  /*** 
   * Removes existing time triggers. Then create a new minutes-based one.
   *
   * Arguments:
   * minutesToUpdate -- integer that must be one of 1, 5, 10, 15, or 30
   *
   * References:
   * https://developers.google.com/apps-script/managing_triggers_programmatically
   * http://googleappsdeveloper.blogspot.com/2012/04/introducing-script-service.html
   ***/

  // Default arguments
  var minutesToUpdate = typeof minutesToUpdate !== 'undefined' ? minutesToUpdate : 15;

  // Remove existing clock triggers
  var allTriggers = ScriptApp.getScriptTriggers();
  var clockTriggerId = ScriptProperties.getProperty('clockTriggerId');
  for (i in allTriggers) {
    Logger.log("Trigger ID: " + allTriggers[i].getUniqueId()
               + "\nTrigger handler function: "
               + allTriggers[i].getHandlerFunction()
               + "\nTrigger type: " + allTriggers[i].getEventType()
               + "\n-------------------------");
    if (allTriggers[i].getUniqueId() == clockTriggerId) {
      ScriptApp.deleteTrigger(allTriggers[i]);
    }
  }

  // Create a new clock trigger
  var minutesTimer = ScriptApp.newTrigger(fxToTrigger)
      .timeBased()
      .everyMinutes(minutesToUpdate)
      .create();
  ScriptProperties.setProperty('clockTriggerId', minutesTimer.getUniqueId());
}

function populateSheets(feedURL, sourceSheetName, historySheetName, options) {
  /***
   * Gets RSS data. Calls updateHistory function.
   *
   * Arguments:
   * feedURL -- RSS URL
   * sourceSheetName -- name of worksheet to feed RSS data
   * historySheetName -- name of worksheet to store RSS history
   * options -- object literal containing optional arguments
   */
  var feedFormula = "=ImportFeed(\"" + feedURL + "\",, TRUE,)";
  var sourceSheet = ss.getSheetByName(sourceSheetName);
  var historySheet = ss.getSheetByName(historySheetName);

  sourceSheet.clear();
  sourceSheet.getRange("A1").setFormula(feedFormula);
  updateHistory(sourceSheet, historySheet, options);
}

function updateHistory(sourceSheet, historySheet, options) {
  /***
   * Finds differences between a source and target sheet,
   *   then adds those differences to the target sheet.
   *
   * Arguments:
   * sourceSheet -- sheet with source data
   * historySheet -- sheet with history data, the "target" sheet
   *
   * Options:
   * primaryKeyCol -- default: 4
   *   number (starting at 1) of the column containing a unique identifier per row
   * firstRowPosition -- default: 2
   *   number (starting at 1) of the first row containing data to match   
   * leftMostColumn -- default: 1
   *   number (starting at 1) of the leftmost column containing data to match
   * boolSort -- default: true
   *   if true, sorts the History sheet with data from sortByCol
   * sortByCol -- default: 4
   *   effective only if boolSort is true. number (starting at 1) of column to sort history sheet
   * boolSortAscending -- default: false
   *   effective only if boolSort is true. if true, ascending sort is used
   * boolConvertTimezone -- default: false
   *   if true, converts a column of time data to another timezone
   * timezone -- default: Session.getTimeZone()
   *   effective only if boolConvertTimezone is true. timezone to change time data to
   * timeCol -- default: 4
   *   number (starting at 1) of the column with time data
   *
   * Notes:
   * The firstRowPosition argument is useful for ignoring header row(s).
   * If sorting by time, setting sortAscending to false produces most recent rows at the top
   ***/  

  // Default function arguments
  var SOURCE_FIRST_ROW = 2; // since Feed starts on A1
  var SOURCE_LEFT_COL = 1;

  var options = options || {};
  var primaryKeyCol = typeof options.primaryKeyCol === 'undefined' || isNaN(options.primaryKeyCol) ? 4 : options.primaryKeyCol;
  var firstRowPosition = typeof options.firstRowPosition === 'undefined' || isNaN(options.firstRowPosition) ? 2 : options.firstRowPosition;
  var leftMostColumn = typeof options.leftMostColumn === 'undefined' || isNaN(options.leftMostColumn) ? 1 : options.leftMostColumn;
  var boolSort = typeof options.boolSort === 'undefined' ? true : options.boolSort;
  var sortByCol = typeof options.sortByCol === 'undefined' || isNaN(options.sortByCol) ? 4 : options.sortByCol;
  var boolSortAscending = typeof options.boolSortAscending === 'undefined' ? false : options.boolSortAscending;
  var boolConvertTimezone = typeof options.boolConvertTimezone === 'undefined' ? false : options.boolConvertTimezone;
  var timezone = typeof options.timezone === 'undefined' ? Session.getTimeZone() : options.timezone;
  var timeCol = typeof options.timeCol === 'undefined' || isNaN(options.timezone) ? 4 : options.timeCol;

  // Get source data.
  var sourceLastRow = sourceSheet.getLastRow();   
  var sourceLastColumn = sourceSheet.getLastColumn();
  var sourceData = sourceSheet.getRange(SOURCE_FIRST_ROW, SOURCE_LEFT_COL, sourceLastRow, sourceLastColumn).getValues();

  // Get history data.
  /*  If historyLastRow or historyLastColumn are zero, historySheet.getRange(...) returns an error:
   *  "The coordinates or dimensions of the range are invalid." without this if/else statement.
   */
  if (historySheet.getLastRow() == 0){
    var historyLastRow = sourceLastRow;
    var historyLastColumn = sourceLastColumn ;
  } else {
    var historyLastRow = historySheet.getLastRow();
    var historyLastColumn = historySheet.getLastColumn();
  } 
  var historyData = historySheet.getRange(firstRowPosition, leftMostColumn, historyLastRow, historyLastColumn).getValues();

  // Find differences between sourceData and historyData.
  var updates = [];
  for (i in sourceData) {
    var isNew = true;
    for (j in historyData) {
      // Reminder: arrays start at zero.
      if (sourceData[i][primaryKeyCol-1] == historyData[j][primaryKeyCol-1]) {
        isNew = false;
        break;
      }
    }  
    if (isNew == true) {
      updates.push(sourceData[i]);  
    } 
  }

  // Append new entry to top of history sheet.
  if (updates.length >= 1) {
    historySheet.insertRowsBefore(firstRowPosition, updates.length);
    historySheet.getRange(firstRowPosition, leftMostColumn, updates.length, updates[0].length).setValues(updates);
  }

  // Sort history
  if (boolSort == true) {
    var historyNewLastRow = historySheet.getLastRow();
    var historyNewLastColumn = historySheet.getLastColumn();
    historySheet.getRange(firstRowPosition, leftMostColumn, historyNewLastRow, historyNewLastColumn).sort({column: sortByCol, ascending: boolSortAscending});
  }  

  // Convert timezone
  if (boolConvertTimezone == true) {
    convertTimeZone(historySheet, timezone, firstRowPosition, timeCol);  
  }
}

function convertTimeZone(sheet, timezone, firstRowPosition, timeCol) {
  /***
   * Converts a column of time data from one time zone to another.
   *
   * Arguments:
   * sheet -- worksheet with data
   * timezone -- string containing Olson ID (both main & alternates seem to work)
   * firstRowPosition -- number (starting at 1) of first row of data
   * timeCol -- number (starting at 1) of the column containing time data
   ***/
  var timeData = sheet.getRange(firstRowPosition, timeCol, sheet.getLastRow()-1, 1).getValues();
  var time = Array(timeData.length);
  for (i in timeData){
    // Time Zone List: http://unicode.org/repos/cldr/trunk/docs/design/formatting/zone_log.html
    var formattedTime = Utilities.formatDate(new Date(timeData[i]), timezone, "EEE, d MMM yyyy HH:mm:ss z");
    // This workaround is needed because otherwise time is a 1-D array, and setValues doesn't like 1-D arrays!
    time[i] = new Array(0);
    time[i].push(formattedTime);
  }
    sheet.getRange(firstRowPosition, timeCol, time.length, 1).setValues(time); 
}

Menu.gs

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the readRows() function specified above.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Show Interface",
    functionName: "appInterface"    
  }, {
    name : "Manually Update History",
    functionName: "updateHistory"
  }];
  sheet.addMenu("RSS Archiver", entries);
};

Interface.gs

var ss = SpreadsheetApp.getActiveSpreadsheet();
var db = ScriptDb.getMyDb();

function appUI() {
  /***
   * Creates a user interface to pass values to updateRSS and createTimeTrigger.
   *
   * References:
   * https://developers.google.com/apps-script/uiapp
   * https://sites.google.com/site/scriptsexamples/stock-watcher-reloaded/build-the-user-interface
   * http://googleappsdeveloper.blogspot.com/2011/11/creating-more-responsive-applications.html
   * http://stackoverflow.com/questions/7518586/access-widgets-in-a-verticalpanel-in-google-apps-scripts
   * http://code.google.com/p/re2/wiki/Syntax
   */

  var app = UiApp.createApplication().setTitle("RSS Archiver Interface")
                                     .setWidth(600);

  // Create tab panel
  var panelTab = app.createTabPanel()
                    .setSize("100%", "75%");                          

  // Create grid widgets
     // "Required"
       var txtFeedURL = app.createTextBox().setName('txtFeedURL');
       var txtFeedSheetName = app.createTextBox().setName('txtFeedSheetName');
       var lblFeedWarning = app.createLabel().setId('lblFeedWarning')
                                     .setStyleAttribute("fontSize", "0.8em")
                                     .setVisible(false);
       var txtHistorySheetName = app.createTextBox().setName('txtHistorySheetName');
       var lblHistoryWarning = app.createLabel().setId('lblHistoryWarning')
                                        .setStyleAttribute("fontSize", "0.8em")
                                        .setVisible(false);
       var intKeyCol = app.createTextBox().setName('intKeyCol');
       var lblIntKeyCol = app.createLabel('Default: 4')
                             .setId('lblIntKeyCol')
                             .setStyleAttribute("fontSize", "0.8em");
       var boolAutoUpdate = app.createListBox().setName('boolAutoUpdate')
                                               .addItem('Yes')
                                               .addItem('No');
       var intUpdateInterval = app.createListBox().setName('intUpdateInterval')
                                                  .addItem(1)
                                                  .addItem(5)
                                                  .addItem(10)
                                                  .addItem(15)
                                                  .addItem(30);
     // "Optional"
       var intFirstRow = app.createTextBox().setName('intFirstRow');
       var lblIntFirstRow = app.createLabel('Default: 2').setId('lblIntFirstRow')
                                                         .setStyleAttribute("fontSize", "0.8em");
       var intLeftCol = app.createTextBox().setName('intLeftCol');
       var lbllintLeftCol = app.createLabel('Default: 1').setId('lblintLeftCol')
                                                         .setStyleAttribute("fontSize", "0.8em");
       var boolSortHistorySheet = app.createListBox().setName('boolSortHistorySheet')
                                                     .addItem("Yes")
                                                     .addItem("No");
       var lblBoolSortHistorySheet = app.createLabel('Default: Yes').setId('lblBoolSortHistorySheet')
                                                                    .setStyleAttribute("fontSize", "0.8em");
       var intSortCol = app.createTextBox().setName('intSortCol');
       var lblIntSortCol = app.createLabel('Default: 4').setId('lblIntSortCol')
                                                        .setStyleAttribute("fontSize", "0.8em");
       var boolConvertTimeZone = app.createListBox().setName('boolConvertTimeZone')
                                                    .addItem("Yes")
                                                    .addItem("No");
       var lblBoolConvertTimeZone = app.createLabel('Default: Yes').setId('lblBoolConvertTimeZone')
                                                                   .setStyleAttribute("fontSize", "0.8em");
       var intTimeCol = app.createTextBox().setName('intTimeCol');
       var lblIntTimeCol = app.createLabel('Default: 4').setId('lblIntTimeCol')
                                                        .setStyleAttribute("fontSize", "0.8em");

  // Create input fields
  var panelRequired = app.createFlowPanel(); 
    var gridRequired = app.createGrid(6, 3);
      gridRequired.setWidget(0, 0, app.createLabel('Feed URL:'));
      gridRequired.setWidget(0, 1, txtFeedURL);
      gridRequired.setWidget(1, 0, app.createLabel('Feed Worksheet Name:'));   
      gridRequired.setWidget(1, 1, txtFeedSheetName);
      gridRequired.setWidget(1, 2, lblFeedWarning);
      gridRequired.setWidget(2, 0, app.createLabel('History Worksheet Name:'));
      gridRequired.setWidget(2, 1, txtHistorySheetName);
      gridRequired.setWidget(2, 2, lblHistoryWarning);
      gridRequired.setWidget(3, 0, app.createLabel('Key Column #:'));
      gridRequired.setWidget(3, 1, intKeyCol);
      gridRequired.setWidget(3, 2, lblIntKeyCol); 
      gridRequired.setWidget(4, 0, app.createLabel('Auto-Update?'));
      gridRequired.setWidget(4, 1, boolAutoUpdate);
      gridRequired.setWidget(5, 0, app.createLabel('Update Interval (minutes)'));
      gridRequired.setWidget(5, 1, intUpdateInterval);
  var panelOptional = app.createFlowPanel();
    var gridOptional = app.createGrid(6, 3);
      gridOptional.setWidget(0, 0, app.createLabel('First Row #:'));
      gridOptional.setWidget(0, 1, intFirstRow);
      gridOptional.setWidget(0, 2, lblIntFirstRow);
      gridOptional.setWidget(1, 0, app.createLabel('Leftmost Column #:'))
      gridOptional.setWidget(1, 1, intLeftCol);
      gridOptional.setWidget(1, 2, lbllintLeftCol);
      gridOptional.setWidget(2, 0, app.createLabel('Sort History Sheet?'));
      gridOptional.setWidget(2, 1, boolSortHistorySheet);
      gridOptional.setWidget(2, 2, lblBoolSortHistorySheet);
      gridOptional.setWidget(3, 0, app.createLabel('Column # to Sort By:'));
      gridOptional.setWidget(3, 1, intSortCol);
      gridOptional.setWidget(3, 2, lblIntSortCol);
      gridOptional.setWidget(4, 0, app.createLabel('Convert Time Zone?'));
      gridOptional.setWidget(4, 1, boolConvertTimeZone);
      gridOptional.setWidget(4, 2, lblBoolConvertTimeZone);
      gridOptional.setWidget(5, 0, app.createLabel('Column # of Time Column'));
      gridOptional.setWidget(5, 1, intTimeCol);
      gridOptional.setWidget(5, 2, lblIntTimeCol);

  // Create horizontal panel for warnings (save for future use)
  var panelWarnings = app.createHorizontalPanel();

    // Create validation handlers for integer inputs
  var validnotIntKeyCol = app.createClientHandler()
      .validateNotInteger(intKeyCol)
      .forTargets(lblIntKeyCol).setStyleAttribute("color", "red")
      .forTargets(lblIntKeyCol).setText('Default: 4 - Please enter a non-zero positive integer.');
  var validnotIntFirstRow = app.createClientHandler()
      .validateNotInteger(intFirstRow)
      .forTargets(lblIntFirstRow).setStyleAttribute("color", "red")
      .forTargets(lblIntFirstRow).setText('Default: 2 - Please enter a non-zero positive integer.');
  var validnotintLeftCol = app.createClientHandler()
      .validateNotInteger(intLeftCol)
      .forTargets(lbllintLeftCol).setStyleAttribute("color", "red")
      .forTargets(lbllintLeftCol).setText('Default: 1 - Please enter a non-zero positive integer.');
  var validnotIntSortCol = app.createClientHandler()
      .validateNotInteger(intSortCol)
      .forTargets(lblIntSortCol).setStyleAttribute("color", "red")
      .forTargets(lblIntSortCol).setText('Default: 4 - Please enter a non-zero positive integer.');
  var validnotIntTimeCol = app.createClientHandler()
      .validateNotInteger(intTimeCol)
      .forTargets(lblIntTimeCol).setStyleAttribute("color", "red")
      .forTargets(lblIntTimeCol).setText('Default: 4 - Please enter a non-zero positive integer.');

  var validyesIntKeyCol = app.createClientHandler()
      .validateMatches(intKeyCol, "^[1-9]*$")
      .forTargets(lblIntKeyCol).setStyleAttribute("color", "black")
      .forTargets(lblIntKeyCol).setText('Default: 4');
  var validyesIntFirstRow = app.createClientHandler()
      .validateMatches(intFirstRow, "^[1-9]*$")
      .forTargets(lblIntFirstRow).setStyleAttribute("color", "black")
      .forTargets(lblIntFirstRow).setText('Default: 2');
  var validyesintLeftCol = app.createClientHandler()
      .validateMatches(intLeftCol, "^[1-9]*$")
      .forTargets(lbllintLeftCol).setStyleAttribute("color", "black")
      .forTargets(lbllintLeftCol).setText('Default: 1');
  var validyesIntSortCol = app.createClientHandler()
      .validateMatches(intSortCol, "^[1-9]*$")
      .forTargets(lblIntSortCol).setStyleAttribute("color", "black")
      .forTargets(lblIntSortCol).setText('Default: 4');
  var validyesIntTimeCol = app.createClientHandler()
      .validateMatches(intTimeCol, "^[1-9]*$")
      .forTargets(lblIntTimeCol).setStyleAttribute("color", "black")
      .forTargets(lblIntTimeCol).setText('Default: 4');

  // Create horizonal panel for buttons
  var panelButtons = app.createHorizontalPanel();

  // Create handler for save button
  var buttonSubmit = app.createButton('Submit');
  var handlerSubmit = app.createServerHandler('submitUI');
  handlerSubmit.addCallbackElement(panelTab);
  buttonSubmit.addClickHandler(handlerSubmit);

  // Create handler for cancel button
  var buttonCancel = app.createButton('Cancel'); 
  var handlerCancel = app.createServerHandler('closeUI');
  buttonCancel.addClickHandler(handlerCancel);

  // Add elements into app interface
  panelRequired.add(gridRequired);
  panelOptional.add(gridOptional);

  panelTab.add(panelRequired, "Required");
  panelTab.add(panelOptional, "Optional");
  panelTab.selectTab(0);

    // "Required" validators
    intKeyCol.addKeyUpHandler(validnotIntKeyCol);
    intFirstRow.addKeyUpHandler(validnotIntFirstRow);
    intLeftCol.addKeyUpHandler(validnotintLeftCol);
    intLeftCol.addKeyUpHandler(validnotIntSortCol);
    intTimeCol.addKeyUpHandler(validnotIntTimeCol);

    // "Optional" validators
    intKeyCol.addKeyUpHandler(validyesIntKeyCol);
    intFirstRow.addKeyUpHandler(validyesIntFirstRow);
    intLeftCol.addKeyUpHandler(validyesintLeftCol);
    intLeftCol.addKeyUpHandler(validyesIntSortCol);
    intTimeCol.addKeyUpHandler(validyesIntTimeCol);

  panelButtons.add(buttonSubmit);
  panelButtons.add(buttonCancel);

  app.add(panelTab);
  app.add(panelWarnings);
  app.add(panelButtons);

  // Return the app interface
  ss.show(app);
}

function submitUI(e) {
  /***
   * A server handler for the "Save" button in the app interface.
   * Parses values from interface and calls createTimeTrigger and populateSheets.
   ***/
  var app = UiApp.getActiveApplication();
  var allSheets = ss.getSheets();
  var DECIMAL = 10;

  // Required parameters
  var feedURL = e.parameter.txtFeedURL;
  var feedSheetName = e.parameter.txtFeedSheetName;
  var historySheetName = e.parameter.txtHistorySheetName;
  var intKeyCol = parseInt(e.parameter.intKeyCol, DECIMAL);

  // Optional parameters
  var intFirstRow = parseInt(e.parameter.intFirstRow, DECIMAL);
  var intLeftCol = parseInt(e.parameter.intLeftCol, DECIMAL);
  var boolSortHistorySheet = (e.parameter.boolSortHistorySheet === "Yes");
  var intSortCol = parseInt(e.parameter.intSortCol, DECIMAL);
  var boolConvertTimeZone = (e.parameter.boolConvertTimeZone === "Yes");
  var intTimeCol = parseInt(e.parameter.intTimeCol, DECIMAL);

  // Show warning label for empty sheet names, or entering a name of an existing sheet
  if ( feedSheetName == '' || historySheetName == '' ) {
    if (feedSheetName == '') {
      app.getElementById('lblFeedWarning').setText('Feed Sheet Name is missing. Please choose a name.')
                                          .setVisible(true).setStyleAttribute("color", "red");
    }
    if (historySheetName == '') {
      app.getElementById('lblHistoryWarning').setText('History Sheet Name is missing. Please choose a name.')
                                             .setVisible(true).setStyleAttribute("color", "red");
    }
    return app;
  } else {
    for (i in allSheets) {
      if (allSheets[i].getName() == feedSheetName){
        app.getElementById('lblFeedWarning').setText( '"' + feedSheetName + '" already exists. Please choose another name.')
                                            .setVisible(true).setStyleAttribute("color", "red");
      }
      if ( allSheets[i].getName() == historySheetName ) {
        app.getElementById('lblHistoryWarning').setText( '"' + historySheetName + '" already exists. Please choose another name.')
                                               .setVisible(true).setStyleAttribute("color", "red");
      }
    }
    // return app;
  }

  // Insert new sheets for feed and history
  var SHEET_POSITION_1 = 0;
  var SHEET_POSITION_2 = 1;
  ss.insertSheet(feedSheetName, SHEET_POSITION_1);
  ss.insertSheet(historySheetName, SHEET_POSITION_2);

  // Create time trigger if desired
  if (e.parameter.boolAutoUpdate == "Yes") {
    createTimeTrigger(parseInt(e.parameter.intUpdateInterval, DECIMAL), "populateSheets");
  }

  // Call populateSheets function
  var options = {primaryKeyCol: intKeyCol,
                 firstRowPosition: intFirstRow,
                 leftMostColumn: intLeftCol,
                 boolSort: boolSortHistorySheet,
                 sortByCol: intSortCol,
                 boolConvertTimezone: boolConvertTimeZone,
                 timeCol: intTimeCol};
  populateSheets(feedURL, feedSheetName, historySheetName, options);

  return app;
}

function closeUI(e) {
  /***
   * Closes the app interface.
   ***/
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

Changelog

13 August 2012

1. Added client-side validation to app interface. Rearranged widgets to give each widget a name (rather than create each one as each grid cell is generated).
   1. Note: this added 100 additional lines (previously 180 lines, now 280!) since each validator took about a dozen lines per widget.
2. Fixed bug to handle default option values, when app interface passes NaN’s to populateSheets.
3. Other minor bug fixes, e.g. reducing use of magic numbers.

10 August 2012

1. Add app interface.  Add tabs for required and optional parameters.
2. Add ability to create new worksheets. Check whether worksheet with that name exists (before potentially overwriting data?).
3. Revised menu entries.
4. Reorganized Feed.gs functions to accommodate user input (e.g., via the app interface), rather than hard-coded arguments.
5. Some bug fixes.