我有一个电子表格,上面有一个清单,要在事件发生前60天、30天和1天填写。这些床单被称为"60天"、"30天"one_answers"开球"。我试图将未勾选的行自动添加到下一个工作表(因此,"60天"上的任何未选中的行都附加到"30天",并从"30天"到"开球"—此外,通过"勾选"我的意思是"x"被输入到单元格中)。我已经在代码上有了一个很好的开始,但我一定是做错了什么-因为它似乎没有做任何事情(原谅我对javascript的无知,我对编码的尝试始于上周codecademy和在这些论坛上浏览了很多)。我觉得我可能遗漏了一些非常基本的东西,我会欢迎任何我能得到的帮助。这是我得到的…
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = ss.getActiveCell();
var i = 4;//have this number be your first row of data
if(r.getColumn()===5){ // this indicates the column that, if left blank, will mean the row should be copied to the next sheet
if (ss.getActiveSheet().getName()=="60-Day"){
var source_sheet = ss.getSheetByName("60-Day");
var target_sheet = ss.getSheetByName("30-Day");
var last_row = target_sheet.getLastRow();
while(i<=last_row){
if(source_sheet.getValue("E"+i)!="x"){
var source_range = source_sheet.getRange(i,2,1,6); // indicate the columns you'd like to copy
var target_range = target_sheet.getRange(last_row + 1,2,1,6); // indicate the columns where you'd like to paste
source_range.copyTo(target_range);
i++;
SpreadsheetApp.flush();
}else{
i++;
}
}
};
else if (ss.getActiveSheet().getName()=="30-Day"){
var source_sheet = ss.getSheetByName("30-Day");
var target_sheet = ss.getSheetByName("Kickoff");
var last_row = target_sheet.getLastRow();
while(i<=last_row){
if(ss.getValue("E"+i)!="x"){
var source_range = source_sheet.getRange(i,2,1,6);//indicate the columns you'd like to copy
var target_range = target_sheet.getRange(last_row + 1,2,1,6);//indicate the columns where you'd like to paste
source_range.copyTo(target_range);
i++;
SpreadsheetApp.flush();
}else{
i++;
}
};
}
}
}
就是这样——这就是我遇到更多麻烦的地方。如果一行已被推到目标工作表,并在目标工作表上被选中,我希望它从目标工作表中消失,我希望源工作表中的原始行现在显示它已被选中。此外,如果在源工作表上勾选了先前的空白行,我希望已经推到目标工作表的副本消失。这就是我开始迷失方向的地方。我该如何实现呢?
我想你会希望在这个…
function onEdit() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var r = ss.getActiveCell();
var i = 4;//have this number be your first row of data
if(r.getColumn()===5){ // this indicates the column that, if left blank, will mean the row should be copied to the next sheet
if (ss.getActiveSheet().getName()=="60-Day"){
var source_sheet = ss.getSheetByName("60-Day");
var target_sheet = ss.getSheetByName("30-Day");
var source_last_row = source_sheet.getLastRow();
var target_last_row = target_sheet.getLastRow() + 1;
while(i<=source_last_row){
if(source_sheet.getRange("E"+i).getValue() !="x"){
var source_range = source_sheet.getRange(i,2,1,6); // indicate the columns you'd like to copy
var target_range = target_sheet.getRange(target_last_row,2,1,6); // indicate the columns where you'd like to paste
source_range.copyTo(target_range);
//var new_last_row = target_last_row - 1;
source_sheet.deleteRow(i); // delete source row after copy
i++;
SpreadsheetApp.flush();
target_sheet.getRange('E'+target_last_row).setValue('x'); // insert 'x' back into target
}else{
i++;
}
}
}
else if (ss.getActiveSheet().getName()=="30-Day"){
var source_sheet = ss.getSheetByName("30-Day");
var target_sheet = ss.getSheetByName("Kickoff");
var source_last_row = source_sheet.getLastRow();
var target_last_row = target_sheet.getLastRow() + 1;
while(i<=source_last_row){
if(source_sheet.getRange("E"+i).getValue() !="x"){
var source_range = source_sheet.getRange(i,2,1,6);//indicate the columns you'd like to copy
var target_range = target_sheet.getRange(target_last_row,2,1,6);//indicate the columns where you'd like to paste
source_range.copyTo(target_range);
//var new_last_row = target_last_row - 1;
source_sheet.deleteRow(i); // delete source row after copy
i++;
SpreadsheetApp.flush();
target_sheet.getRange('E'+target_last_row).setValue('x'); // insert 'x' back into target
}else{
i++;
}
}
}
}
}