Google Apps Script on Form Submit Time Formatting

Question

Background:

How: I suspect that this is a glitch within Google Form (submission process)/Spreadsheet, but may be part of the Date conversion utility of the Spreadsheet interface (and is an intended feature).

When entering a format in a text box in Google Forms, there is some sort of communication error between the Form submit and Response Spreadsheet, or pre-processing of the Form's data before it is sent to the spreadsheet. The glitch only seems to happen for data in a text field of the format ##:## TEXT where TEXT contains no '.' characters. For example: 4:15 pm will reproduce the glitch, but 4:15 p.m and 4:15 p.m. will not.

Result: An apostrophe character is added to the beginning of the string when it is put into the Spreadsheet (i.e. '4:15 pm) which throws off several sub-systems I have in place that use that time data. Here are two screenshots (sorry for the bad sizing on the second):

I'm 99% certain that the glitch is caused by the ##: combination.

Temporary Fix?: The real question is... how might I go about removing that pesky apostrophe before I start manipulating the time data? I know how to getValue() of a cell/Range. Assume I have the value of a cell in the following manner:

var value = myRange.getValue();
// value = '4:15 pm

How can I go about processing that value into 4:15 pm? A simple java function could be

value = value.substring(1); // Assuming "value" is a String

But in Google App Scripts for Spreadsheets, I don't know how I would do that.

Post-Script: It is necessary to post-process this data so that I don't have to lecture university faculty in the language department about inputting time format correctly in their forms.

Thanks in advance to those who can help!

Answer 1

If you remove the ' in the spreadsheet cell the spreadsheet interface will convert this entry to a date object.

This might (or not) be an issue for you so maybe you should handle this when you read back your data for another use... It doesn't happen when text is different (for example with P.M) simply because in this case the ' is not necessary for the spreadsheet to keep it as a string since the spreadsheet can't convert it to a date object (time value).

Artificial intelligence has its bad sides ;-)

---

edit : You cant do this in an onFormSubmit triggered function using the javascript substring() you mentioned. If you're not familiar with that, here is the way to go :

To run a script when a particular action is performed:

1. Open or a create a new Spreadsheet.
2. Click the Unsaved Spreadsheet dialog box and change the name.
3. Choose Tools > Script Editor and write the function you want to run.
4. Choose Resources > Current project's triggers. You see a panel with the message No triggers set up. Click here to add one now.
5. Click the link.
6. Under Run, select the function you want executed by the trigger.
7. Under Events, select From Spreadsheet.
8. From the next drop-down list, select On open, On edit, or On form submit.
9. Click Save.

see doc here and here

Answer 2

How can I go about processing that value into 4:15 pm? A simple java function could be

value = value.substring(1); // Assuming "value" is a String But in Google App Scripts for Spreadsheets, I don't know how I would do that.

Google Apps Scripts uses Javascript which has the exact same method.

value = value.substring(1);

should return all except the first character.

More about Javascript substring at: http://www.w3schools.com/jsref/jsref_substring.asp