ianlewis/csv_import_magic.js

## csv_import_magic.js
// vim: ft=javascript:
/*jslint sloppy: true, vars: true, white: true, nomen: true, browser: true  */
/*global SpreadsheetApp, UiApp, UrlFetchApp, Utilities */
/*
 * A script to automate requesting data from an external url that outputs CSV data.
 *
 * Adapted from the Google Analytics Report Automation (magic) script.
 * @author nickski15@gmail.com (Nick Mihailovski)
 * @author ianmlewis@gmail.com (Ian Lewis)
*/

var CSV_CONFIG = 'csvconfig';

/* =========== Logging ======================= */

/**
 * The output text that should be displayed in the log.
 * @private.
 */
var logArray_;

/**
 * Clears the in app log.
 * @private.
 */
function setupLog_() {
  logArray_ = [];
}

/**
 * Returns the log as a string.
 * @returns {string} The log.
 */
function getLog_() {
  return logArray_.join('\n');
}

/**
 * Appends a string as a new line to the log.
 * @param {String} value The value to add to the log.
 */
function log_(value) {
  logArray_.push(value);

  var app = UiApp.getActiveApplication();
  var foo = app.getElementById('log');
  foo.setText(getLog_());
}

/**
 * Displays the log in memory to the user.
 */
function displayLog_() {
  var uiLog = UiApp.createApplication().setTitle('Report Status').setWidth(400).setHeight(500);
  var panel = uiLog.createVerticalPanel();
  uiLog.add(panel);

  var txtOutput = uiLog.createTextArea().setId('log').setWidth('400').setHeight('500').setValue(getLog_());
  panel.add(txtOutput);

  SpreadsheetApp.getActiveSpreadsheet().show(uiLog);
}

function getOrCreateSheet_(sheet_name) {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = activeSpreadsheet.getSheetByName(sheet_name);
  if (!sheet) {
    sheet = activeSpreadsheet.insertSheet(sheet_name, 0);
  }
  return sheet;
}

/**
 * Create a Menu when the script loads. Adds a new csvconfig sheet if
 * one doesn't exist.
 */
function onOpen() {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  // Add a menu.
  activeSpreadsheet.addMenu(
    "CSV Report", [
      {name: "Get Data", functionName: "getCSVData"},
      {name: "Create Report", functionName: "createCSVReport"}
    ]
  );

  getOrCreateSheet_(CSV_CONFIG);
}

/**
 * Returns the trailing number on a string. For example the
 * input: xxxx555 will return 555. Inputs with no trailing numbers
 * return undefined. Trailing whitespace is not ignored.
 * @param {string} input The input to parse.
 * @resturns {number} The trailing number on the input as a string.
 *     undefined if no number was found.
 */
function getTrailingNumber_(input) {
  // Match at one or more digits at the end of the string.
  var pattern = /(\d+)$/;
  var result = pattern.exec(input);
  if (result) {
    // Return the matched number.
    return result[0];
  }

  return undefined;
}

/**
 * Returns the values from 2 columns from the csvconfig sheet starting at
 * colIndex, as key-value pairs. Key-values are only returned if they do
 * not contain the empty string or have a boolean value of false.
 * If the key is start-date or end-date and the value is an instance of
 * the date object, the value will be converted to a string in yyyy-MM-dd.
 * If the key is start-index or max-results and the type of the value is
 * number, the value will be parsed into a string.
 * @param {number} colIndex The column index to return values from.
 * @return {object} The values starting in colIndex and the following column
       as key-value pairs.
 */
function getConfigsStartingAtCol_(sheet, colIndex) {
  var config = {}, rowIndex, key, value;
  var range = sheet.getRange(1, colIndex, sheet.getLastRow(), 2);

  // The first cell of the first column becomes the name of the query.
  config.query = range.getCell(1,1).getValue();

  for (rowIndex = 2; rowIndex <= range.getLastRow(); ++rowIndex) {
    key = range.getCell(rowIndex, 1).getValue();
    value = range.getCell(rowIndex, 2).getValue();
    if (value) {
      config[key] = value;
    }
  }

  return config;
}

/**
 * Returns an array of config objects. This reads the csvconfig sheet
 * and tries to extract adjacent column names that end with the same
 * number. For example Names1 : Values1. Then both columns are used
 * to define key-value pairs for the coniguration object. The first
 * column defines the keys, and the adjacent column values define
 * each keys values.
 * @param {Sheet} The csvconfig sheet from which to read configurations.
 * @returns {Array} An array of API query configuration object.
 */
