How to import multiple csv files (of identical format) and create charts automatically in a Google Spreadsheet?

4
votes

I have about 25 csv files, all of the same format (identical number of rows and columns). I would like to import these into separate sheets (tabs) of a google docs spreadsheet, and create charts (about 10) in each of the sheets.

Is there a convenient script or some automatic way of populating the spreadsheet, given the format and chart templates for one sheet?

One solution is to (a) import one csv file into a sheet, (b) create all the charts in the sheet, (c) make several copies of the sheet, which also creates the charts in each copy, and (d) import the remaining csv files into the other copies. Is there a less painful or more scalable solution? Thanks!

1
google-apps-scriptgoogle-sheetsgoogle-docs

1 Answers

3
votes

For an example.

I take a FileIterator, a Spreadsheet and put them all to the spreadsheet by sheet.

Code.gs

function main() {
  var files = DriveApp.getFolderById(FOLDER_ID).getFilesByType('text/csv');
  var csver = new CSVer();
  csver.setSpreadsheet(SpreadsheetApp.openById(SPREADSHETT_ID));
  csver.importFiles(files, newChart);
}

function newChart(sheet) {
  var chartBuilder = sheet.newChart();
  chartBuilder.setChartType(Charts.ChartType.TABLE).addRange(sheet.getDataRange()).setPosition(1, 3, 1, 1);
  sheet.insertChart(chartBuilder.build());
}

CSVer.gs

var CSVer = function() {
  var spreadsheet_ = null;

  this.setSpreadsheet = function(spreadsheet) {
    spreadsheet_ = spreadsheet;
    return this;
  }

  this.importFile = function(file, funct) {
    try {
      var csv = file.getBlob().getDataAsString().split('\n');
      var len = csv[0].parseCSVtoArray().length;
      var data = [];
      for (var i = 0; i < csv.length; i++) {
        var row = csv[i].parseCSVtoArray();
        if (row.length == len)
          data.push(row);
      }
      var newSheet = spreadsheet_.insertSheet();
      newSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
      if (funct)
        funct(newSheet);
      return newSheet;
    } catch (err) {
      Logger.log(JSON.stringify(err));
      return null;
    }
  }
}

CSVer.prototype.importFiles = function(files, funct) {
  while (files.hasNext()) {
    var file = files.next();
    this.importFile(file, funct);
  }
  return this;
};

Proto.gs

Highly recommend to read How can I parse a CSV string with Javascript?

String.prototype.parseCSVtoArray = function() {
  var text = this;
  var re_valid = /^\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*(?:,\s*(?:'[^'\\]*(?:\\[\S\s][^'\\]*)*'|"[^"\\]*(?:\\[\S\s][^"\\]*)*"|[^,'"\s\\]*(?:\s+[^,'"\s\\]+)*)\s*)*$/;
  var re_value = /(?!\s*$)\s*(?:'([^'\\]*(?:\\[\S\s][^'\\]*)*)'|"([^"\\]*(?:\\[\S\s][^"\\]*)*)"|([^,'"\s\\]*(?:\s+[^,'"\s\\]+)*))\s*(?:,|$)/g;
  if (!re_valid.test(text))
    return null;
  var a = [];
  text.replace(re_value, function(m0, m1, m2, m3) {
    if (m1 !== undefined)
      a.push(m1.replace(/\\'/g, "'"));
    else if (m2 !== undefined)
      a.push(m2.replace(/\\"/g, '"'));
    else if (m3 !== undefined)
      a.push(m3);
    return '';
  });
  if (/,\s*$/.test(text))
    a.push('');
  return a;
};

For batch operations (26 csv-files to 26 sheets with 1 chart per each), I get good results over time.

[15-06-11 13:53:51:645 EAT] Execution succeeded [31.428 seconds total runtime]