Automatic CSV output to google sheets using google script

Question

can someone please tell me how to append imported CSV data in an existing googlespreadsheet. Once imported there is an empty row at the bottom and every time i run the code i would like it to insert the new csv data starting from the empty end row.

Thank you

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()) { // proceed if "report.csv" file exists in the reports    folder
var file = fi.next();
var csv = file.getBlob().getDataAsString();
var csvData = CSVToArray(csv); // see below for CSVToArray function
//sd.deleteSheet('newtest1')
  //var newsheet = ss.insertSheet('newtest11'); // create a 'NEWDATA' sheet   to store imported data
// loop through csv data array and insert (append) as rows into 'NEWDATA'   sheet

var sheet = ss.getSheets()[0];
 // Columns start at "1" - this will delete the first two columns
 //sheet.deleteColumns(6, 20);

 //var lastRow = sheet.getLastRow();sheet.appendRow(['']);
  //var lastRow = s.getLastRow();s.appendRow(['']);
  //s.deleteRows(lastRow+1, 50);

 s.getRange(+1, 1, csvData.length, csvData[5].length).setValues(csvData);



/*
** report data is now in 'NEWDATA' sheet in the spreadsheet - process it as needed,
** then delete 'NEWDATA' sheet using 
*/
// rename the report.csv file so it is not processed on next scheduled run

//file.setName("L661_BOM-CAD_14-12-15-"+(transferred)+".csv");
}
};

// http://www.bennadel.com/blog/1504-Ask-Ben-Parsing-CSV-Strings-With-   Javascript-Exec-Regular-Expression-Command.htm
// 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 CSVToArray( strData, strDelimiter ){
    // Check to see if the delimiter is defined. If not,
    // then default to comma.
    strDelimiter = (strDelimiter || ";");

    // 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 ];

        // 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( [] );

        }

        var strMatchedValue;

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

Answer 1

This code:

for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
  newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new   Array(csvData[i]));
}

Looks like it's writing data row by row. That takes more time than writing all the data at once. First put all the data into a two dimensional array, then only write the data once. So, don't use setValues() inside the loop. Build an array inside the loop, but don't write data inside the loop.

var outerArray = [],
    innerArray = [];

for (var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
  innerArray.push(new Array(csvData[i]));
  outerArray.push(innerArray);
};

newsheet.getRange(1, 1, outerArray.length, outerArray[0].length).setValues(outerArray));

The above code might not work, but hopefully you get the basic idea of what you could try.

NOTE: If the rows of data are of different lengths, that would create inner arrays of different lengths. In that situation, an error will occur. All the inner arrays must be exactly the same length. So, if your data has rows of different lengths, you'd need to somehow make them all the same length by inserting blank values, or this strategy won't work.