2

There's a Google form that's linked with a Google sheet: whenever a response is posted to the forme, a new line is added to the sheet (here's a quick tutorial just in case).

Is it possible to have this newly added line to include some formulas?

As an example, say the web form asks for

Name 
Date of birth

Then I'd like the output Google sheet to look like

Name          |  Date of Birth  |  Age
<from form>     <from form>        =TODAY() - B2  

While by default, the third column is created with no value

  • There's a workaround : create another spreadsheet with data linking from the lone linked with the forms. That second sheet will not autosize when rows are added though – PPC Jun 1 '19 at 21:04
1

You will need an array formula for that purpose:

=ARRAYFORMULA(IF(LEN(B2:B), TODAY()-B2:B, ))

or in the first row:

={"header"; ARRAYFORMULA(IF(LEN(B2:B), TODAY()-B2:B, ))}

| improve this answer | |
0

i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.

its possible with a simple workaround

first thing dont work on the actual form responses data sheet get the data to a new sheet and then work on it (see more below on why not to do this & what to do instead)

Working directly on the actual google forms responses sheet is not advisable as google forms will overrite any preexisiting data and replace it with the form responses answers - be it text formulas or even cell formatting - theres a simple fix for this - do not work directly on that sheet - just bring that data to a new sheet & then your codes & formulas will work just fine be it scripting code or simple vlookup formulas to colatlate your data.

Heres a simple workaround solution:

I use a simple workaround to bypass the google form overwriting formatting & deleting formulas & other data on the form responses answer sheet on each new entry.

With this solution i will show you - you get two benifits

1) you can retain any cell formatting you want - this method allows you to do that conveniently

2) and most importantly you can use formulas safely without the fear of the formulas being deleted on each new form response entry - as you are aware google forms creates a new entry on the next available row irrespective if you already prefilled formulas in any other collumn or rows on that response sheet - thsi is especially usefull if your form collects numerical data that needs to be summed up etc. & needs to be used for pviots & charts in real time

the solution is simple - on a corresponding new sheet just use "=arrayformula" & refrence the whole form responses answer sheet data range.

What you get on your new sheet is the exact replication of your form responses data sheet that will autoupdate with each new form response & with the added benift of using unlimited formatting & unlimited formulas without the hassel of losing the same by google forms overwriting rows & deleting formatting & deleting formulas.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.