通过处理换行符的谷歌脚本以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)/ 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屋!