How to prevent Google Sheets from reformating input from Google Forms?

Question

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.

Answer 1

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

• How do I import text file as-is to Google spreadsheet?
• Using a script to process form input every time a form is submitted
• Stop a '+' from generating a formula
• Sending formulas with Google Forms
• Insert literal text that begins with = sign

Answer 2

Just a quick follow-up, should anyone face this problem. I used the following code in a OnSubmit trigger of the Google Form. :

function onSubmit(e){
  // determine sheet that receives the responses
  const actForm = FormApp.getActiveForm()
  const destSheet = get_form_destination_sheet(actForm);
  if (!destSheet)
    throw new Error("No sheets in destination with form url '" + actForm.getPublishedUrl() + "'");


  // parse response so to add ' in front of every individual answer      
  var items = e.response.getItemResponses();
  var response = []
  for (i in items){
    response[i] = "'" + items[i].getResponse();
  }

  // determine destination range and write "textified" answers
  var lastRow = destSheet.getLastRow();
  range = "B" + lastRow.toString () + ":" + columnToLetter (items.length + 1) + lastRow.toString ()
  destSheet.getRange(range).setValues ([response])

}


function columnToLetter(column){
  // code copied from https://stackoverflow.com/a/21231012/2280291
  var temp, letter = '';
  while (column > 0)
  {
    temp = (column - 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column - temp - 1) / 26;
  }
  return letter;
}

function get_form_destination_sheet(form) {
    // code copied from https://stackoverflow.com/a/57566500/2280291
    const form_id = form.getId();
    const destination_id = form.getDestinationId();
    if (destination_id) {
        const spreadsheet = SpreadsheetApp.openById(destination_id);
        const matches = spreadsheet.getSheets().filter(function (sheet) {
            const url = sheet.getFormUrl();
            return url && url.indexOf(form_id) > -1;
        });
        return matches.length > 0 ? matches[0] : null; 
    }
    return null;
}

Basically, it locates where the user response is written in the Sheet and it overwrites it with literal user input with apostrophe prepended. This forces Sheet to treat it as text.

I have one concern about this code and it's about concurrency - it'd be nice if one could lock up the Sheet from the trigger so that simultaneous responses don't screw the thing up but don't exactly know how to do it.

Answer 3

I would rather create onFormSubmit() function bounded to the destination sheet and process answers in similar fashion. In the spreadsheet use appendRow() instead of getRange().setValues() and write it to a separate worksheet. AppendRow is an atomic action which was specifically created for instant data flush.

Good luck.

Answer 4

Yes its Possible

i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently.

I use a simple workaround to bypass the google form overwriting formatting & deleting formulas & other data on the form responses answer sheet on each new entry.

With this solution i will show you - you get two benifits

1) you can retain any cell formatting you want - this method allows you to do that conveniently

2) and most importantly you can use formulas safely without the fear of the formulas being deleted on each new form response entry - as you are aware google forms creates a new entry on the next available row irrespective if you already prefilled formulas in any other collumn or rows on that response sheet - thsi is especially usefull if your form collects numerical data that needs to be summed up etc. & needs to be used for pviots & charts in real time

the solution is simple - on a corresponding new sheet just use "=arrayformula" & refrence the whole form responses answer sheet data range.

What you get on your new sheet is the exact replication of your form responses data sheet that will autoupdate with each new form response & with the added benift of using unlimited formatting & unlimited formulas without the hassel of losing the same by google forms overwriting rows & deleting formatting & deleting formulas.