A Google Apps Script for importing CSV data into a Google Spreadsheet.

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 [email protected] (Nick Mihailovski)
 * @author [email protected] (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);
}

The script break due to permission issue on all of the sheet modification call such as clear content or add new content. Did you have this issue?

Another approach Batch import CSV to a Spreadsheet

Can you provide a license on your file?
Can you share how you viewed the script behind "Google Analytics Report Automation (magic) script"? Presumably this is the same script as this add-on: https://chrome.google.com/webstore/detail/google-analytics/fefimfimnhjjkomigakinmjileehfopp

Hi, Ian,

I am really in need of your script, but I am unable to make it run it seems. Whenever I use the menu you create on the google sheets interface, I get a Report Status with the following:

Running on: Fri Dec 16 2016 13:40:01 GMT+0100 (CET)
No report configurations found
Script done

And nothing happens afterwards. What am I doing wrong? THis is the first time I am using GOogle Scripts, so forgive my ignorance and thank you for any help!!!

Hi,
Thank you for this script. It's great!
I would like to keep the cell styling (bold, background color, etc.), and only update the value when I click on "Get Data". Is there a simple line of code to change, or does it seem more complicated?
Thanks!

hello, thankps for the scypt. But i need to import only certain column (column 1, 5, 20). What i have t do?