function getConfigs_(sheet) {

    var configs = [], colIndex;
    // There must be at least 2 columns.
    if (sheet.getLastColumn() < 2) {
        return configs;
    }

    var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
    var firstColValue, firstColNum, secondColValue, secondColNum;

    // Test the name of each column to see if it has an adjacent column that ends
    // in the same number. ie xxxx555 : yyyy555.
    // Since we check 2 columns at a time, we don't need to check the last column,
    // as there is no second column to also check.
    for (colIndex = 1; colIndex <= headerRange.getNumColumns() - 1; ++colIndex) {
        firstColValue = headerRange.getCell(1, colIndex).getValue();
        firstColNum = getTrailingNumber_(firstColValue);

        secondColValue = headerRange.getCell(1, colIndex + 1).getValue();
        secondColNum = getTrailingNumber_(secondColValue);

        if (firstColNum && secondColNum && firstColNum === secondColNum) {
            configs.push(getConfigsStartingAtCol_(sheet, colIndex));
        }
    }

    return configs;
}

// This will parse a delimited string into an array of
// arrays. The default delimiter is the comma, but this
// can be overriden in the second argument.
function parseCsvResponse_( strData, strDelimiter ){
    // Check to see if the delimiter is defined. If not,
    // then default to comma.
    strDelimiter = (strDelimiter || ",");
    strData = strData.replace(/^\s+|\s+$/g, '');

    // Create a regular expression to parse the CSV values.
    var objPattern = new RegExp(
        (
            // Delimiters.
            "(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

            // Quoted fields.
            "(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

            // Standard fields.
            "([^\"\\" + strDelimiter + "\\r\\n]*))"
        ),
        "gi"
        );


    // Create an array to hold our data. Give the array
    // a default empty first row.
    var arrData = [[]];

    // Create an array to hold our individual pattern
    // matching groups.
    var arrMatches = null;


    // Keep looping over the regular expression matches
    // until we can no longer find a match.
    while (arrMatches = objPattern.exec( strData )){

        // Get the delimiter that was found.
        var strMatchedDelimiter = arrMatches[ 1 ], strMatchedValue;

        // Check to see if the given delimiter has a length
        // (is not the start of string) and if it matches
        // field delimiter. If id does not, then we know
        // that this delimiter is a row delimiter.
        if (
            strMatchedDelimiter.length &&
            (strMatchedDelimiter !== strDelimiter)
            ){

            // Since we have reached a new row of data,
            // add an empty row to our data array.
            arrData.push( [] );

        }

        // Now that we have our delimiter out of the way,
        // let's check to see which kind of value we
        // captured (quoted or unquoted).
        if (arrMatches[ 2 ]){

            // We found a quoted value. When we capture
            // this value, unescape any double quotes.
            strMatchedValue = arrMatches[ 2 ].replace(
                new RegExp( "\"\"", "g" ),
                "\""
                );

        } else {

            // We found a non-quoted value.
            strMatchedValue = arrMatches[ 3 ];

        }

        // Now that we have our value string, let's add
        // it to the data array.
        arrData[ arrData.length - 1 ].push( strMatchedValue );
    }

    // Return the parsed data.
    return arrData;
}


function populateSheetWithCSV_(sheet, csvUrl, user, pw) {

  // request the CSV!
  var headers = {};
  if (user && pw) {
    headers = {
      // use basic auth
      'Authorization': 'Basic ' + Utilities.base64Encode(
        user + ':' + pw, Utilities.Charset.UTF_8)
    };
  }
  var resp = UrlFetchApp.fetch(csvUrl, {
    headers: headers,
    validateHttpsCertificates: false // No valid certificate yet :(
  });

  // parse the response as a CSV
  var csvContent = parseCsvResponse_(resp.getContentText());

  // clear everything in the sheet
  sheet.clearContents().clearFormats();

  // set the values in the sheet (as efficiently as we know how)
  sheet.getRange(
    1, 1,
    csvContent.length, /* rows */
    csvContent[0].length /* columns */).setValues(csvContent);

}


