有多种方法可以从 Google 表格导出电子表格和/或工作表。将工作表导出为 csv 文件内置于 Google 表格(文件、下载、CSV)中。
在这种情况下,OP 引入了“标准”方法无法解决的两个复杂问题。
1)由“管道”字符分隔的字段(|
), and
2) 所有字符串字段都要用双引号括起来。
GitHub 上有多个脚本可以自动将工作表另存为 csv。导出命名表作为-csv.gs https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1作者:迈克尔·德拉森(https://gist.github.com/mderazon/9655893 https://gist.github.com/mderazon/9655893)是一个示例,我用它作为此代码的基础。但是,这些脚本遵循使用逗号作为字段分隔符的“正常”规则,并且没有对字符串进行特殊处理。
以下代码将活动工作表保存为 csv 文件,并提供管道字段分隔符和字符串周围的双引号。用户可以通过编辑“参数”表上的字段来指定这些参数,如此屏幕截图所示。
该脚本使用typeof
识别字符串和函数isValidDate
德米特罗·舍甫琴科 (Dmytro Shevchenko) 在在 JavaScript 中检测“无效日期”Date 实例 https://stackoverflow.com/questions/1353684/detecting-an-invalid-date-date-instance-in-javascript.
/*
* script to export data in all sheets in the current spreadsheet as individual csv files
* files will be named according to the name of the sheet
* author: Michael Derazon
* source: https://gist.github.com/mderazon/9655893
* adapted by Ted Bell for https://stackoverflow.com/questions/49248498/how-can-i-export-to-csv-with-pipe-delimiter
*/
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{
name: "export as csv file",
functionName: "saveAsCSV"
}];
ss.addMenu("CSV Export", csvMenuEntries);
};
function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssname = ss.getName();
var sheet = ss.getActiveSheet();
var sheetname = sheet.getSheetName();
//Logger.log("DEBUG: the name of the spreadsheet is "+ssname);//DEBUG
//Logger.log("DEBUG: the sheet name is "+sheetname);// DEBUG
//// create a folder from the name of the spreadsheet
var folder = DriveApp.createFolder(ssname.toLowerCase() + '_' + sheetname.toLowerCase().replace(/ /g, '_') + '_csv_' + new Date().getTime());
//Logger.log("DEBUG: the folder name is "+folder);//DEBUG
// append ".csv" extension to the sheet name
var fileName = ssname + '_' + sheetname + ".csv";
// convert all available sheet data to csv format
var csvFile = so_4225484202(fileName);
// create a file in the Docs List with the given name and the csv data
folder.createFile(fileName, csvFile);
Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
}
function isValidDate(date) {
return date && Object.prototype.toString.call(date) === "[object Date]" && !isNaN(date);
}
function so_4225484202(filename) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var paramsheet = ss.getSheetByName("Parameters");
var linearray = [];
var rowdata = [];
var csv = "";
var fieldvalue = "";
var param = paramsheet.getRange(2, 2, 2);
var paramValues = param.getValues();
//Logger.log("DEBUG: parameters = "+param.getA1Notation());//DEBUG
var fieldDelimiter = paramValues[0][0];
var textDelimiter = paramValues[1][0];
//Logger.log("DEBUG: field delimiter: "+fieldDelimiter+", text delim: "+textDelimiter);//DEBUG
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
//Logger.log("DEBUG: lastColumn: "+lastColumn+", lastRow: "+lastRow);//DEBUG
// Get array of values in the Data Range
var rangeValues = rangeData.getValues();
// Loop through array and build values for csv
for (i = 0; i < lastRow; i++) {
for (j = 0; j < lastColumn; j++) {
var value = rangeValues[i][j];
var theType = typeof value;
if (theType === "object") {
var testdate = isValidDate(value);
//Logger.log("if typeof is object: testdate: "+testdate);//DEBUG
var testtype = typeof testdate;
if (testtype === "boolean") {
// variable is a boolean
//Logger.log("Its a date");//DEBUG
theType = "date";
} else {
//Logger.log("Its not a date");//DEBUG
}
}
if (theType === "string") {
value = textDelimiter + value + textDelimiter;
}
rowdata.push([value]);
};
//Logger.log("DEBUG: rowdata: "+rowdata);//DEBUG
csv += rowdata.join(fieldDelimiter) + "\n";
var rowdata = [];
};
//Logger.log("DEBUG: csv: "+csv);//DEBUG
return csv;
}
This 电子表格 https://docs.google.com/spreadsheets/d/1EpbcGJ9aIdLdWXKBVB7_J0o8NSuNnjb5eeIvDAaUSZM/edit?usp=sharing包含样本数据。
包含近 1,000 条记录的工作表的处理和保存时间约为 5 秒。