Stop Google Forms from Rounding Up Decimals

Question

I have a simple form where I need exact inputs entered from form to sheet. Unfortunately, Google forms is rounding up some of the data I enter. Can this behavior be modified.

Example:

Form Question: What is Price Per Share? Response: $.0075 Number Submitted to Sheets: $.01

I understand why they might want to round up, but Price Per Share needs to be exact. Any suggestions would be hugely helpful.

Just to add a little color. It appears that Google Sheet is forcing the rounding, b/c it only saves two decimal points. Is there a way to alter the number of decimal places that are saved?

What's the format for the column of data? If it's set to currency, it'll round to the hundredths. Try changing it to Plain Text to prevent rounding. — Brian
@BrianBennett, unfortunately even when I change the formatting it doesn't impact the input. Additionally, when it does work, it strips the commas from the number. I don't know what's going on. Thank you for your help, though! — Bee Smears

utphx utphx · Accepted Answer · 2016-11-27T02:33:09

You can use a form validation (is number) and it will not round on the spreadsheet. Full instructions here:

https://support.google.com/docs/answer/3378864?hl=en

You can also use a small script to format the number any way you like and set a trigger on form submit to run the script, here is a quick sample:

function numFormat(e) {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];  // select first sheet
 var col = "A" // change to the column that has the price per share
 var row = e.range.getRow();
 var cell = sheet.getRange(col+row);
 cell.setNumberFormat("$0.00000");  //set the format here
}

Different number formats can be found here under custom number formatting:

https://support.google.com/docs/answer/56470

Stop Google Forms from Rounding Up Decimals

1 Answers