Include calculated column values in email resposne

[Atit Shah]

Hi, I have added extra columns in the form where  the values get calculated. My problem is whenever  a new response is recorded  the cell calculating the value get shifted one down and the new record tops over it. :( Suppose I have values in the columns A1:A5 and the column B1:B5 has the calculated value. Now whenever the new response is recorded A6 the value does not get calculated  in B6. (I have applied the formula to the complete column B). Also i want to send the value of column B in the mail response. How can this be done? Pls. Help.

[Dan Longley]

Hi Atit

I think I can answer this one to save Henrique some of his precious time, apologies if I've misunderstood your question. I've answered this step-by-step so there's no confusion.

Reserve row 1 for your headings only, no data. Use row 2 as a dummy row and place your formulae in row 2 in the columns after your form entry columns.

Check that you've told FormEmailer in your settings the correct columns for your formulae.  Also, remember that FormEmailer reserves exclusive use of column A to tell you that email(s) have/haven't been sent.

When a form is submitted, FormEmailer uses the formula it finds in row 2 and copies it down to the new row.  Make sure that your formula in row 2 uses absolute references as necessary.  Example:

Say that column D contains last form question data, E contains your first formula:

Enter formula in E2. Say it is adding data in C and D, the formula in E2 might be =C2+D2.

Good practice to make cell references 'absolute' when they mustn't change. C2 will always be column C, just the number will change, so I would write: =$C2+$D2.

Form is submitted into new row, say row 6. Formemailer uses your formula in E2 and returns the sum of $C6+$D6 in E6.

Does that answer your question.

Dan

On 24 May 2014 13:15, Atit Shah via FormEmailer <formemailer+noreply-APn2wQd0n2Ps...@googlegroups.com> wrote:

Hi, I have added extra columns in the form where  the values get calculated. My problem is whenever  a new response is recorded  the cell calculating the value get shifted one down and the new record tops over it. :( Suppose I have values in the columns A1:A5 and the column B1:B5 has the calculated value. Now whenever the new response is recorded A6 the value does not get calculated  in B6. (I have applied the formula to the complete column B). Also i want to send the value of column B in the mail response. How can this be done? Pls. Help.

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.
To post to this group, send email to forme...@googlegroups.com.
Visit this group at http://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/d/optout.

[Henrique Abreu]

Thanks Dan, nice answer.

Also, please notice that this "adding a new line" (regardless if you have space the sheet or not) is a feature/behavior of Google Forms.

And that's why I developed this formulas copying feature.

Henrique Abreu

[Atit Shah]

Hi Dan,

Thanks for replying. Can you please tell me how and where to adjust settings or what changes i have to do in the settings?

[Dan Longley]

Atit

If FormEmailer is setup correctly, look at the spreadsheet and click on the FormEmailer menu tab and you'll be able to select 'Settings'.  All this is thoroughly documented by Henrique if you click on his help page!

dan

[Atit Shah]

Hi Dan,

Thanks for guiding. But still have some questions :)

How about if we want to apply the formula to the whole column? I tried the example given in the documentation on the help page. On submitting the response the value was calculated on for the subsequent column where the formula was written. But when i submitted the next response the formula didn't work out. Could you please help on this?

On Tuesday, May 27, 2014 3:02:14 AM UTC+5:30, Dan Longley wrote:

Atit

If FormEmailer is setup correctly, look at the spreadsheet and click on the FormEmailer menu tab and you'll be able to select 'Settings'.  All this is thoroughly documented by Henrique if you click on his help page!

dan

[Dan Longley]

Hi Atit, can u share your spreadsheet with us so we can see what's happening. I can only guess otherwise.
Thanks Dan

[Atit Shah]

Hi Dan,

Here's the link for the sheet. This the sample sheet i've been trying for. The actual sheet and form are different. However i tried to calculate the responses each time the new record is submitted by using the "Query" function of the excel in the new spreadsheet and tried to pick up the values from it (by setting the name of the new sheet in the FormMailer settings) but still didn't work out.

https://docs.google.com/a/tudip.com/spreadsheet/ccc?key=0AmOyA6g6_QObdHFKTU9CLTRCSlB5TmR0b1R5a1ZrckE&usp=sharing

Please let me know ASAP if anything is going wrong. :)

On Tuesday, May 27, 2014 4:10:24 PM UTC+5:30, Dan Longley wrote:

Hi Atit, can u share your spreadsheet with us so we can see what's happening. I can only guess otherwise.
Thanks Dan

[Dan Longley]

Atit, thanks for sharing sheet. Sorry, I'm a little confused, can anyone else help as im on vacation at mo!? Can u write some code in this sheet and set up settings for FormEmailer so we can see what's not working for you and why?
Dan

[Henrique Abreu]

Atit,

Why do you use query just to bring the results on a different sheet? Why don't you use the FormResponses directly?

Anyway, I configured the spreadsheet you shared with the way I think is best.

Before you do any editing on the spreadsheet, please try it out first. Just pick Process Manually on the FormEmailer menu and type in any row number (e.g. 3) to see FormEmailer copying the formula for you.

Regards,

Henrique Abreu