Autofill a column in Google Sheets as new rows are submitted through Google Forms

Question

I have a google form that has fields taking up 7 columns in the response sheet. I have reserved the 8th column to compute few fields (basically a formula) and generate a unique ID for that response. I know that when new responses are added, I can drag the box of the 8th column field all the way down to the given number of rows to auto-fill the column. But this type of auto-fill requires a manual effort. What I want is an automated system to keep filling in the column with my formula.

I have tried pulling the column down beyond the current number of rows in a hope for it to auto calculate when the new row is added but a new response simply overwrites the entire row instead of filling in just the seven columns which deletes the 8th column in that row.

The spreadsheet is https://docs.google.com/spreadsheets/d/1HM2dDRtkF_KlQ8SKoeW2YmjP2dttYAk1_4iCYBVEN8o/edit?usp=sharing The responses fill up to column H (Member #3) and my desired column is column I (Registration ID) which is aided by column J, K and L.

Answer 1

You can try using

=ARRAYFORMULA(IF(ROW(M:M)=1,"Registration_ID",IF(C:C="UM-DAE CBS, Mumbai", "cbs"&"_"&J:J, IF(C:C="ICT, Mumbai", "ict"&"_"&K:K, IF(C:C="IISER, Pune", "iiser"&"_"&L:L,"waiting for a response...")))))

Just some explanation:

//This one is just to label the first row as Registration_ID so you can replace M:M with any column you want.
 IF(ROW(M:M)=1,"Registration_ID"

My first time answering in Stack Exchange so I'm not familiar with the formatting.

Also a heads up, if you wanted to use ArrayFormula() with an If(AND()) or If(Or()) function, just know that the ArrayFormula() requires you to use arithmetic functions like "*" or "+" instead.

So IF(AND(A,B)) will be IF(A*B).

Answer 2

Use the "CopyDown" add-on for Google!

On your Google Sheet that your Form posts to, click Add-Ons > Get Add-Ons > type "copyDown".

This add-on quickly & easily allows the sheet to automatically copy the formula from one of your top rows (adjustable) to the rest of the form's submissions.

Love it!