Using Apps Script to import a CSV file to sheets results not importing all data

[gregbo...@gmail.com]

I'm importing CSV data from a file in Google Drive using this function:

  function importList(fileId, sheetName) { 

      let stringified = DriveApp.getFileById(fileId).getBlob().getDataAsString(); 

      let newData = Utilities.parseCsv(stringified); 

      const ss = SpreadsheetApp.getActiveSpreadsheet(); 

      const sht = ss.getSheetByName(sheetName); 

      sht.getRange(1, 1, newData.length, newData[0].length).setValues(newData); 

    }

The function runs without errors. The problem is that the CSV has around 500+rows and 10 columns and the imported result brings in only the first 302 rows. The data 'stops' in one case at 337 rows and 5 columns. Where the CSV has a blank cell at the 6th column of row 337, this is where the data appears to stop importing. A similar result occurs where I import some other files, but the results are not consistent.

When I Logger.log the length of newData array, this is reflecting the length of the array being sent to the sheet and a Logger.log of the last row of the array also returns what is being posted to the sheet.

It's like the getBlob() isn't getting all of the data in the CSV file. I've not been able to find similar issues encountered by others. Is there some other method I can use to return a more reliable result? Or am I doing something wrong in my approach?

[Clark Lind]

You may have to manually parse the CSV file and replace any empty cells with "null". The parser may be finding the empty cell and treat it as end of file.

[Clark Lind]

Maybe something like this:

.....

      let stringified = DriveApp.getFileById(fileId).getBlob().getDataAsString(); 

      let noSpaces = stringified.replace(/,,/g, ,"null",)  //this assumes the empty cells are truly empty and not a space. The Regex gurus can make sure I'm doing this correctly...

      let newData = Utilities.parseCsv( noSpaces ); 

....