2

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
AshishB
  • 747
  • 7
  • 18
  • I added the spreadsheet. I just want the 8th column to populate automatically as responses from google forms are added to the existing sheet – AshishB Dec 24 '16 at 16:04
  • The excel tag is for questions about Excel, not about any spreadsheet. – Rubén Dec 24 '16 at 21:24
  • 1
    Possible duplicate of [Make Google Spreadsheet Formula Repeat Infinitely](http://stackoverflow.com/questions/19039665/make-google-spreadsheet-formula-repeat-infinitely) – Rubén Dec 24 '16 at 21:32
  • You can use the [getLastRow() method LINK](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getlastrow) to get the number of the last row, then you could copy the formula down, or insert a formula. [Link Apps Script - setFormula()](https://developers.google.com/apps-script/reference/spreadsheet/range#setformulaformula) – Alan Wells Dec 26 '16 at 17:46

2 Answers2

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).

Nico
  • 15
  • 5
0

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!

Steve
  • 1