0

I developed a Google Form that copies responses to a linked output spreadsheet in Google Sheets.

In addition to the columns created by the form question/responses themselves, I've added additional custom columns. These columns contain functions (and formatting) to manipulate and utilize the response data for a script I wrote. I copied the generic functions through each row for these additional columns.

Everything works as I expect, except....

Every time another user fills out the form (i.e., every new response I receive), a new row is created in my Google spreadsheet instead of simply "adding" to my next function-containing row. As a result, the new response rows are added without the generic functions/formatting, leaving my previously "set up" rows below the newly added response row.

enter image description here

Is there some way to have new response rows be created in a way that incorporates functions in the non-form column cells?

  • In other words, I don't want to have to copy functions into cells of new rows every time a new response is generated, but instead to have these functions "auto-fill."
2

This post is eight months old as I respond here, but the basic answer is, Yes: use an array formula in the first cell of those columns.

I don't know what your custom functions do or if they would involve any calculations that don't work with arrays, but as written, these formulas would work:

In E1: =ArrayFormula({"Custom Column 1";IF(A2:A="","",Function(A2:A,B2:B,C2:C)})

In F1: =ArrayFormula({"Custom Column 2";IF(A2:A="","",Function(A2:A,E2:E)})

  • Thanks, Erik. I'll have to check in on this to see if it works out for my case (which I'll have to brush some dust off to check...). – theforestecologist Oct 14 '18 at 15:19
  • theforestcologist, if you try it and it doesn't work, it's because you've got certain parts of your function that conflict with array use. There is almost always a way to rework a formula so that it does work with a single-cell array, however. If this is the case when you try it, I'm happy to take a look at your Google doc (post it here) and suggest an alternative. – Erik Tyler Oct 14 '18 at 19:15

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.