如果所有空白都位于源底部并且数据下方没有其他内容,则此操作应该有效。
function triggerOnTime() {
var SpreadSheetKeyA = "MY key";
var SpreadSheetKeyB = "MY key";
var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getSheetByName("Source Name");
var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getSheetByName("Target Name");
var startRow = 5;
var data = sheet1.getRange(startRow,11,sheet1.getLastRow() - startRow + 1,5).getValues();
var time = new Date ().toJSON().slice(0,10);;
for (var r = 0; r < data.length; r++) {
data[r].unshift(time);
}
if(data.length > 0 ) {
sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);
sheet2.getRange(sheet2.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}
}
如果由于某种原因无法满足上述条件并且您需要将行数硬编码为 40,您可以尝试以下操作:
function triggerOnTime() {
var SpreadSheetKeyA = "MY key";
var SpreadSheetKeyB = "MY key";
var sheet1 = SpreadsheetApp.openById(SpreadSheetKeyA).getSheetByName("Source Name");
var sheet2 = SpreadsheetApp.openById(SpreadSheetKeyB).getSheetByName("Target Name");
var startRow = 5;
var data = sheet1.getRange(5,11,40,6).getValues();
var time = new Date ().toJSON().slice(0,10);;
for(var i = data.length - 1; i > -1; i--) {
if(data[i].join("").length == 0) data.splice(i, 1);
}
for (var r = 0; r < data.length; r++) {
data[r].unshift(time);
}
if(data.length > 0 ) {
sheet2.insertRowsAfter(sheet2.getLastRow(), data.length);
sheet2.getRange(sheet2.getLastRow()+1, 1, data.length, data[0].length).setValues(data);
}
}