Formatting Issues with CSV import to Google Sheets from Gmail-CodePudding

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:

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"));
  });

CodePudding user response：

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"