Formatting Issues with CSV import to Google Sheets from Gmail

Question

I'm currently importing CSV data from gmail emails automatically using the following script:

 function importsearchresultsCSVFromGmail2() {

var threads = GmailApp.search("Scheduled report (********** **** *******)");
var messages = threads[0].getMessages();
var message = messages[messages.length - 1];
var attachment = message.getAttachments()[0];

// Is the attachment a CSV file

attachment.setContentType('text/csv');
//attachment.setContentTypeFromExtension();

if (attachment.getContentType() === "text/csv") {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var sh0 = sheet.getSheetByName("Sheet1")
var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");


// Remember to clear the content of the sheet before importing new data
sh0.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
GmailApp.markMessageRead(message);

}
}

However, some address fields in the csv are formatted in the entire cell of the csv to be:

Address Layout 1

and some will be shown as "123 New Road, London, NE3 1RD". < These display correctly in the import, however the formatted version above is spread across a number of cells and messes up the original layout of the csv.

Is there a way to ensure the imported cell (that contains the formatted address cell) wont spread across to other cells?

Current CSV File Example Current Google Sheet Result

Thanks in advance.

Tom

Answer 1

Here is a variant (not so elegant as Yuri solution)

function importCSVFromGoogleDrive() {
  var file = DriveApp.getFilesByName("CSV TEST.csv").next();
  var csvString = file.getBlob().getDataAsString().replace(/\n/g, '♥').replace(/\r♥/g, '\r\n')
  var csvData = Utilities.parseCsv(csvString);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mySheet');
  sheet.clear();
  sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  SpreadsheetApp.flush()
  let ranges = SpreadsheetApp.getActive()
    .createTextFinder("♥")
    .matchEntireCell(false)
    .matchCase(true)
    .matchFormulaText(false)
    .ignoreDiacritics(true)
    .findAll();
  ranges.forEach(function (range) {
    range.setValue(range.getValue().replace(/♥/g,"\n"));
  });
}

adapted to your situation

  var csvData = Utilities.parseCsv(attachment.getDataAsString().replace(/\n/g, '♥').replace(/\r♥/g, '\r\n'), ",");
  sh0.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  SpreadsheetApp.flush()
  let ranges = sh0
    .createTextFinder("♥")
    .matchEntireCell(false)
    .matchCase(true)
    .matchFormulaText(false)
    .ignoreDiacritics(true)
    .findAll();
  ranges.forEach(function (range) {
    range.setValue(range.getValue().replace(/♥/g, "\n"));
  });

Answer 2

It would be better if you show your CSV as a text.

I think there are \r and \n inside a quote marks ". You can try to replace them with spaces if you change this line:

var csvData = Utilities.parseCsv(attachment.getDataAsString(), ",");

to this:

var s = attachment.getDataAsString().replace(/"[^"]+?"/, x => x.replace(/[\r\n]+/g, ' '));
var csvData = Utilities.parseCsv(s, ',');

Output:

Update

Here is the variant of about the same function. It does the same thing: replaces \r and \n within quote marks. But it replaces them with the special symbol. And replaces this symbol back to \n after the data is pasted on the sheet:

function get_csv() {
  var threads = GmailApp.search("Scheduled report"); // subject
  var message = threads[0].getMessages().pop(); // get last message from first thread
  var attachment = message.getAttachments()[0]; // first attachment
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName("Sheet1");

  // get the csv string and replace \n with the symbol ¶
  var s = attachment.getDataAsString().replace(/"[^"]+?"/g, x => x.replace(/[\r\n]+/g, '¶'));
  var csvData = Utilities.parseCsv(s, ',');

  sh.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
  sh.getDataRange().createTextFinder('¶').replaceAllWith('\n'); // restore \n inside cells

  GmailApp.markMessageRead(message);
}

Result:

Here is the CSV data (not sure if the \r\n are intact after copy and paste):

Activity date & time,Status change,Postcode,Introducer,Marketing source,Assigned user,Lead reference,Lead date,Signed Date,Offered Date,Completed Date,Title,First name,Last name,Lead type,Lead group,Site,Product,"Security Address, if different from above"
01/02/2022,Signed,NE3 1RD,Me,,Me,172312026,15/11/2021,None,None,None,Mr.,John,Smith,Band A,Type 1,Leads.com,Fixed 5,"123 New Road, LONDON, NE3 1RD"
01/02/2022,Signed,NE3 1RD,Me,,Me,172312026,15/11/2021,None,None,None,Mr.,John,Smith,Band A,Type 1,Leads.com,Fixed 5,"123 New Road
LONDON
NE3 1RD"