2

I'm trying to write a script that will calculate based on user input from a Google Form and then send a customized email based on the input provided.

I've written an apps script bound to the response spreadsheet that sends the form data in an html email back to the user.

I have all my calculations working in the response spreadsheet (I used an array formula to add them across the spreadsheet following the form data, but I could not figure out how to call them into the script.) So I moved on to doing the calculations in the script, (even though I had no idea how to do that, and just kinda winged it), and it's working and sending the email.

However, several of the calculations do not match what is in the spreadsheet, so I know my script isn't calculating right.

Here are the two calculations I need to add to my script in the correct way.

  1. sC = sH *.9, rounded to nearest .5
  2. sSt = sC * sG, rounded to nearest multiple of 4

And after much reading of posted Q&As, here's where I landed in my script, still lost:

 var sG= e.values[3];
 var sH= e.values[6];

 var sC= (Math.round(sH * .9)).toFixed(1);
//sC should be .0 or .5
 var sSt= (Math.round((sG*sC) /4)*4).toFixed(0) ;
//sSt should be a whole number, nearest multiple of 4

For #1, I thought it'd be possible to round at submission when the user inputs the measurement. Then it'd be a simple *.9 in the script. Hence, the title of my question. But I can't find a way to do that that I understand how to replicate. And, that would only solve part of #2--I'd still have to deal with the multiple of 4 rounding.

For #2, the hard part is that sC is calculated, then multiplied, then rounded to a multiple. I can't understand how to do all three of those in a script.

All the calculations below these two cascade, so if I can get these two right, everything else works.

3

Wouldn't a more simple way of doing this be done using functions in the actual sheet?

Up: =MROUND(A1+0.25;0.5)
Down: =MROUND(A1-0.25;0.5)

I would assume that having another column added to the spreadsheet that takes the Google Forms input and performs the rounding function on it, then use Apps Script to email the data would be easier than than trying to do it all in a script.

enter image description here

  • I have mround in the spreadsheet, but I could not figure out how to get the resulting number in my script because the sG and sH values are form responses. I learned how to put the mround calculation into an arrayformula on the response sheet and it calculates correctly in the columns following each response, but everything I tried to get that result into my script didn't work, likely because I don't know enough js to make it work. – Kristi Millsap Nov 26 '15 at 16:26
1
(Math.floor(Math.random() * 60) / 5).toFixed(0) * 5

This code will round the number to nearest 5. Modify it to suit your requirement.

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.