Skip to content

Instantly share code, notes, and snippets.

@oshliaer
Last active December 27, 2019 03:30
Show Gist options
  • Save oshliaer/3acd892713c001e9a579 to your computer and use it in GitHub Desktop.
Save oshliaer/3acd892713c001e9a579 to your computer and use it in GitHub Desktop.
Batch import CSV to a Spreadsheet #gas #sheet #csv
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());
}
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;
};
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;
};
@georambo
Copy link

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.

@mikestarks91
Copy link

@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.

@votuduc
Copy link

votuduc commented Jun 28, 2018

@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

@leobenkel
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment