仅将活动行从一个电子表格复制到另一个电子表格

Question

我想创建一个脚本来将表单的活动行（TIME MACHINE）复制到另一个Google电子表格的表单（数据库）。我也想发送“1”到复制行的第13行。如果有人能指导我，那将是非常棒的。下面给出的代码只能在同一电子表格中复制。

function endTime() { 

transfer("1E3CWWohirN4DD_f_23WWBElYrhqnAs_27crrxowFjFA", "TIME MACHINE", "TIME MACHINE2") 

} 

function transfer(targetId, sourceSheetName, targetSheetName) { 

var d = new Date(); 
var offset = -d.getTimezoneOffset()/60; 
var h = d.getHours(); 
var h = h >= 13? h - 12: h; 
h = h == 0? 12: h; 
var m = m < 10? "0" + d.getMinutes(): d.getMinutes(); 
var s = d.getSeconds(); 
var ss = SpreadsheetApp.getActiveSpreadsheet(); 
var sourceSheet = ss.getSheetByName(sourceSheetName); 
var last = sourceSheet.getActiveRange().getRow(); 
var data = sourceSheet.getRange(last, 1, 1, 14).getValues(); 
var time1 = h + ":" + m + ":" + s; 

sourceSheet.getRange(last, 28).setValue(time1); 


var tamma = sourceSheet.getRange(last, 11).getValue(); 

// copy data 
var ss2 = SpreadsheetApp.openById(targetId); 
var targetSheet = ss2.getSheetByName(targetSheetName); 
//get last row 
var lastRow = targetSheet.getLastRow(); 
//write data 
targetSheet.getRange(lastRow + 1, 1, data.length, data[0].length).setValues(data); 
//write "1" into column 13 
targetSheet.getRange(lastRow + 1, 13, data.length, 1).setValue("1"); 
targetSheet.getRange(lastRow + 1, 11, data.length, 1).setValue(tamma); 
} 

Answer 1

你的代码只需要一点点重组，你需要从源表获得正确的数据。

这应该工作：

function main() { 

transfer("1mEluY-_K9oBvbxzASKn25YZ8BgF_naLl0XNitb4zIA8", "Sheet1", "Sheet1"); 

} 
function transfer(targetId, sourceSheetName, targetSheetName) { 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var sourceSheet = ss.getSheetByName(sourceSheetName); 
    var last = sourceSheet.getActiveRange().getRow(); 
    var data = sourceSheet.getRange(last, 1, 1, 14).getValues(); 

    // copy data 
    var ss2 = SpreadsheetApp.openById(targetId); 
    var targetSheet = ss2.getSheetByName(targetSheetName); 
    //get last row 
    var lastRow = targetSheet.getLastRow(); 
    //write data 
    targetSheet.getRange(lastRow + 1, 1, data.length, data[0].length) 
      .setValues(data); 
    //write "1" into column 13 
    targetSheet.getRange(lastRow + 1, 13, data.length, 1) 
      .setValue("1") 

}