如果单元格包含X,则将行复制到其他工作表,然后删除行. [英] Copy Row if Cell Contains X to Different Sheet then delete row.
问题描述
我整天都在努力寻找如何做到这一点,并逐渐接近但从未达到最终状态的过程.
I have been searching all day trying to find out how to do this and get close but never to a finished state.
我想做的是在活动电子表格上,查看工作表"Happy".如果Colum G中的单元格包含"Closed",我想将该行在A:K范围之间的单元格复制到工作表"Sad".复制完成并移动数据后,我要删除复制数据的工作表"Happy"上的整行.
What I am trying to do is on active spreadsheet, look at Sheet "Happy". If a cell in Colum G contains "Closed" I want to copy the cells between range A:K for that row to sheet "Sad". Once the copy is done and the data is moved I want to then delete the whole row on sheet "Happy" that the data was copied from.
如果单元格=封闭,则将其复制到工作表并删除,否则什么也没有.
IF cell = closed, than copy to sheet and delete, else nothing.
我将每隔5分钟运行一次此脚本,我可以使用触发器执行此操作.
I will run this script every 5 min which I can do with triggers.
任何帮助将不胜感激.
这是我到目前为止尝试过的
Here is what I tried so far
function Copy() {
var sss = SpreadsheetApp.getActive()
var ss = sss.getSheetByName('Happy');
var range = ss.getRange('A:k');
var data = range.getValues();
var tss = SpreadsheetApp.getActive();
var ts = tss.getSheetByName('Sad:');
var valuesToCopy = ss.getRange(2,2,100).getValues();
ts.getRange(2,ts.getLastRows()+1,valuesToCopy.length,1).setValues(valuesToCopy);
推荐答案
function copyrange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Happy'); //source sheet
var testrange = sheet.getRange('G:G');
var testvalue = (testrange.getValues());
var csh = ss.getSheetByName('Sad'); //destination sheet
var data = [];
var j =[];
//Condition check in G:G; If true copy the same row to data array
for (i=0; i<testvalue.length;i++) {
if ( testvalue[i] == 'Closed') {
data.push.apply(data,sheet.getRange(i+1,1,1,11).getValues());
//Copy matched ROW numbers to j
j.push(i);
}
}
//Copy data array to destination sheet
csh.getRange(csh.getLastRow()+1,1,data.length,data[0].length).setValues(data);
//Delete matched rows in source sheet
for (i=0;i<j.length;i++){
var k = j[i]+1;
sheet.deleteRow(k);
//Alter j to account for deleted rows
if (!(i == j.length-1)) {
j[i+1] = j[i+1]-i-1;
}
}
}
这篇关于如果单元格包含X,则将行复制到其他工作表,然后删除行.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!