gpt4 book ai didi

csv - 将新的 CSV 数据附加到 Google 表格

转载 作者:行者123 更新时间:2023-12-04 02:15:13 29 4
gpt4 key购买 nike

如何将导入的 CSV 数据附加到现有的 Google 电子表格中?导入后,底部有一个空行,每次后续运行代码时,我都希望它在空行结束后插入新的 CSV 数据。

这是我现有的代码:

function getCSV() {
var fSource = DriveApp.getFolderById('0B2lVvlNIDosoajRRMUwySVBPNVE'); // reports_folder_id = id of folder where csv reports are saved
var fi = fSource.getFilesByName('L661_BOM-CAD_14-12-15.csv'); // latest report file
var ss =SpreadsheetApp.openById('1WEDYfEudYsbkUhHbCxZspEbNXz3cjQIe3JdhnbFmmYA'); // data_sheet_id = id of spreadsheet that holds the data to be updated with new report data
var sd = SpreadsheetApp.getActiveSpreadsheet();
var s = SpreadsheetApp.getActiveSheet();
var target = new Array()
var sSheet = SpreadsheetApp.getActiveSpreadsheet();
sheet = SpreadsheetApp.setActiveSheet(sSheet.getSheets()[0]);

if ( fi.hasNext()) {
var file = fi.next();
var csv = file.getBlob().getDataAsString();
var csvData = CSVToArray(csv); // see below for CSVToArray function

var sheet = ss.getSheets()[0];

s.getRange(+1, 1, csvData.length, csvData[5].length).setValues(csvData);

}
};


function CSVToArray( strData, strDelimiter ){

strDelimiter = (strDelimiter || ";");

var objPattern = new RegExp(
(
// Delimiters.
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +

// Quoted fields.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +

// Standard fields.
"([^\"\\" + strDelimiter + "\\r\\n]*))"
),
"gi"
);

var arrData = [[]];

var arrMatches = null;

while (arrMatches = objPattern.exec( strData )){


var strMatchedDelimiter = arrMatches[ 1 ];
if (
strMatchedDelimiter.length &&
strMatchedDelimiter !== strDelimiter
){

// Since we have reached a new row of data,
// add an empty row to our data array.
arrData.push( [] );

}

var strMatchedValue;
if (arrMatches[ 2 ]){
strMatchedValue = arrMatches[ 2 ].replace(
new RegExp( "\"\"", "g" ),
"\""
);

} else {

strMatchedValue = arrMatches[ 3 ];
}

arrData[ arrData.length - 1 ].push( strMatchedValue );
}
return( arrData );
}

最佳答案

现在可以使用 parseCSV 实用程序对此进行简化。如果您将 fSource、fi 和 ss 变量的值传递给这个修改后的函数,它应该仍然可以工作并且不必使用 CSVToArray 方法。感谢您指出正确的方向。默认情况下,您的电子表格应该已经加载到第一张工作表 (0),因此我认为额外的“getActiveSpreadsheet”和 getActiveSheets 是多余的。

function getCSVAndAppend(spreadsheetId, folderId, filename) {
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFilesByName(filename);

var openSpreadsheet = SpreadsheetApp.openById(spreadsheetId);
var activeSheet = SpreadsheetApp.getActiveSheet();

if ( files.hasNext()) {
var file = files.next();
var csv = file.getBlob().getDataAsString();
var csvData = Utilities.parseCsv(csv);

var lastrow = activeSheet.getLastRow();
activeSheet.getRange(lastrow + 1, 1, csvData.length, csvData[0].length).setValues(csvData);
}
}

关于csv - 将新的 CSV 数据附加到 Google 表格,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/34591598/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com