通过处理换行符的谷歌脚本以CSV格式保存 [英] Saving as CSV through google script handling newline characters

问题描述

因此，我试图从Google Spreadsheet中获取工作表，并将其保存为.CSV文件，就像您手动执行的那样。它工作得很好，但我遇到了一个新行/换行/回车等问题。我已经使用并修改了 Google的解决方案以及一些符合我目前需求的堆栈溢出资源，但是我一直在困扰这个新行。

问题：

原因：
p>

我会注意到，如果我手动从Google Spreadsheets中保存CSV文件，它看起来很好，但是您在原因图片中看到的那个框是我无法解决的问题。

我的当前代码：

pre $函数convertRangeToCsvFile_（csvFileName，sheet） {
//在电子表格中获取可用的数据范围
var activeRange = sheet.getDataRange（）;
尝试{
var data = activeRange.getValues（）;
var csvFile = undefined;

//遍历范围中的数据并用csv数据创建一个字符串
if（data.length> 1）{
var csv =; （var var = 0; row< data.length; row ++）{
for（var col = 0; col< data [row] .length; col ++）{
/ /处理特殊字符
var text = data [row] [col] .toString（）;

text = text.replace（/（\r\\\
| \\\
| \r）/ g，）;
for（var i = 0; i< text.length; i ++）
{
if（text [i] ==\'|| text [i] == \|| text [i] ==\\|| text [i] ==\\\
|| 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 +\; //引用字符串的数据引用
}
}

//加入每行的列
//将回车添加到每行的结尾，除了最后一行
if（row< data。长度1）{
csv + = data [row] .join（，）+\r\\\
;
}
else {
csv + = data [row];
}
}

}
csvFile = csv;

return csvFile;
}
catch（err）{
Logger.log（err）;






$ b

正如你所看到的，我试图处理但是，我已经阅读了有关正则表达式的新行，但他们似乎并没有为我做任何事情。所以也许我只是使用它错了，然后它会起作用，或者我需要改变它。

编辑：我在Notepad ++中查看了该文件，并确定它们是导致行中间问题的LF字符。在行结束的地方，\r\\\
工作正常。

替换（/（\ r \ n | \\\
| \r）/ g，）尝试 .replace（\\\
， ）.replace（\r，），我知道它本质上是一样的，但我目前正在使用这个解决方案而没有打嗝。

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:

The 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.

解决方案

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.

这篇关于通过处理换行符的谷歌脚本以CSV格式保存的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！