How do I append data from a CSV file without the header into Google Sheet

Question

I have managed to retrieve the data and append it into my sheet but I have not figured out how to exclude the header row.

function importFromCSV() {
  var fileName = Browser.inputBox("Ingrese archivo en Drive a importar (ej. datosBaby.csv):");
   
  var searchTerm = "title = '"+fileName+"'";
   
  // search for our file
  var files = DriveApp.searchFiles(searchTerm)
  var csvFile = "";
   
  // Loop through the results
  while (files.hasNext()) {
    var file = files.next();
    // assuming the first file we find is the one we want
    if (file.getName() == fileName) {
      // get file as a string
      csvFile = file.getBlob().getDataAsString();
      break;
    }
  }
  // parseCsv returns a [][] array and writes to the sheet
  var csvData = Utilities.parseCsv(csvFile);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // data to a sheet
  sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData.length).setValues(csvData);
  
  //sheet.getRange(1, 1, csvData.length, csvData[1].length).setValues(csvData); <-- This one places txt a A1
  //sheet.appendRow(csvData[1]); <-- This one  does not work
}

Here is an example of what is happening: The header from the CSV import is repeated

Thanks to anyone who can point me in the right direction. I am a beginner at this.

Your question boils down to: "I have an array `csvData`, how do I remove the first element?" I'm sure if you look at it this way, you will find existing solutions. — , May 14 '20 at 08:05
Does this answer your question? [Remove first Item of the array (like popping from stack)](https://stackoverflow.com/questions/29605929/remove-first-item-of-the-array-like-popping-from-stack) — , May 14 '20 at 08:06

score 2 · Answer 1 · edited May 20 '21 at 20:37

The splice approach is right, but in the solution above, it returns the values for the header, and does not remove the header. What is needed to remove the header is to use the splice function and tell it to start in the second row and go until the end. This would look like this:

var csvDataNoHeader = csvData.splice(1,csvData.length-1)

This tells the function to start at the second element of the array and select all elements after, the minus 1 accounts for the removing of the header.

score 1 · Answer 2 · answered May 14 '20 at 10:38

1

So I understand that you are importing multiple csv where the header is repeating for each one.

To get rid of it there are a lot of actions, I myself prefer to seperate the header of the actual data. You can do that by invoking the .shift() into the array, and that would remove and return the first value of the array.

Call the shift method after parsing the csv.

var csvData = Utilities.parseCsv(csvFile);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

var header = csvData.shift(); // Now the csvData don't have the header and you can
                              // still use the `header` variable if needed

// data to a sheet
sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData[0].length)
     .setValues(csvData);

I also changed the way you select the columns, unless your data is square N arrays of N elements, you will have problems. So better to take the columns as csvData[0].length.

answered May 14 '20 at 10:38

Raserhin

2,516
1
10
14

thank you so much. I am still not understanding how creating a 'var header = csvData.shift()' is removing the header from the data that is being parsed into the active sheet. The variable _header_ is not being menstioned in 'sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData[0].length).setValues(csvData);'. But it **worked great**!!! One more question: how would i shift if the header is 5 rows and not just the first row? – S Mullins May 14 '20 at 19:40
The `shift` method invoked on an array **removes** and **returns** its *first* element, so after invoking there is no longer a header. If you wanted to do the same with the *last* there is the [`pop`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/pop) method. Now if you want to do it in a arbitrary index you can use [`splice`](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/splice) imagine the 5th row `csvData.splice(4, 1)` meaning: delete `1` element at index `4` (5th position because the arrays are zero index). – Raserhin May 15 '20 at 08:07
you were very helpful. 'csvData.splice()' worked great except not 'csvData.splice(4, 1)' but rather 'csvData.splice(0, 5)'. I still don't understand where the 'var header' is being invoked. The variable header is not being menstioned in 'sheet.getRange(sheet.getLastRow()+1, 1, csvData.length, csvData[0].length).setValues(csvData);'. – S Mullins May 16 '20 at 05:05
Glad it helped, but take into account that `csvData.splice(0, 5)` will actually delete the first 5 rows, so just take a look in case that it may work because you are losing some data because I don't quite know how your data is laid out. – Raserhin May 18 '20 at 07:15

How do I append data from a CSV file without the header into Google Sheet

2 Answers2