A row is inserted when I generate on Form Submit making my formula useless. What's the solution?

If you are collecting data from a Google Form and want to calculate it (for example unit price*quantity) on your spreadsheet (total =unit price*quantity) then generate a document on Form Submit we suggest you use arrayFormula.

1. Link your Google Form to your Google spreadsheet and set up your Document Merge (1) and/or Mail Merge (2)

2. Submit your Form once. This will create a line on your spreadsheet

  3. Insert a row above your Form submission

  4. Add your arrayFormula in the row you have inserted.

In the case below we will use =ARRAYFORMULA(F2:F20*G2:G20) or Total=Unit Price*Quantity

You may also choose to add this arrayFormula in your header column, replacing Unit price and Quantity with numerals: =ARRAYFORMULA(F1:F20*G1:G20)

 

 

 

3. Choose "On form submit" from the Generate menu

 

4. Your Documents and/or Mail Merge will be generated each time a Form is submitted, showing you the calculated Total

 

If you use arrayFormula on Form Submit documents will only be generated when a Google Form is submitted

NB: arrayFormula should not be used to "Generate Now" as Doc and Mail Merge will generate a document for every line indicated in your formula. For example,  if your arrayFormula is F1:F20*G1:G20 Doc and Mail Merge will generate 20 documents. If your arrayFormula is F1:F201*G1:G201 Doc and Mail Merge will generate 201 documents.

 

 

Have more questions? Submit a request

Comments

Powered by Zendesk