How to prevent Google Forms from converting form input to scientific notation format

Question

I have a simple script set up that sends emails based on Google Form entries using a script-based VLookup to get the contact emails. In some cases, Google Forms converts longer numbers entered into the form field to scientific notation. A workaround I have been using is to enter an apostrophe before the number - for some reason this keeps the cell formatted to plaintext. I would like to find a solution that does not require this extra step.

The sheet has a form with a single field, eGCs. The eGCs field can contain ANY combination of letters and numbers and may be a multi-line string. The script sends an email to the user onFormSubmit with the eGCs field entry in the body of the email. The problem arises when I try to submit a very long string that is only numbers and the form entry variable is converted to scientific notation.

I need whatever the user enters in the eGCs field to appear EXACTLY as they entered it on both the Responses 1 sheet and in the body of the email that is sent. Here is the code:

function onFormSubmit(e) {

   var eGCs = e.values[1];
   var email = Session.getActiveUser().getEmail();

//Replace the Google Sheets formatted line breaks with HTML line breaks so they display properly in the email:
   eGCs = eGCs.replace(/\n/g, '<br>');

//Send the email:   
   var subject = "This is only a test";
   var body = eGCs;
   MailApp.sendEmail(email, subject, body, {htmlBody: body})
   return
   }

If I submit

6110523527643880 

...into the form, the number is changed to scientific notation format and appears as 6.11052E+15 both on the sheet and in the email that is sent. If I submit a multi-line string such as:

6110523527643880
6110523527643880
6110523527643880

...then the script works fine and the form field entry is not converted (probably because Google does not consider it a number any more). I need it to appear exactly as entered whether or not the form entry is a single line or multiple lines.

Here is my example sheet / script / form. It should be public, so please feel free to test it.

Answer 1

Form responses in Forms (as opposed to Spreadsheets) store responses as Strings. Your trigger function could grab the response from the form to get the string as entered by the respondent.

function onFormSubmit(e) {
  // Get response sheet. First version works only in contained script,
  // second works even in stand-alone scripts.
//  var sheet = SpreadsheetApp.getActiveSheet();
  var sheet = e.range.getSheet();

  // Get URL of associated form & open it
  var formUrl = sheet.getParent().getFormUrl();
  var form = FormApp.openByUrl(formUrl);

  // Get response matching the timestamp in this event
  var timestamp = new Date(e.namedValues.Timestamp);
  // NOTE: There is a race condition between the updates in Forms and Sheets.
  // Sometimes (often!) the Spreadsheet Form Submission trigger function is invoked
  // before the Forms database has completed persisting the new Responses. As
  // a result, we might get no results when asking for the most recent response. 
  // To work around that, we will wait and try again.
  var timeToGiveUp = 0;
  do {
    if (timeToGiveUp > 0) Utilities.sleep(1000); // sleep 1s on subsequent tries
    timeToGiveUp++;
    var responses = form.getResponses(timestamp);
  } while (responses.length == 0 && (timeToGiveUp < 3));
  Logger.log("time to give up "+timeToGiveUp);
  var response = responses[0]; // assume just one response matches timestamp
  var itemResponses = response.getItemResponses();
  var eGCsItemNumber = 1;  // Indicates where the question appears in the form
  var eGCs = itemResponses[eGCsItemNumber-1].getResponse().toString();

  // You now have exactly what the respondent typed, as a string.
  // It can be used as-is in an email, for example.
  var body = "The user entered: "+eGCs;
  MailApp.sendEmail(
     Session.getActiveUser().getEmail(),
     "This is only a test",
     body
   );

  // To preserve the value in the spreadsheet, we must
  // force it to remain a string by prepending a tick (')
  var eGCsCol = 2;
  e.range.offset(0,eGCsCol-1,1,1).setValue("'"+eGCs);
}

Note wrt Race condition comment: The actual work that was needed in this area of the code was a single line:

var responses = form.getResponses(timestamp);

While playing with this, I found that I was frequently receiving an exception, the same as noted in comments below this answer...

Cannot find method getResponses(object)

It turned out that this only happened when the function was triggered by a form submission event, not when running from the editor/debugger with simulated events. That implies that, for a short period of time, the response we're trying to handle is not returned by the call to getResponses().

Because of the way that shared documents are implemented, there is a propagation delay for any change... that's the time it takes for a change in one view of an asset to propagate to all other views.

In this situation, our trigger function has launched with a spreadsheet event, and then opens a view of the Form and tries to read the newest responses before that view contains them.

A simple work-around would be to sleep() for a period of time that would allow propagation to complete.

Utilities.sleep(5000);   // 5s pause
var responses = form.getResponses(timestamp);

Simple, yes - but inefficient, because we'd be waiting even when we didn't need to. A second problem would be determining how long was long enough... and what if that changed tomorrow?

The chosen work-around will retry getting responses only if it is not successful the first time. It will only wait when doing a retry. And it won't wait forever - there's a limiting condition applied, via timeToGiveUp. (We could have added an additional check for success after the loop, but since the next statement will through an exception if we've blown through our time limit, we can let it do the dirty work.)

var timeToGiveUp = 0;
do {
  if (timeToGiveUp > 0) Utilities.sleep(1000); // sleep 1s on subsequent tries
  timeToGiveUp++;
  var responses = form.getResponses(timestamp);
} while (responses.length == 0 && (timeToGiveUp < 3));

Lots more than one line of code, but more robust.

Answer 2

I am assuming eGCs is the response with the number.

e.values[2] will always come back as a string (in this case "6.15312E+16"), therefore you cannot convert that to the original number as you loose everything after that last 2. Even if you convert it, the best you can get is "61531200000000000"

Instead, you can pull the value from the spreadsheet. In the beginning of your onFormSubmit() function add this code:

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheetByName("Form Responses 1");
 var eGCs = sheet.getRange(sheet.getLastRow(), 2, 1, 1).getValue(); 
 try {
   eGCs = eGCs.toFixed();
 } catch (e) {
   Logger.log(eGCs);
 }

Your eGCs will now return as the full number if it's a number, otherwise it will return as text.

If you want the spreadsheet to have the right format when new responses are submitted add this to your code:

sheet.getRange(sheet.getLastRow(), 2, 1, 1).setNumberFormat("000");

This will convert the new row added by the form in to the correct format. This does not affect the actual value in the spreadsheet, only the way it is formatted.