Copy and Paste in GOOGLE SCRIPT

Question

*I want to check the columns "D" and "E" on "Sheet1". *IF: => the values of column "D" and "E" are empty and not empty => I want to copy the row from A:I from Sheet1 to the next row of last row in "Sheet2"; *If there's no condition met => DO NOTHING

The code you provided is good but got an error when no condition met.

I want to achieve this using Google Apps Script.

SAMPLE1_UPDATED SAMPLE2_UPDATED

Answer 1

I believe your goal as follows.

• You want to check the columns "D" and "E" on "Sheet1".
• When the values of column "D" and "E" are empty and not empty, you want to copy the columns "A" to "I" of the row to the next row of last row in "Sheet2".
• You want to achieve this using Google Apps Script.

About If there's no condition met => DO NOTHING, I'm not sure which you want to achieve as follows.

1. When the values of column "D" and "E" are not empty or empty, you don't want to copy, even when the condition of other rows is filled.
2. When the values of column "D" and "E" are not empty or empty, you don't want to copy the rows. But the rows with the filled condition are copied.

So, in this answer, I proposed the following 2 patterns.

Pattern 1:

In this pattern, when the values of column "D" and "E" are not empty or empty, the script is not run, even when the condition of other rows is filled.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const obj = sheet1.getRange("A1:I" + sheet1.getLastRow()).getValues().reduce((o, r) => {
    o[(r[3].toString() == "" && r[4].toString() != "") ? "trueCondition" : "falseCondition"].push(r);
    return o;
  }, {falseCondition: [], trueCondition: []});
  if (obj.falseCondition.length == 0) {
    sheet2.getRange(sheet2.getLastRow() + 1, 1, obj.trueCondition.length, obj.trueCondition[0].length).setValues(obj.trueCondition);
  }
}

Pattern 2:

In this pattern, when the values of column "D" and "E" are not empty or empty, the rows are not copied. But the rows with the filled condition are copied.

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const obj = sheet1.getRange("A1:I" + sheet1.getLastRow()).getValues().reduce((o, r) => {
    o[(r[3].toString() == "" && r[4].toString() != "") ? "trueCondition" : "falseCondition"].push(r);
    return o;
  }, {falseCondition: [], trueCondition: []});
  sheet2.getRange(sheet2.getLastRow() + 1, 1, obj.trueCondition.length, obj.trueCondition[0].length).setValues(obj.trueCondition);
}

Note:

• In this sample script, the values of "Sheet1" are retrieved from the 1st row. When you want to change the start row, please modify "A1:I" to your actual situation.

References:

• getValues()
• reduce()
• setValues(values)

Added:

About your additional following 2 sample images,

how about the following sample script?

Sample script:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet1 = ss.getSheetByName("Sheet1");
  const sheet2 = ss.getSheetByName("Sheet2");
  const obj = sheet1.getRange("A1:I" + sheet1.getLastRow()).getValues().reduce((o, r) => {
    if ((r[3].toString() != "" && r[4].toString() != "") || (r[3].toString() == "" && r[4].toString() == "") || (r[3].toString() != "" && r[4].toString() == "")) {
      o.falseCondition.push(r);
    } else if ((r[3].toString() == "" && r[4].toString() != "")) {
      o.trueCondition.push(r);
    }
    return o;
  }, {falseCondition: [], trueCondition: []});
  if (obj.trueCondition.length > 0) {
    sheet2.getRange(sheet2.getLastRow() + 1, 1, obj.trueCondition.length, obj.trueCondition[0].length).setValues(obj.trueCondition);
  }
}

• In this case, you can see the rows which were not copied by console.log(obj.falseCondition).