根据单元格值将行移动到其他工作表 [英] Move Row to Other Sheet Based on Cell Value
问题描述
我有一个工作表,其中有一些行要根据单元格值移到另一工作表.我尝试遵循本文的解决方案(请参阅下面的内容),但是在按我想要的方式编辑脚本时遇到了麻烦.
I have a sheet with rows I'd like to move to another sheet based on a cell value. I tried following this post's solution (refer below), but I'm having trouble editing the script towards what I want it to do.
我正在为事件办理登机手续.我希望能够更改F列中的值,并使用状态更改的时间填充G列,并将行数据迁移到到达与会者"表.
I'm doing Check-Ins for an event. I would like to be able to change the value in Column F, populate Column G with the time the status changed, and for the row data to migrate to the Attendee Arrived sheet.
我相信脚本已经做到了,但是必须手动运行它.在将行数据迁移到B(到达与会者)之后,还需要删除A(事件)中的行数据.
I believe the script already does this, but one has to run it manually. It also takes care of deleting the row data in A (Event) after migrating it to B (Attendee Arrived).
我的问题是有人可以帮助我进行设置,以使脚本连续运行(在编辑时),如果我错过了某些事情,还可以完成上述所有工作吗?
My question is could someone please help me set it up in order for script to run continuously (on edit), and also accomplish all of the above if I missed something?
我认为脚本在运行时不会遵循下拉格式,因此我愿意手动输入内容.如果能够保持这种状态,那将很酷-使一个人变得更容易.
I don't believe the script will respect the drop down format as it runs so I'm willing to manually type in something. It'd be cool if it could stay that way though - makes it easier for one.
这是我正在测试的表.
这是我尝试遵循的解决方案.这一切都归功于Jason P和Ritz.
Here's the solution I tried following. All credit to Jason P and Ritz for this.
Google App脚本-Google电子表格可根据单元格值有效地移动行
谢谢D:
function CheckIn() {
// How Many Columns over to copy
var columsCopyCount = 7; // A=1 B=2 C=3 ....
// What Column to Monitor
var columnsToMonitor = 6; // A=1 B=2 C=3 ....
//TARGET SPREAD SHEETS
var target1 = "Attendee Arrived";
//Target Value
var cellvalue = "Attendee Arrived";
//SOURCE SPREAD SHEET
var ss = SpreadsheetApp.openById('1HrFnV2gFKj1vkw_UpJN4tstHVPK6Y8XhHCIyna9TLJg');
var sourceSpreadSheetSheetID = ss.getSheetByName("Event");
var sourceSpreadSheetSheetID1 = ss.getSheetByName(target1);
var data = sourceSpreadSheetSheetID.getRange(2, 1, sourceSpreadSheetSheetID.getLastRow() - 1, sourceSpreadSheetSheetID.getLastColumn()).getValues();
var attendee = [];
for (var i = 0; i < data.length; i++) {
var rValue = data[i][6];
if (rValue == cellvalue) {
attendee.push(data[i]);
} else { //Fail Safe
attendee.push(data[i]);
}
}
if(attendee.length > 0){
sourceSpreadSheetSheetID1.getRange(sourceSpreadSheetSheetID1.getLastRow() + 1,
1, attendee.length, attendee[0].length).setValues(attendee);
}
//Will delete the rows of importdata once the data is copided to other
sheets
sourceSpreadSheetSheetID.deleteRows(2,
sourceSpreadSheetSheetID.getLastRow() - 1);
}
推荐答案
尝试一下:
我想您已经知道您将需要一个可安装的onEdit触发器,并且无法在没有事件对象的情况下运行该函数来测试这种功能.
I imagine that you already know that you'll need an installable onEdit Trigger and that you can't test a function of this nature by running it without the event object.
function checkIn(e) {
var sh=e.range.getSheet();
if(sh.getName()!="Event") return;
if(e.range.columnStart==6) {
if(e.value=="Attendee Arrived"){
e.range.offset(0,1).setValue(Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "M/d/yyyy HH:mm:ss"));
var row=sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).getValues()[0];
e.source.getSheetByName("Attendee Arrived").appendRow(row);
sh.deleteRow(e.range.rowStart);
}
}
}
这篇关于根据单元格值将行移动到其他工作表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!