/*
Gets the CSV data for each report and loads it into the appropriate sheet.
*/
function getCSVData(e) {
    setupLog_();
    var now = new Date(), i, config, configName, sheet;
    log_('Running on: ' + now);

    var configs = getConfigs_(getOrCreateSheet_(CSV_CONFIG));

    if (!configs.length) {
        log_('No report configurations found');
    } else {
        log_('Found ' + configs.length + ' report configurations.');

      for (i = 0; config = configs[i]; ++i) {
          configName = config.query;
          if (config['sheet-name']) {
              if (config.url) {
                  try {
                      log_('Getting CSV: ' + configName);
                      sheet = getOrCreateSheet_(config['sheet-name']);
                      populateSheetWithCSV_(sheet, config.url, config['http-username'], config['http-password']);
                  } catch (error) {
                      log_('Error executing ' + configName + ': ' + error.message);
                  }
              } else {
                  log_('No URL found: ' + configName);
              }
          } else {
              log_('No sheet-name found: ' + configName);
          }
      }


    }
    log_('Script done');

    // Update the user about the status of the queries.
    if( e === undefined ) {
      displayLog_();
    }
}

/**
 * Returns 1 greater than the largest trailing number in the header row.
 * @param {Object} sheet The sheet in which to find the last number.
 * @returns {Number} The next largest trailing number.
 */
function getLastNumber_(sheet) {
  var maxNumber = 0;

  var lastColIndex = sheet.getLastColumn();

  if (lastColIndex > 0) {
    var range = sheet.getRange(1, 1, 1, lastColIndex);

    for (var colIndex = 1; colIndex < sheet.getLastColumn(); ++colIndex) {
      var value = range.getCell(1, colIndex).getValue();
      var headerNumber = getTrailingNumber_(value);
      if (headerNumber) {
        var number = parseInt(headerNumber, 10);
        maxNumber = number > maxNumber ? number : maxNumber;
      }
    }
  }
  return maxNumber + 1;
}

/**
 * Adds a CSV Report configuration to the spreadsheet.
 */
