Parsing a CSV from a Mail (Gmail) with double quotes and ? characters with Google Apps Script

Question

I did set up routines with the following code to parse CSVs into specific spreadsheets:

 function updateGmvAndNmv() {
  const threads = GmailApp.search("from:([email protected]) subject:(uniqueHeader)");
  const messages = threads[0].getMessages();
  const length = messages.length;
  const lastMessage = messages[length - 1];
  const attachemnt = lastMessage.getAttachments()[0];

  const csvData = Utilities.parseCsv(attachemnt.getDataAsString(), ",");

  const ss = SpreadsheetApp.openById("spreadsheetID").getSheetByName("sheetName")
  const ssOriginalRange = ss.getRange("A:E");
  const ssToPaste = ss.getRange(1,1,csvData.length,csvData[0].length);

  ssOriginalRange.clear();
  ssToPaste.setValues(csvData)
}

With the latest CSV that I want to parse, I encounter an issue, where I am stuck. I tried to play around with the settings in the app that sends me the report but I can not change the way the CSV is being constructed. When I look at the CSV with a text Editor, I see something like this:

GMV and NMV per partner

"Merchant",,"NMV","GMV bef Cancellation","GMV bef Return"

When I let the above code run, it gets the file and outputs the following in my spreadsheet: Spreadsheet Example

Which brings up the following questions:

1. Why do I have "" (double quotes) in row 5? I assumed the parseCsv-function removes those.
2. With my other CSVs I did not have any issues, but there I did not have any double quotes. Can someone explain the difference in CSVs, once with double quotes and once without?
3. How can I treat this data correctly, in order to get the data without the "" into the spreadsheet?
4. Why do I see some ? symbols (please look at the fx input field, row 1 and 7) and how do I get rid of them? The export should be without any format (CSV) and in a text editor I do see all values normally - without any ?.

Answer 1

The issue was the encoding. The correct encoding of the file is UTF-16, while the standard encoding of .getDataAsString() is UTF-8.