google-sheets - 行消失在复选框单击 GoogleAppscripts

Question

我以前在这里收到过关于相同代码的帮助，它工作得非常好！我非常感谢 stackoverflow 以及所有互相贡献和帮助的人。我们已经使用此表一段时间了，并且注意到行丢失了。现在，用户可以选中一个框以将选定的行移动到工作表中的另一个选项卡。有时，不是所有时候，它会选择一行，只将其下方的行移动到预期的工作表并删除实际选择的行。在哪里？我不知道......所以！我从一张新表重新开始，它仍在发生。我想我把它缩小到引用“.getLastRow”命令的行。我试过 .getActiveRow 但没有用。是否有替代 getLastRow 仅特定于复选框选择的行的替代方法？我认为它认为下面的行是最后一行并将其拉过来。

另外，如果您知道更好的方法，请告诉我。是否有可以做得更好的公式或数组？我有把事情复杂化的习惯......所以也许代码不是必需的？无论如何，谢谢你的时间！！

使用的其他公式： "=ARRAYFORMULA('Intake Responses'!B2:L)" 和 "=query('To ABDI'!A2:N, "SELECT * where J = 'No'")"

失踪视频

链接到新工作表

代码：

function onForm(){
  
  var s = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sort");
  var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("To ABDI");
  var r = s.getLastRow();
  var col = s.getLastColumn();
  var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,11);
    s.getRange(r, 1, 1,col).copyTo(target);
    targetSheet.getRange('O2:O').uncheck();
    targetSheet.getRange(targetSheet.getLastRow() + 0,15).activate().insertCheckboxes();
}

function onEdit() {
  // assumes source data in sheet named Upcoming
  // target sheet of move to named Current
  // getColumn with check-boxes is currently set to column 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var r = SpreadsheetApp.getActiveRange();

  if(s.getName() == "To ABDI" && r.getColumn() == 15 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName('Schedule');
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,15);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
    targetSheet.getRange('O2:O').uncheck();
    targetSheet.getRange(targetSheet.getLastRow() + 0,16).activate().insertCheckboxes();
  }
}

function onEditF() {
  // assumes source data in sheet named Upcoming
  // target sheet of move to named Current
  // getColumn with check-boxes is currently set to column 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var r = SpreadsheetApp.getActiveRange();

  if(s.getName() == "Schedule" && r.getColumn() == 15 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName('Followup');
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,15);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
    targetSheet.getRange('O2:O').uncheck();
    targetSheet.getRange(targetSheet.getLastRow() + 0,16).activate().insertCheckboxes();
  }
}

function onEditB() {
  // assumes source data in sheet named Upcoming
  // target sheet of move to named Current
  // getColumn with check-boxes is currently set to column 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var r = SpreadsheetApp.getActiveRange();

  if(s.getName() == "Followup" && r.getColumn() == 15 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName('Schedule');
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,15);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
    targetSheet.getRange('O2:O').uncheck();
    targetSheet.getRange(targetSheet.getLastRow() + 0,16).activate().insertCheckboxes();
  }
}

function onEditD() {
  // assumes source data in sheet named Upcoming
  // target sheet of move to named Current
  // getColumn with check-boxes is currently set to column 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var r = SpreadsheetApp.getActiveRange();

  if(s.getName() == "Schedule" && r.getColumn() == 16 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Done");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,16);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

function onEditFD() {
  // assumes source data in sheet named Upcoming
  // target sheet of move to named Current
  // getColumn with check-boxes is currently set to column 1 or A
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var r = SpreadsheetApp.getActiveRange();

  if(s.getName() == "Followup" && r.getColumn() == 16 && r.getValue() == true) {
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Done");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,16);
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    s.deleteRow(row);
  }
}

标签： google-sheetsgoogle-sheets-formula

Answer 1

我认为代码中的问题之一是使用activeRange以及activeSheet何时可以使用事件对象。

修改后的脚本

对我来说，在更改了那段代码并制作了一份工作表的副本后解决了这个问题：

function onEdit(e) {
  // Moved the checked row in `To ABDI` to `Schedule` 
  // And removing the moved row and uncheck
  var ss = e.source;
  let r = e.range;
  var s = e.range.getSheet();

  if(s.getName() == "To ABDI" && r.getColumn() == 15 && r.getValue() == true) {
    // Get edited range (row and number of columns)
    var row = r.getRow();
    var numColumns = s.getLastColumn();
    
    // Retrieveing the target sheet and range
    var targetSheet = ss.getSheetByName('Schedule');
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1,1,1,15);
    
    s.getRange(row, 1, 1, numColumns).moveTo(target);
    
    // Delete moved sheet
    s.deleteRow(row);

    targetSheet.getRange('O2:O').uncheck();
    targetSheet.getRange(targetSheet.getLastRow() + 0,16).activate().insertCheckboxes();
  }
}

结果

修改后的解决方案的 GIF

参考

• 简单触发器
• 事件对象
• 当前选择