I am trying to loop through rows within a spreadsheet and identify if a particular row has the key word "hello" and move that entire row into a new spreadsheet.
I have attempted the following code. The code works for the first row but doesn't loop through and stops after the first row. Expanding the range selection to "C1:E32" does not help.
function Edit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activatedSheetName = ss.getActiveSheet().getName();
var ActiveSheet = ss.getSheetByName("ActiveSheet"); // source sheet
var MoveDatatoThisSheet = ss.getSheetByName("MoveDatatoThisSheet"); // target sheet
var re = new RegExp(/(Hello)/i);
var startRow = 1;
var endRow = ss.getLastRow();
var getRange = ss.getDataRange();
var getRow = getRange.getRow();
for (var ree = startRow; ree <= endRow; ree++) {
// if the value in column D is "Approved", move the row to target sheet
cellValue = ss.getRange("C1:E1");
if (cellValue.getValue().match(re)) {
// insert a new row at the second row of the target sheet
MoveDatatoThisSheet.insertRows(2, 1);
// move the entire source row to the second row of target sheet
var rangeToMove = ActiveSheet.getRange(/*startRow*/ getRow, /*startColumn*/ 1, /*numRows*/ 1, /*numColumns*/ ActiveSheet.getMaxColumns());
rangeToMove.moveTo(MoveDatatoThisSheet.getRange("A2"));
// add date and time of when approved to target row in column E
MoveDatatoThisSheet.getRange("E2").setValue(Date());
// delete row from source sheet
ActiveSheet.deleteRow(cellValue, 1);
}
}
}
See Question&Answers more detail:os