copy - 清除数据而不是删除行

Question

我有下面的脚本可以满足我的需要，除了它删除行的结尾。我需要的是从Sheet1复制并粘贴到Sheet2，并清除sheet1中除A列之外的行。

function copyrange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Sheet1'); //source sheet
var testrange = sheet.getRange('D:D');
var testvalue = (testrange.getValues());
var csh = ss.getSheetByName('Sheet2'); //destination sheet
var data = [];
var j =[];

//Condition check in D:D; If true copy the same row to data array
for (i=0; i<testvalue.length;i++) {
if ( testvalue[i] == 'x') {
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 ** I Need these to clear data 
from B:E for row that it copied and pasted rather then deleted it out.***
for (i=0;i<j.length;i++){
var k = j[i]+1;
sheet.deleteRow(k); // I have tried sheet.Clearcontents, with no sucess

//Alter j to account for deleted rows
if (!(i == j.length-1)) {
j[i+1] = j[i+1]-i-1;
}
}
}

标签： copyrowpaste

Answer 1

我能够找到另一种清除我需要的方法，即通过另一个清除任何橙色单元格的脚本。我使用条件格式来让它工作。见下文。谢谢....

function copyandclearorange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Research'); //source sheet
var testrange = sheet.getRange('L:L');
var testvalue = (testrange.getValues());
var csh = ss.getSheetByName('Log'); //destination sheet
var data = [];
var j =[];

//Condition check in L:L; If true copy the same row to data array
for (i=0; i<testvalue.length;i++) {
if (  testvalue[i] == 'x') {
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);


var sheet = SpreadsheetApp.getActive().getSheetByName('Research');
var range = sheet.getDataRange();
var bgColors = range.getBackgrounds();
for (var i=0; i<bgColors.length; i++) {
for (var j=0; j<bgColors[i].length; j++) {
  if (bgColors[i][j] === '#ff9900') {
    range.getCell(i+1,j+1).clearContent();
  }
}
}
}