1

I'm piecing together some Google Apps Script from this post that will take a list of files and import all of them as unique sheets. It works all except for if there is a blank/empty spot in any of the .csvs then the import to sheet writes that blank value as "undefined" instead of skipping over or leaving blank.

Is there a way to have a multi-dimensional array with an empty nested array and then use 

newsheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));

WITHOUT the blank/empty spots in my .csv setting an undefined value? Or is there a quick cleanup I can do overtop?

Thanks

Community
  • 1
  • 1
Grant
  • 903
  • 1
  • 16
  • 24

2 Answers2

1

When array data is outputted to spreadsheet, the array elements with null don't display "undefined" in the cell.

When the elements in a part of CSV data is empty, the array elements translated by CSVToArray are given null. So when the array is outputted to to a spreadsheet, the elements with null become clean cells without displaying "undefined".

If it is not clean cells, following code can be used.

for (var i=0; i< csvData.length; i++){
  csvData[i][csvData[i].indexOf("")] = null
}

If my understanding is incorrect, I apologize.

Tanaike
  • 181,128
  • 11
  • 97
  • 165
  • So using what you said didn't work for my scenario, but it did point me in the right direction. I used to for loops `for(i....) { for(j....) { ` then logged each individual index. I found that my undefined values were null in the log. So by adding `if(csvData[i][j] == null){ csvData[i][j]= ''; }` that solved it! – Grant Nov 09 '16 at 12:55
1

Logging solved my issue with help from k.

Logger.log(csvData[i]) is returning

[.....Top Products, false, null, null, One per quote line, null, Quantity, false, null, Always, null, null, null,.....]

then when I do another for loop, Logger.log(csvData[i][j]) shows

[16-11-09 07:52:25:255 EST] Top Products
[16-11-09 07:52:25:256 EST] false
[16-11-09 07:52:25:256 EST] undefined
[16-11-09 07:52:25:257 EST] undefined
[16-11-09 07:52:25:257 EST] One per quote line
[16-11-09 07:52:25:257 EST] undefined
[16-11-09 07:52:25:258 EST] Quantity
[16-11-09 07:52:25:258 EST] false
[16-11-09 07:52:25:259 EST] undefined
[16-11-09 07:52:25:259 EST] Always
[16-11-09 07:52:25:259 EST] undefined
[16-11-09 07:52:25:260 EST] undefined
[16-11-09 07:52:25:260 EST] undefined

The following snippet inside of the nested for fixed my issue.

    if(csvData[i][j] == null){
      csvData[i][j]= '';
    }
Grant
  • 903
  • 1
  • 16
  • 24