谷歌工作表移动编辑如果-移动编辑如果不是

Question

我有2个谷歌表与列E是一个状态下拉与多个状态条目。我的床单叫做"sheet1"。和";Completed"。选择"完成"后在状态下拉列表中，我希望将行移动到"Completed"床单在被移动并制作"完成"之后;工作表活动，我希望将行移回"sheet1";如果我选择的不是"complete"在状态下拉列表中

这是我到目前为止所知道的。它将把我的行从sheet1移动到completed，而不是相反。如有任何帮助，不胜感激:

function onEdit(event) {
// assumes source data in sheet named Sheet1
// target sheet of move to named Completed
// test column with "10 Complete" is col 5 or E
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "Sheet1" && r.getColumn() == 5 && 
r.getValue() == "10 Complete") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Completed");
var target = 
targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
if(s.getName() == "Completed" && r.getColumn() == 5 
&& r.getValue() != "10 Complete") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet1");
var target = 
targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}
}

Answer 1

你的代码不工作的原因是因为你把你的第二个IF语句在第一个IF语句内。第二个IF语句将永远不会被执行。

你的当前代码:

function onEdit(event){

if(s.getName() == "Sheet1" && r.getColumn() == 5 && 
r.getValue() == "10 Complete") {
.....
if(s.getName() == "Completed" && r.getColumn() == 5 
&& r.getValue() != "10 Complete") {
......
}
}
}

---

解决方案:

两个IF语句应该在同一层。

工作代码:

function onEdit(event) {
// assumes source data in sheet named Sheet1
// target sheet of move to named Completed
// test column with "10 Complete" is col 5 or E
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();
if(s.getName() == "Sheet1" && r.getColumn() == 5 && 
r.getValue() == "10 Complete") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Completed");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}

if(s.getName() == "Completed" && r.getColumn() == 5 
&& r.getValue() != "10 Complete") {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Sheet1");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
s.deleteRow(row);
}
}