function createCSVReport() {
  var sheet = getOrCreateSheet_(CSV_CONFIG);
  var headerNumber = getLastNumber_(sheet);
  var config = [
    ["query" + headerNumber, "value" + headerNumber],
    ['url', ''],
    ['http-username', ''],
    ['http-password', ''],
    ['sheet-name', '']];

  sheet.getRange(1, sheet.getLastColumn() + 1, config.length, 2).setValues(config);
}
	// vim: ft=javascript:
	/jslint sloppy: true, vars: true, white: true, nomen: true, browser: true /
	/global SpreadsheetApp, UiApp, UrlFetchApp, Utilities /
	/*
	* A script to automate requesting data from an external url that outputs CSV data.
	*
	* Adapted from the Google Analytics Report Automation (magic) script.
	* @author nickski15@gmail.com (Nick Mihailovski)
	* @author ianmlewis@gmail.com (Ian Lewis)
	*/

	var CSV_CONFIG = 'csvconfig';

	/* =========== Logging ======================= */

	/**
	* The output text that should be displayed in the log.
	* @private.
	*/
	var logArray_;

	/**
	* Clears the in app log.
	* @private.
	*/
	function setupLog_() {
	logArray_ = [];
	}

	/**
	* Returns the log as a string.
	* @returns {string} The log.
	*/
	function getLog_() {
	return logArray_.join('\n');
	}

	/**
	* Appends a string as a new line to the log.
	* @param {String} value The value to add to the log.
	*/
	function log_(value) {
	logArray_.push(value);

	var app = UiApp.getActiveApplication();
	var foo = app.getElementById('log');
	foo.setText(getLog_());
	}

	/**
	* Displays the log in memory to the user.
	*/
	function displayLog_() {
	var uiLog = UiApp.createApplication().setTitle('Report Status').setWidth(400).setHeight(500);
	var panel = uiLog.createVerticalPanel();
	uiLog.add(panel);

	var txtOutput = uiLog.createTextArea().setId('log').setWidth('400').setHeight('500').setValue(getLog_());
	panel.add(txtOutput);

	SpreadsheetApp.getActiveSpreadsheet().show(uiLog);
	}

	function getOrCreateSheet_(sheet_name) {
	var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
	var sheet = activeSpreadsheet.getSheetByName(sheet_name);
	if (!sheet) {
	sheet = activeSpreadsheet.insertSheet(sheet_name, 0);
	}
	return sheet;
	}

	/**
	* Create a Menu when the script loads. Adds a new csvconfig sheet if
	* one doesn't exist.
	*/
	function onOpen() {
	var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();

	// Add a menu.
	activeSpreadsheet.addMenu(
	"CSV Report", [
	{name: "Get Data", functionName: "getCSVData"},
	{name: "Create Report", functionName: "createCSVReport"}
	]
	);

	getOrCreateSheet_(CSV_CONFIG);
	}

	/**
	* Returns the trailing number on a string. For example the
	* input: xxxx555 will return 555. Inputs with no trailing numbers
	* return undefined. Trailing whitespace is not ignored.
	* @param {string} input The input to parse.
	* @resturns {number} The trailing number on the input as a string.
	* undefined if no number was found.
	*/
	function getTrailingNumber_(input) {
	// Match at one or more digits at the end of the string.
	var pattern = /(\d+)$/;
	var result = pattern.exec(input);
	if (result) {
	// Return the matched number.
	return result[0];
	}

	return undefined;
	}

	/**
	* Returns the values from 2 columns from the csvconfig sheet starting at
	* colIndex, as key-value pairs. Key-values are only returned if they do
	* not contain the empty string or have a boolean value of false.
	* If the key is start-date or end-date and the value is an instance of
	* the date object, the value will be converted to a string in yyyy-MM-dd.
	* If the key is start-index or max-results and the type of the value is
	* number, the value will be parsed into a string.
	* @param {number} colIndex The column index to return values from.
	* @return {object} The values starting in colIndex and the following column
	as key-value pairs.
	*/
	function getConfigsStartingAtCol_(sheet, colIndex) {
	var config = {}, rowIndex, key, value;
	var range = sheet.getRange(1, colIndex, sheet.getLastRow(), 2);

	// The first cell of the first column becomes the name of the query.
	config.query = range.getCell(1,1).getValue();

	for (rowIndex = 2; rowIndex <= range.getLastRow(); ++rowIndex) {
	key = range.getCell(rowIndex, 1).getValue();
	value = range.getCell(rowIndex, 2).getValue();
	if (value) {
	config[key] = value;
	}
	}

	return config;
	}

	/**
	* Returns an array of config objects. This reads the csvconfig sheet
	* and tries to extract adjacent column names that end with the same
	* number. For example Names1 : Values1. Then both columns are used
	* to define key-value pairs for the coniguration object. The first
	* column defines the keys, and the adjacent column values define
	* each keys values.
	* @param {Sheet} The csvconfig sheet from which to read configurations.
	* @returns {Array} An array of API query configuration object.
	*/
	function getConfigs_(sheet) {

	var configs = [], colIndex;
	// There must be at least 2 columns.
	if (sheet.getLastColumn() < 2) {
	return configs;
	}

	var headerRange = sheet.getRange(1, 1, 1, sheet.getLastColumn());
	var firstColValue, firstColNum, secondColValue, secondColNum;

	// Test the name of each column to see if it has an adjacent column that ends
	// in the same number. ie xxxx555 : yyyy555.
	// Since we check 2 columns at a time, we don't need to check the last column,
	// as there is no second column to also check.
	for (colIndex = 1; colIndex <= headerRange.getNumColumns() - 1; ++colIndex) {
	firstColValue = headerRange.getCell(1, colIndex).getValue();
	firstColNum = getTrailingNumber_(firstColValue);

	secondColValue = headerRange.getCell(1, colIndex + 1).getValue();
	secondColNum = getTrailingNumber_(secondColValue);

	if (firstColNum && secondColNum && firstColNum === secondColNum) {
	configs.push(getConfigsStartingAtCol_(sheet, colIndex));
	}
	}

	return configs;
	}

	// This will parse a delimited string into an array of
	// arrays. The default delimiter is the comma, but this
	// can be overriden in the second argument.
	function parseCsvResponse_( strData, strDelimiter ){
	// Check to see if the delimiter is defined. If not,
	// then default to comma.
	strDelimiter = (strDelimiter \|\| ",");
	strData = strData.replace(/^\s+\|\s+$/g, '');

	// Create a regular expression to parse the CSV values.
	var objPattern = new RegExp(
	(
	// Delimiters.
	"(\\" + strDelimiter + "\|\\r?\\n\|\\r\|^)" +

	// Quoted fields.
	"(?:\"([^\"](?:\"\"[^\"])*)\"\|" +

	// Standard fields.
	"([^\"\\" + strDelimiter + "\\r\\n]*))"
	),
	"gi"
	);


	// Create an array to hold our data. Give the array
	// a default empty first row.
	var arrData = [[]];

	// Create an array to hold our individual pattern
	// matching groups.
	var arrMatches = null;


	// Keep looping over the regular expression matches
	// until we can no longer find a match.
	while (arrMatches = objPattern.exec( strData )){

	// Get the delimiter that was found.
	var strMatchedDelimiter = arrMatches[ 1 ], strMatchedValue;

	// Check to see if the given delimiter has a length
	// (is not the start of string) and if it matches
	// field delimiter. If id does not, then we know
	// that this delimiter is a row delimiter.
	if (
	strMatchedDelimiter.length &&
	(strMatchedDelimiter !== strDelimiter)
	){

	// Since we have reached a new row of data,
	// add an empty row to our data array.
	arrData.push( [] );

	}

	// Now that we have our delimiter out of the way,
	// let's check to see which kind of value we
	// captured (quoted or unquoted).
	if (arrMatches[ 2 ]){

	// We found a quoted value. When we capture
	// this value, unescape any double quotes.
	strMatchedValue = arrMatches[ 2 ].replace(
	new RegExp( "\"\"", "g" ),
	"\""
	);

	} else {

	// We found a non-quoted value.
	strMatchedValue = arrMatches[ 3 ];

	}

	// Now that we have our value string, let's add
	// it to the data array.
	arrData[ arrData.length - 1 ].push( strMatchedValue );
	}

	// Return the parsed data.
	return arrData;
	}



	function populateSheetWithCSV_(sheet, csvUrl, user, pw) {

	// request the CSV!
	var headers = {};
	if (user && pw) {
	headers = {
	// use basic auth
	'Authorization': 'Basic ' + Utilities.base64Encode(
	user + ':' + pw, Utilities.Charset.UTF_8)
	};
	}
	var resp = UrlFetchApp.fetch(csvUrl, {
	headers: headers,
	validateHttpsCertificates: false // No valid certificate yet :(
	});

	// parse the response as a CSV
	var csvContent = parseCsvResponse_(resp.getContentText());

	// clear everything in the sheet
	sheet.clearContents().clearFormats();

	// set the values in the sheet (as efficiently as we know how)
	sheet.getRange(
	1, 1,
	csvContent.length, /* rows */
	csvContent[0].length /* columns */).setValues(csvContent);

	}



	/*
	Gets the CSV data for each report and loads it into the appropriate sheet.
	*/
	function getCSVData(e) {
	setupLog_();
	var now = new Date(), i, config, configName, sheet;
	log_('Running on: ' + now);

	var configs = getConfigs_(getOrCreateSheet_(CSV_CONFIG));

	if (!configs.length) {
	log_('No report configurations found');
	} else {
	log_('Found ' + configs.length + ' report configurations.');

	for (i = 0; config = configs[i]; ++i) {
	configName = config.query;
	if (config['sheet-name']) {
	if (config.url) {
	try {
	log_('Getting CSV: ' + configName);
	sheet = getOrCreateSheet_(config['sheet-name']);
	populateSheetWithCSV_(sheet, config.url, config['http-username'], config['http-password']);
	} catch (error) {
	log_('Error executing ' + configName + ': ' + error.message);
	}
	} else {
	log_('No URL found: ' + configName);
	}
	} else {
	log_('No sheet-name found: ' + configName);
	}
	}


	}
	log_('Script done');

	// Update the user about the status of the queries.
	if( e === undefined ) {
	displayLog_();
	}
	}

	/**
	* Returns 1 greater than the largest trailing number in the header row.
	* @param {Object} sheet The sheet in which to find the last number.
	* @returns {Number} The next largest trailing number.
	*/
	function getLastNumber_(sheet) {
	var maxNumber = 0;

	var lastColIndex = sheet.getLastColumn();

	if (lastColIndex > 0) {
	var range = sheet.getRange(1, 1, 1, lastColIndex);

	for (var colIndex = 1; colIndex < sheet.getLastColumn(); ++colIndex) {
	var value = range.getCell(1, colIndex).getValue();
	var headerNumber = getTrailingNumber_(value);
	if (headerNumber) {
	var number = parseInt(headerNumber, 10);
	maxNumber = number > maxNumber ? number : maxNumber;
	}
	}
	}
	return maxNumber + 1;
	}

	/**
	* Adds a CSV Report configuration to the spreadsheet.
	*/
	function createCSVReport() {
	var sheet = getOrCreateSheet_(CSV_CONFIG);
	var headerNumber = getLastNumber_(sheet);
	var config = [
	["query" + headerNumber, "value" + headerNumber],
	['url', ''],
	['http-username', ''],
	['http-password', ''],
	['sheet-name', '']];

	sheet.getRange(1, sheet.getLastColumn() + 1, config.length, 2).setValues(config);
	}