We have a Google Spreadsheet that multiple people can enter data into. For each entry we have created a series of formulas to run calculations and derive totals from the values entered.

We decided to make it simpler to enter data by using a form. This works amazingly, as you can control what data can be entered, and then even control who can edit it. It's a great way to ensure consistency and even data security.

However, I ran into a major problem. Each time a form was submitted, this creates a new row of data (which is expected). However, the problem is that the formulas were not being copied from previous rows, the formulas cells were just empty and nothing was calculated.

After many hours of searching, here's how I was able to fix it:

The first option I thought of was to just always copy the formulas each time a form was submitted. But this kind of defeats the purpose of automation, and I assumed Google was smart enough to think of a way to solve this. Turns out they have, with a function called ArrayFormula().

I don't full understand what ArrayFormula() is for. I read the Help doc, but it was high technical and of little use to me. However, after dissecting what a few others had done, I found it could be used for this.

Ok, enough explanation, here's how to do it:

1. Put your formulas in cells in row 2 of your sheet (row 1 is the headers). Make sure these cells are not part of the form, obviously.
2. Delete (not clear) all rows below row 2. So now your sheet only has 2 rows.
3. Now, suppose your formula was "=A2+B2". Wrap it with the function, and add :[column] after each cell reference. Here's what it will now look like:
   =ArrayFormula(A2:A+B2:B)

That's all! What this effectively does is cause these formulas to be applied to each cell in new rows entered by the form. Note though, that when you click on a new cell, you won't see the formula, you'll see "=CONTINUE(A2, 2, 1)", which can't be edited. I suppose this is just part of the function spec.

I hope this of use to some of you and saves you hours!