Adjust Apps Script Function to check entire Column instead of single Row-CodePudding

I have an onEdit function that creates an array and uses Range.setValues to "move" that row to another worksheet.

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {
    var sheetName;
    switch (e.value) {
      case "Pending":
        sheetName = "Pending";
        break;
      case "In Hand":
        sheetName = "In Hand";
        break;
    }
    if (sheetName) {
      const dest = e.source.getSheetByName(sheetName);
      const srcRange = src.getRange(r.getRow(),1,1,18);
      const formulas = srcRange.getFormulas()[0];
      const values =  srcRange.getValues();
      formulas.forEach((formula,i) => {
        if(formula !== '') values[0][i] = formula;
      });
      dest.getRange(dest.getLastRow() 1,1,1,18)).setValues(values);
      src.deleteRow(r.getRow();
    }
  }
}

The function works but at the moment it only checks Column 2 - Row 2 shown here:

If I change the following Status nothing happens:

I know this is because the function is not checking the entire column, it's only checking the value change in Active Sheet - Column 2 - Row 2.

What I need help with is to adjust the range and ensure the whole of Column 2 is checked - and if a status is changed in Row 5, the Row 5 data is moved - and so forth.

CodePudding user response：

Replace r.getRow() == 2 by r.getRow() >= 2 in the following line

if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {

The above because the first row has the column headers and it's very unlikely that you want that the headers row be moved in case that column header of column B be edited.

Resources

https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators

CodePudding user response：

Try to remove from this line:

if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {

The condition r.getRow() == 2 . This way:

if (r.getColumn() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {

CodePudding user response：

The if condition in your code will only works if the user edited a cell in Row 2 Column B of your sheet due to this condition r.getRow() == 2.

If you want to check the entire column starting from row 2, change your code

From:

if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {

To:

if (r.getColumn() == 2 && r.getRow() > 1 && r.getWidth() == 1 && r.getHeight() == 1) {

This will ensure that the script will only apply the "move" logic if a cell is edited in the column B starting from row 2.