Google-sheets – How to round a Google Form input to nearest .5

google sheetsgoogle-apps-scriptgoogle-forms

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.

Best Answer

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