Google Sheets, populate extra columns when new rows added

Question

I'm new to scripting for Google Sheets.

I'm making a sheet for mobile users to enter payment amount and recording the payment time. The sheet receives data from an external form into new bottom rows. The "payment amt" and "payment time" columns don't exist/come from the form. I made a simplified example sheet here, which doesn't take input from forms, but it still have the same problems. Please feel free to look and edit your suggestions. https://docs.google.com/spreadsheets/d/1Z6U6jf7Rv6Gd_kN7hAnNqiQE-iXo5sGlLCv4iDd06mE/edit#gid=0

My goal:

• Let the mobile app user be able to use the drop-down "payment amt" cells, which then triggers this: if payment >= 20, then the "payment time" cell records the time when the "payment amt" cell on the row was last changed.

Problem:

• With the methods I've tried, when a new row comes in, I have to manually copy and paste the extra column in. Very tedious. If I copy and paste a bunch of rows for the extra columns before hand, the new data rows will come in below the last row I pasted.
• All time cells on the entire sheet change to the current time whenever the sheet is edited.

What I've tried:

• Method 1: in "payment time 1" cells, add the below function and copy/paste into new rows. The D2 cell would have: =IFS(C2<20, "", C2>=20, NOW()).
• Method 2: in "payment time 2" column first row, add: ={"payment time 2";ARRAYFORMULA(if(C2:C<20,"",text(NOW(),"mm/dd/yyyy hh:mm:ss")))}

I have a hunch that the NOW() function is run when the sheet is edited, and all the cells that uses that function get overwritten with current time. It's confusing why though, because I haven't written any scripts and there's nothing in the triggers of the sheet.

Thank you for reading. If anyone could shed a light on what's going on, or know a better way to do this, I'd much appreciate it!

Answer 1

Try this. Let me know if this work for you or not.

   function onEdit(e){
     const range = e.range;
     const column_index = range.columnStart;
     const sheet = range.getSheet();
     if(column_index == 3 && range.getValue() >= 20){
       //change the GMT to your local time
       sheet.getRange(range.rowEnd,4).setValue(Utilities.formatDate(new Date(),"GMT","MM/dd/yyyy hh:mm:ss"));
     }
    }