• Row 1 in both Sheet 1 and Sheet 2 are frozen, with column names.
• Once I have a working script, I will setup a corresponding onChange trigger to execute the script.
• Due to the way new data containing rows are added to Sheet 1, the script itself cannot be written with onChange/onEdit syntax, I just need a function that works, and I'll use an onChange trigger to execute it.

Step 1:

Sheet 1 - a new data containing row was added (Row 2).

Sheet 2 - the data from Sheet 1 - Row 2 should be copied to the next available row, which in this case would be Row 2.

Step 2:

Sheet 1 - after the data from Row 2 has been copied to the next available row in Sheet 2, then Row 2 should be cleared.

Step 3:

Sheet 1 - a new data containing row was added (Row 2).

Sheet 2 - the data from Sheet 1 - Row 2 should be copied to the next available row, which in this case would be Row 3.

Step 4:

Sheet 1 - after the data from Row 2 has been copied to the next available row in Sheet 2, then Row 2 should be cleared.

...and so on

• Sheet 1 should only ever have two rows. Row 1 for the column names and Row 2, which will be blank to start, then filled with data (which should then be copied over to Sheet 2), then cleared so that it is blank again, repeat.
• Sheet 2's row count should be continually growing, one by one, as individual data containing rows populate into Sheet 1 - Row 2 over time and get copied over.
• The incoming data that fills Sheet 1 - Row 2 comes from an external app, I'm never manually entering data into that sheet/row. The incoming data fills all columns in Sheet 1 - Row 2, all at once.

CodePudding user response：

Try

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('