Copy cell from on sheet to another, if other cell

Question

What I want, is to copy a cell(s) from one sheet to another, only if another cell in the same row (different column) have/has a particular value(s) in Google Sheets.

Ideally, I would like this to be live; if I add a row in the first sheet and the criterion matches, the second sheet will update too. Here's an example:

Sheet one

Column A | Column(s)…  | Column D
=================================
Hello    |             | Yes
World!   |             | Yes
Foo      |             | Maybe
Bar      |             |

Sheet two

Column A | Column(s)…  | Column D
=================================
Hello    |             |
World!   |             |
Foo      |             |
         |             |

So in this example, my criteria is that if the row's cell in Column D equals either Yes or Maybe, that row's cell in Column A is copied into the second sheet.

Answer 1

Paste below script in the script editor (of a google sheet that is) and save the project. Then try editing col D of sheet1 (values: 'Yes' or 'Maybe') and see if the row moves... Feel free to change the sheetnames if needed...

function onEdit(e) {
var ss = e.source,
    sheet = ss.getActiveSheet();
if (sheet.getName() !== 'Sheet1' || e.range.columnStart !== 4 || e.value !== 'Yes' && e.value !== 'Maybe') return;
e.source.getSheetByName('Sheet2')
    .appendRow(e.range.offset(0, -3, 1, 4)
        .getValues()[0]);
sheet.deleteRow(e.range.rowStart);
}

Answer 2

Please try:

=query('Sheet one'!A:D,"Select A where D='Yes' or D='Maybe'")