google-sheets-api - 如何在 Google 表格脚本中添加其他源表格和目标表格？

Question

我设置了一个 Google 工作表来检查指定列中源工作表上的单元格是否接收到值，并且当接收到值时，将该行复制到目标工作表并从源工作表中删除该行。

如果源表没有改变，下面的代码似乎可以工作：

function onEdit(event) {

    var editedCell;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();

  if(s.getName() == "Job Schedule" && r.getColumn() == 50 && r.getValue() == "X") {   //the number (50=AX) must match the distance for the 'SHIPPING/SHIP' column
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job History");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();

    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);

}
     if(r.getValue() == "H") {  //the number (50=AX) must match the distance for the 'SHIPPING/SHIP' column
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job Holding");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();

    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);

} }

...但是当我尝试反转源表和目标表时，什么也没有发生。我在这里做错了什么？

function onEdit(event) {

    var editedCell;
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();

  if(s.getName() == "Job Schedule" && r.getColumn() == 50 && r.getValue() == "X") {
  //the number (50=AX) must match the distance for the 'SHIPPING/SHIP' column
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job History");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();

    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);

}

  if(r.getValue() == "H") {  //the number (50=AX) must match the distance for the 'SHIPPING/SHIP' column
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job Holding");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();

    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);

}

  if(s.getName() == "Job History" && r.getColumn() == 50 && r.getValue() == "R") {  //the number (50=AX) must match the distance for the 'SHIPPING/SHIP' column
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job Schedule");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();

    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);

}



if(s.getName() == "Job Holding" && r.getColumn() == 50 && r.getValue() == "R") {  //the number (50=AX) must match the distance for the 'SHIPPING/SHIP' column
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Job Schedule");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1, 1, numColumns);
    var source = s.getRange(row, 1, 1, numColumns);
    var notes = source.getNotes();

    source.copyTo((target), {contentsOnly:true});
    target.setNotes(notes);
    s.deleteRow(row);

}
}

这是日志所说的：

2018-05-29 10:09:30.409 EDT 目标范围的坐标超出了工作表的尺寸。在 onEdit(代码:63) 展开全部 | 折叠所有 { insertId: "qxgz0dg15to7zh" jsonPayload: {
context: { reportLocation: {…} } 消息："目标范围的坐标超出工作表的尺寸。在 onEdit(Code:63)" serviceContext: { service: "AKfycbxhBfEl0hoqtNu87yC2n5H8MpwYDUhojBjI_G9okmTG" } } labels: { script.googleapis.com/process_id: "EAEA1GOzXOP8tCPAT0uKRL24EjcxsGpBqJFr9-Tq8Z1rNGrlkYLWlWf7JzvIN1u0tLRPxGjJHjquTAEXXw4hn7vCf2T_UloDjcJQFHKHeKh58fJEXGg7gqfHut_j-zFd__MdCWgLRWiiOlNvTEI-f5CYhVstuN4NyZC-ulQ" script.googleapis.com/project_key: "M-4Ir7ZfvYpEjdXYSVA5_ukuLxhikTIQl"
script.googleapis.com/user_key: "APHrgwr1ptfqh8OKASadCsz3luoVac92py5po4eQivhAsyqN3K0IxIAKMJNxdckvOON6dS9sjCXj" } logName: "projects/project-id-6367347185723609045/logs/script.googleapis.com%2Fconsole_logs" receiveTimestamp: "2018-05-29T14:09:31.411762110Z" resource: {
标签：{function_name：“onEdit”invocation_type：“自定义函数”project_id：“project-id-6367347185723609045”}类型：“app_script_function”}严重性：“ERROR”时间戳：“2018-05-29T14：09：30.409Z”}

标签： google-sheets-api