Batch import CSV to a Spreadsheet #gas #sheet #csv

3acd892713c001e9a579.md

###Batch import CSV to a Spreadsheet http://stackoverflow.com/questions/17578317

Code.gs

function main(){
  var files = DriveApp.getFolderById(FOLDER_ID).getFilesByType('text/csv');
  var csver = new CSVer();
  csver.setSpreadsheet(SpreadsheetApp.openById(SPREADSHEET_ID));
  csver.importFile(files.next(), 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.js

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;
};

Thanks a lot!!! Can't believe someone has not created a google app to do this yet.

To get this working for me, I had to :

1. comment out the regex check in "Proto.js"

2. comment out the function "newChart" which was just adding an annoying table on top of the imported data.

3. add a while loop to get it to go through all the files in the folder.
   while (files.hasNext()) {
   var file = files.next();
   csver.importFile(file, newChart);
   }

4. In CSVer.gs , i added insertSheet(file.getName()); so that it names the worksheet same name as the original file.

@georambo Could you clarify what you mean by "comment out the regex check"? Do you mean the whole String.prototype method? Sorry, I've been trying to make heads or tails of some of the code here, I think I need a little more knowledge of google script. This code looks like it could be really useful for what I'm trying to do (import csv values to fill data for a report template and spit out a pdf version), but as you pointed out, it seems to only execute for one file.

@oshliaer: Thank you for great purpose script.

Please help me with your script.

After run "main" function, I've got error as below:

{"message":"Cannot

read property "length" from null.","name":"TypeError","fileName":"CSVer.js","lineNumber":16,"stack":"\tat CSVer.js:16\n\tat Code:9 (main)\n"}

I did'nt change any line of code in your script.

Could you please tell me why?

My CSV file is included special string like : "" , ', [, ],..etc

Where do you put those files ? Where do you run it from ?