How to append data into a specific sheet in a google spreadsheet from a CSV in a folder in google Drive?

Question

What is the business need? The data is from some contacts that I need to update into mailchimp through zapier.

I´m trying to add data from an imported CSV from a specific folder in google drive. This data should we added to the last row of a certain sheet in a google spreadsheet.

I want to do it with Google Script.

Why needs to be added to the last row because this triggers a function in zapier to update this data.

I got the following from other posts, but this only creates a new sheet and what i need is to append the data in a specific sheet.

                    function importData() {
              var fSource = DriveApp.getFolderById("0B1FOmGiNuwVNMU1SU29kc2dGSkE"); // reports_folder_id = id of folder where csv reports are saved
              var fi = fSource.getFilesByName('plancomercial.csv'); // latest report file
              var ss = SpreadsheetApp.openById("1w7iW_DKJPZEcy9C8N9iskh_GpCmmL6N9PiZNSpXtrcc"); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data
              if ( fi.hasNext() ) { // proceed if "plancomercial.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

            var dateObj = new Date();
            var month = dateObj.getUTCMonth() + 1; //months from 1-12
            var day = dateObj.getUTCDate();
            var year = dateObj.getUTCFullYear();
            var newdate = year + "/" + month + "/" + day; //formating date to be stamped into the sheet label

                var newsheet = ss.insertSheet('Ciclo '+ newdate,0); // create a 'Ciclo + formated date' sheet to store imported data
                // loop through csv data array and insert (append) as rows into 'Ciclo + formated date' sheet
                for ( var i=0, lenCsv=csvData.length; i<lenCsv; i++ ) {
                  newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
                }
                /*
                ** report data is now in 'Ciclo + formated date' sheet in the spreadsheet - process it as needed,
                ** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
                */
                // rename the report.csv file so it is not processed on next scheduled run
                file.setName("plancomercial-"+(new Date().toString())+".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( [] );

                }

                // 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.
                  var strMatchedValue = arrMatches[ 2 ].replace(
                    new RegExp( "\"\"", "g" ),
                    "\""
                  );

                } else {

                  // We found a non-quoted value.
                  var 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 );
            };        

Thank you!

Answer 1

How about following script?

For "setValues", a multidimensional array can be imported directly to spreadsheet.

For "getRange", 4 parameters are row (coordinate ), column (coordinate ), numRows (number (size) of data array) and numColumns (number (size) of one element in data array), respectively. Ref

function importData() {
  var fSource = DriveApp.getFolderById("0B1FOmGiNuwVNMU1SU29kc2dGSkE").getFiles(); // reports_folder_id = id of folder where csv reports are saved
  var csvfile = 'plancomercial.csv'; // latest report file
  var ss = SpreadsheetApp.openById("1w7iW_DKJPZEcy9C8N9iskh_GpCmmL6N9PiZNSpXtrcc"); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data

  while (fSource.hasNext()) {
    var file = fSource.next();
    if (file.getName() == csvfile) {
      var dateObj = new Date();
      var month = dateObj.getUTCMonth() + 1; //months from 1-12
      var day = dateObj.getUTCDate();
      var year = dateObj.getUTCFullYear();
      var newdate = year + "/" + month + "/" + day; //formating date to be stamped into the sheet label
      var newsheet = ss.insertSheet('Ciclo '+ newdate,0); // create a 'Ciclo + formated date' sheet to store imported data
      var csv = file.getBlob().getDataAsString();
      var csvData = CSVToArray(csv); // see below for CSVToArray function
      newsheet.getRange(1,1,csvData.length,csvData[0].length).setValues(csvData);
      /*
      ** report data is now in 'Ciclo + formated date' sheet in the spreadsheet - process it as needed,
      ** then delete 'NEWDATA' sheet using ss.deleteSheet(newsheet)
      */
      // rename the report.csv file so it is not processed on next scheduled run
      file.setName("plancomercial-"+(new Date().toString())+".csv");
    }
  }
}