Google Apps Script Utilities.parseCsv() and replacement character - �

Question

I'm working on a project that involves a csv file in Google Drive that is updated with new data every minute or so.

I've built a spreadsheet dashboard to make the data in the csv more meaningful.

I made the mistake of assuming that I could use the Google Spreadsheet function =importdata(url) to get the data from the CSV in Google Drive into my Google Spreadsheet, however, that produces an error unless I make the CSV public, which is not feasible for security and privacy reasons. Even if I do make the CSV public and use importdata, the data that comes in is completely malformed and unuseable - it looks nothing like the actual CSV.

malformed data with importdats(url)

I'm trying to write a script to automatically import the csv data using DriveApp to open the csv file, Utilities.parseCsv to turn the csv into an array of data, and then setValues to write the data to the sheet.

function importData() {
  var ss = SpreadsheetApp.getActive();
  var file = DriveApp.getFilesByName("Agent Performance.csv")
  var csv = file.next().getBlob().getDataAsString();
  var csvData = Utilities.parseCsv(csv);
  var sheet = ss.getSheetByName('CSV Import TEST');
  for (var i = 0; i < csvData.length; i++) {
    sheet.getRange(i+1, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
  }
}

The issue is I get replacement characters like: � all throughout the data that gets written to the sheet. It's so strange. The sheet looks normal, but if you click on a cell to see it's value the formula bar has whatever the text is in the cell, but between each character is a �. This makes any calculation in Google Sheets impossible.

Notice replacement characters in formula bar

If I just import the same CSV using Google Sheets "File" and "Import" and "Replace current sheet", the data comes in fine. This isn't a good solution because really I want no more than a few minutes of a delay between when the CSV updates and when the dashboard in the Google Sheets update. If I have to manually upload the CSV, it defeats the purpose of the system.

Any help would be greatly appreciated. Thank you!

Answer 1

Have you check the file charset? You can specify it when calling getDataAsString(charset). Try this:

function importData() {
  var ss = SpreadsheetApp.getActive();
  var file = DriveApp.getFilesByName("Agent Performance.csv")
  var csv = file.next().getBlob().getDataAsString('ISO-8859-1'); //note the charset
  var csvData = Utilities.parseCsv(csv);
  //unless you csv has variable amount of columns per line, you should do this
  if(csvData.length > 0) {
    ss.getSheetByName('CSV Import TEST')
      .getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  } else
    throw 'Blank file';
}