Saving as CSV through google script handling newline characters

Question

So I'm attempting to take a sheet from a Google Spreadsheet and save it as a .CSV file just like you can manually do. It is working fairly well, but I've ran into an issue with new lines/line feeds/carriage returns etc. I have used and modified Google's solution and some stack overflow resources that have meet my needs so far, however I'm stuck on this new line issue.

The Problem: Problem

The Cause: Cause

I will note that if I manually save the CSV file from Google Spreadsheets it looks fine, but that box you see in the cause picture is the issue I cannot resolve.

My current code:

function convertRangeToCsvFile_(csvFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var csvFile = undefined;

    // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
            //Handle special characters
            var text = data[row][col].toString();

            text = text.replace(/(\r\n|\n|\r)/g," ");
            for(var i = 0; i < text.length; i++)
            {
              if(text[i] == "\'" || text[i] == "\"" || text[i] == "\\" || text[i] == "\n" || text[i] == "\r" || text[i] == "\t" || text[i] == "\b" || text[i] == "\f")
              {
                text = spliceSlice(text,i,0,"\\");
                i++;
              }
            }
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + text + "\""; //Puts quotes around the data for strings.
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }

    }
    csvFile = csv;

    return csvFile;
  }
  catch(err) {
    Logger.log(err);
  }
}

As you can see I'm attempting to handle the new lines with what I have read about 'regex expressions' however, they don't seem to actually be doing anything for me. So perhaps I'm just using it wrong and then it will work, or I need to go about this differently.

EDIT: I've looked at the file in Notepad++ and determined that they are LF characters causing the problem in the middle of the rows. Where as my end of row \r\n is working fine.

score 1 · Accepted Answer · answered Mar 19 '15 at 11:39

1

Instead of .replace(/(\r\n|\n|\r)/g," ") try .replace("\n", " ").replace("\r", " "), I know it's essentially the same, but I'm currently using this solution without hiccups.

answered Mar 19 '15 at 11:39

Kriggs

3,731
1
15
23

I attempted to use this instead and I'm having the same problem still. – B-Stewart Mar 20 '15 at 05:49
After tinkering around with this some more, it ended up working. To be honest I don't know if your switch was what caused it, but I didn't do anything significantly different. – B-Stewart Apr 07 '15 at 17:19

score 1 · Answer 2 · answered Apr 20 '22 at 23:36

This is an old question, but I ran into it at the top of my Google search when just looking for a simple solution to handling newlines on a CSV export from Sheets.

Since it's 2022 now, you can use Sheets' pretty powerful find + replace to target all newline characters (removing them, or replacing them with a literal \n string, etc). You can then export as CSV normally.

In my example here, the "Replace with" doesn't use regex, only the search does, so I'm wanting to preserve the newlines here before export.

Saving as CSV through google script handling newline characters

2 Answers2

Linked