How can I append imported CSV data to an existing Google Spreadsheet? Once imported there is an empty row at the bottom, and every subsequent time I run the code I would like it to insert the new CSV data following that empty end row.
Here is my existing code:
function getCSV() {
var fSource = DriveApp.getFolderById('0B2lVvlNIDosoajRRMUwySVBPNVE'); // reports_folder_id = id of folder where csv reports are saved
var fi = fSource.getFilesByName('L661_BOM-CAD_14-12-15.csv'); // latest report file
var ss =SpreadsheetApp.openById('1WEDYfEudYsbkUhHbCxZspEbNXz3cjQIe3JdhnbFmmYA'); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data
var sd = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var target = new Array()
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[0]);
if ( fi.hasNext()) {
var file = fi.next();
var csv = file.getBlob().getDataAsString();
var csvData = CSVToArray(csv); // see below for CSVToArray function
var sheet = ss.getSheets()[0];
s.getRange(+1, 1, csvData.length, csvData[5].length).setValues(csvData);
}
};
function CSVToArray( strData, strDelimiter ){
strDelimiter = (strDelimiter || ";");
var objPattern = new RegExp(
(
// Delimiters.
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
// Quoted fields.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
// Standard fields.
"([^\"\\" + strDelimiter + "\\r\\n]*))"
),
"gi"
);
var arrData = [[]];
var arrMatches = null;
while (arrMatches = objPattern.exec( strData )){
var strMatchedDelimiter = arrMatches[ 1 ];
if (
strMatchedDelimiter.length &&
strMatchedDelimiter !== strDelimiter
){
// Since we have reached a new row of data,
// add an empty row to our data array.
arrData.push( [] );
}
var strMatchedValue;
if (arrMatches[ 2 ]){
strMatchedValue = arrMatches[ 2 ].replace(
new RegExp( "\"\"", "g" ),
"\""
);
} else {
strMatchedValue = arrMatches[ 3 ];
}
arrData[ arrData.length - 1 ].push( strMatchedValue );
}
return( arrData );
}
I managed to fix the issue by adding getLastRow(); command
hopefully this helps someone else out
var lastrow = s.getLastRow();
s.getRange(lastrow + 1, 1, csvData.length, csvData[0].length).setValues(csvData);