CSV blank values due to new line character

David Salomon 0
Viewed 41

I'm parsing a CSV using Utilities.parseCsv() but some cells have the new line character and at the moment I parse the value, it creates three different records out of one. Is there a way to remove /n to have it as a one cell.

Sample CSV

enter image description here

Parsed CSV

id comment
1 one Line
New Line
Third Line
2 one Line
3 One line
Second Line

Parsed Array

[
  ["id","comment"],
  [1,"One Line"],
  ["","New Line"],
  ["", "Third Line"],
  [2,"One Line"],
  [3,"One Line"],
  ["","New Line"]
]

Note: .replace() or .replaceAll() won't work on the parsed csv, because once it is parsed with Utilities.parseCsv() it creates a new array element.

Ideally it would work if the array element comes like this:

["1","One Line     
      Second Line"]

but instead it is parsed like this:

["1", "One line"],
["Second line",""]
google-sheets google-apps-script javascript
2 Answers

I like to replace '\n' and ',' in the content with tildes of different lengths

function makeCsv() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet4");
  const vs = sh.getDataRange().getValues();
  let row;
  let a = vs.map((r, i) => {
    row = '';
    r.forEach((c, j) => {
      if (j > 0) {
        row += ',';
      }
      row += c.toString().replace(/\n/g, '~~').replace(/,/, '~');
    })
    row += '\r\n';
    return row;
  });
  Logger.log(a.join(''));
  return a.join('');
}

function loadCsv() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const csv = makeCsv();
  const a = Utilities.parseCsv(csv);
  let row;
  const vs = a.map((r, i) => {
    row = [];
    r.forEach((c, y) => {
      row.push(c.toString().replace(/~~/g, '\n').replace('~', ','));
    })
    return row;
  })
  sh.clearContents();
  sh.getRange(1, 1, vs.length, vs[0].length).setValues(vs);
}
user30hu0

You can use replaceAll() to replace values in strings.

const stringWithLotsOfNewlines = `A
text with 
newline characters 
in it`;
console.log(`-- Before --`)
console.log(stringWithLotsOfNewlines);
const stringWithoutNewlines = stringWithLotsOfNewlines.replaceAll("\n", "");
console.log(`-- After --`)
console.log(stringWithoutNewlines);

Mushroomator0
Related