Google-sheets – How to prevent Google Sheets from reformating input from Google Forms

google sheetsgoogle-apps-scriptgoogle-forms

I need to create Google Form where users can submit different data. Problem is that some of them use comma and others use dots as decimal separator and my Form should handle them both. Problem is, it doesn't. Let me give you clear example:

Consider a Google Form that has two "Short answer text" inputs named T1 and T2, no input validation. Assume that the Form is linked to a Sheet. If a user enters '0.123' into the first textbox and '0,123' into the other, the input will be recorded in the Sheet as '0.123' and '123' respectively. I assume that some kind of formatting is taking place where first input is recognized as number and I don't even know what happened to the second one. So, following excellent answer by Tanaike, I installed OnSubmit triggers in the Sheet

function onSubmit(e){
  Logger.log("%s", JSON.stringify(e));
}

which gives me following log:

[19-12-12 07:46:33:682 PST] {"authMode":{},"values":["12/12/2019
16:46:33","0.123","123"],"namedValues":{"Timestamp":["12/12/2019
16:46:33"],"T1":["0.123"],"T2":["123"]},"range":{"columnStart":1,"rowStart":2,"rowEnd":2,"columnEnd":3},"source":{},"triggerUid":"2545668"}

Installing similar onSubmit handler in the Form

function onSubmit(e){
  Logger.log("authMode=%s, source.getId()=%s", e.authMode, e.source.getId());
  var items = e.response.getItemResponses();
  for (i in items){
    Logger.log("getItem().getTitle()=%s, getResponse()=%s", items[i].getItem().getTitle(), items[i].getResponse());
  }

I get:

[19-12-12 07:46:33:848 PST] authMode=FULL,
source.getId()=1P7OAN5QrctyFW32F9XLm25V48NxuUkwwqjr6YI9J2Ko [19-12-12
07:46:34:520 PST] getItem().getTitle()=T1, getResponse()=0.123
[19-12-12 07:46:34:612 PST] getItem().getTitle()=T2,
getResponse()=0,123

So, this tells me that form actually accepts input as-is, but somewhere in the transfer, input gets reformatted and information is lost. Some things I tried:

  1. changing localization setting will NOT solve the problem because a) Problem could simply just shift to numbers with dot as separator and b) I have exactly the same problem on other input data (dates, zip codes) and would love to have more general solution

  2. using regex does not solve the problem – the form I develop uses regex abundantly but minimal example demo shows the that the problem is independent of regex.

  3. pre-formatting sheet before submitting. Doesn't work because it seems that submit is recorded through inserting new row.

So my question is how to make Form/Sheet combo record literal input without reformatting or any other intervention? Once I have literal input in the Sheet, I know how to handle it further.

Best Answer

Google Sheets doesn't have a way to stop the automatic data type assignation. Instead of using the built-in form response submission to Google Sheets you could use the Google Forms on submit trigger to prepare the response data to be stored properly in your spreadsheet.

If you want to keep the data as was wrote by the form respondent your code should preppend an apostrophe as this forces Google Sheets to treat the value as text.

Related