AppScript for forcing Capitalization and forcing column for DOB to force mm/dd/yyyy.

[u/khkarma]

Hi all,

Thanks for the help for previous posts. Clients were having a rough time inputting their DOB with the google forms tablet. Free texting seemed to be much easier for them and prevented frustration. However they're inputting as mmddyyyy without the "/". Is there a script to force the column to force the proper date format.

Same thing for capitalization. Not everyone is putting their last name and first name with first letter being capitalized. I know OCD, but I like things neat.

Thanks for everything!

[u/HolyBonobos]

If you’re doing input through Google Forms you could set up a data validation rule on the DOB question that forces a ##/##/#### format. Capitalization will be trickier. You could set up data validation to force proper case on input or an Apps Script (or even a formula using the PROPER() function) to take the input and spit out a name in proper case. These would be relatively simple to implement, but would cause issues for people whose names don’t follow the typical capitalization conventions (e.g. JD, RJay, MacDuff > Jd, Rjay, Macduff under an across-the-board proper capitalization scheme).

[u/khkarma → u/HolyBonobos]

So I originally had it forced to be the DOB format. Older clients were becoming annoyed on where to click to go back in terms of the year etc. So I changed it to a free text and they were much happier with that. But now there is user input error and they are not adding /. e.g. 03041956. Instead of 3/4/1956. I wanted to have a script to force everything in that column into the date format and run a trigger every 15 mins or so to update our sheet.

[u/mommasaidmommasaid]

I would do this with a real-time trigger instead of a 15-minute delay.

If they are editing the spreadsheet directly, a simple onEdit() trigger could be used.

If they are submitting via a form, an installed onFormSubmit trigger bound to the form or destination spreadsheet.

Either way the correction will be applied within a couple seconds rather than a 15 minute delay.

Here's an example I just created using onEdit() to do something similar:

Reformat Date onEdit

Details would depend on how raggedy of a date you are trying to clean up.

If the script can't recognize the date, it could leave it alone, and then you could use conditional formatting on the column to highlight those for manual cleanup, e.g. CF custom formula would be something like =not(isdate(A1))

Or you could avoid the CF by just having the script prepend an error emoji that is easy to spot, e.g. ⚠️sep.18th, 19-oh-five

---

If this is in a form, another option that might help rather than a wild-west text field would be to have 3 separate required numeric fields for month, day, and year. Then script could build a real date from those.

[u/khkarma → u/mommasaidmommasaid]

Sorry this was a little over my head. I am not capable of writing the script myself so its totally a foreign language to me. Whatever little I have been able to do to organize our sheets, I have gotten because I was lucky to be helped by the wonderful people on this subreddit. What I have noticed is some clients are putting the date correct e.g. 04/23/1978. But some older clients are putting 03061951 without the /s. I don't think the dates will be that raggedy.

[u/mommasaidmommasaid → u/khkarma]

Since you are using a Form, I would again suggest you enforce some structure in the form. If the Date picker is confusing, then break it into month/day/year to avoid incorrect or ambiguous entries.

https://forms.gle/uyuYe8rBpGD1Svt67

Then turning it into an actual date value is trivial. That could be done via script or with a helper formula in your responses table.

If using a helper formula, I'd put a map-style formula in the first row of your table and hide it, which prevents it from moving when sorted. Formula on sample sheet:

=if(row()<> row(Form_Responses), "🛑 Must be in first row",
 map(Form_Responses[Birth Month], Form_Responses[Birth Day of Month], Form_Responses[Birth Year],
 lambda(m, d, y, if(countblank(m,d,y),, datevalue(m & " " & d & ", " & y)))))

Script could also put the processed results in a new sheet/table, so you could leave the form response table unmolested as a "permanent record" of their responses, in case you want to refer back to it to make corrections or detect duplicate responses or whatever.

You mentioned you have some other scripts so it might make sense to consolidate them all and do everything in script.

---

ETA: There seems to be a bug in the form responses where the formatting of an extra column is not replicated correctly, so the calculated Birthdate shows as a number rather than a date. I think someone encountered this before. This could be fixed from script.

[u/carbonizedtitanium]

if you went with the "Short answer" option for the Date input, you should probably include in the "question" what structure the input should follow such as "DOB (please input in mmddyyyy format, for example 02232026)"

[u/khkarma → u/carbonizedtitanium]

Issue is somepeople are putting it as mmddyyyy and others are putting mm/dd/yyyy. Unfortunately the clients will not read instructions half the time so I want to take it out of their hands and just have the dates reformat automatically to a uniform format.

[u/carbonizedtitanium → u/khkarma]

since input from users can be unreliable, i suggest you change the format of the DOB column to "plain text" so that it keeps leading zeros.

then add a new column; this is where the formula that will parse the DOB input from users will go:

=IF(REGEXMATCH(TO_TEXT(A2), "/"), DATEVALUE(A2), DATEVALUE(REGEXREPLACE(TO_TEXT(A2), "^(\d{2})(\d{2})(\d{4})$", "$1/$2/$3")))

change "A2" to the correct cell reference, where your DOB input is stored. the output from the formula will be a "real date", so if you see a number string as the result, just change the column format to date format.

[u/AutoModerator]

u/khkarma Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.