1

I have code listed below of reading in a csv file and putting it into an array to be pushed onto the spreadhsheet. However, the csv file that I will be reading in needs an additional column in the middle to match the dataset in the spreadsheet. The spreadsheet and csv cannot be changed. Thanks for the help!

// Import function
function importFromCSV() {

 var fileName = Browser.inputBox("Enter the name of the file in your Docs List to          import (e.g. myFile.csv):");

 var files = DocsList.getFiles();
  var csvFile = "";

  for (var i = 0; i < files.length; i++) {
if (files[i].getName() == fileName) {
  csvFile = files[i].getContentAsString();
  break;
    }
  }
  var csvData = CSVToArray(csvFile, ",");
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Archive");
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));

  }
  var rowtoDel=csvData.length;
 delrowFunction(rowtoDel)
 Browser.msgBox("Jobs Successfully Imported.");
}




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


}

1 Answers1

3

You can do it quite simply at array level before writing to the sheet with the splice method.

See below the modified part of your code :

(I used column 3 in this test, you can change to any valid number, see comments in code (smaller than the array width)

  for (var i = 0; i < csvData.length; i++) {
    var row = csvData[i]
    Logger.log(row);// see value before change
    row.splice(2,0,""); add an empty column on col C (arrays count from 0)
    Logger.log(row);// see value after change
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues([row]);
  }
Serge insas
  • 45,904
  • 7
  • 105
  • 131