我正在使用以下代码导入一个名为"家庭护理.csv";进入Google Sheet。问题是,在这个csv文件的一些单元格中,有几个信息用换行符分隔,因此谷歌工作表文件是一个完美的混乱,因为这些信息被放在下面的行中,而没有保存在单元格中。你知道保持细胞原状的方法吗?
function DeleteTracker() {
var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home care');
ss.getRange('A2:Z50000').clearContent();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var maxRows = sheet.getMaxRows();
var lastRow = sheet.getLastRow();
if (maxRows-lastRow != 0)
{
sheet.deleteRows(lastRow+1, maxRows-lastRow);
}
}
function importCSVFromGoogleDrive() {
var file = DriveApp.getFilesByName('Home care.csv').next();
var csvData = Utilities.parseCsv(file.getBlob().getDataAsString());
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home care');
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
提前非常感谢Isabelle
尝试以这种方式更改函数importCSVFromGoogleDrive()
:
function importCSVFromGoogleDrive() {
var file = DriveApp.getFilesByName('Home care.csv').next();
// get the contains as a string
var str = file.getBlob().getDataAsString();
// replace all break lines inside quotes with ¶
var m = str.match(/"[^",]*?[nr]+[^"]*?"/g); // comma ',' delimited csv
if (m) m.forEach(t=> str=str.split(t).join(t.replace(/[nr]+/g,'¶')));
// parse the string and put it on the sheet
var csvData = Utilities.parseCsv(str);
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Home care');
sheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData);
// replace ¶ back to break lines within cells
sheet.createTextFinder('¶').replaceAllWith('n');
}