Reformatting date in google spreadsheet -

Reformatting date in google spreadsheet -

i'm setting spreadsheet else form come in data. 1 of columns supposed hold date. input date format example: "jan 26, 2013" (there lot of re-create & paste involved collect data, changing format @ input step not real option).

i need date column sortable, spreadsheet doesn't recognize date string. (it recognize "jan-26-2013", i've tried.) need reformat input date.

my question is: how can this? have looked around , google apps script looks way go (though haven't found illustration of reformatting yet). unfortunately programming experience in python, , of intermediate level. in python without problem, don't know javascript. (my python approach be:

splitted = date.split() newdate = "-".join([splitted[0], splitted[1][:-1], splitted[2]]) homecoming newdate

)

i don't know how i'd go linking script spreadsheet - attach cell, or form, or where? , how? link helpful, understandable tutorial etc. on point help greatly.

any help appreciated! lastalda

edit: here's code ended with:

//function filter unwanted " chars date entries function reformatdate() { var sheet = spreadsheetapp.getactivesheet(); var startrow = 2; var firstcolumn = 6; var columnspan = 1; var lastrow = sheet.getlastrow(); var dates = sheet.getrange(startrow, firstcolumn, lastrow, columnspan).getvalues(); newdates = [] for(var in dates){ var mydate = dates[i][0]; seek { var newdate = mydate.replace(/"/g,''); } catch(err) { var newdate = mydate } newdates.push([newdate]); } sheet.getrange(startrow, firstcolumn, lastrow, columnspan).setvalues(newdates) }

for other confused google-script newbies me:

attaching script spreadsheet works creating script within spreadsheet (tools => script editor). putting function in there enough, don't seem need function phone call etc.

you select trigger of script script editor (resources => project's triggers).

important: script work if there's empty row @ bottom of sheet in question!

just thought :

if double click on date string in spreadsheet see real value makes string instead of date object 'jan 26, 2013 ' in front end of string didn't add together here...(the form allow type want in text area, including +322475... illustration if phone number, that's known trick in spreadsheets cells) create script runs on form submit , removes ' in cells, guess spreadsheet rest... (i didn't test give seek , consider suggestion).

to remove ' can utilize .replace() method **

var newvalue = value.replace(/'/g,'');

here links relevant documentation : link1 link2

edit next comment :

it simpler since replace doesn't generate error if no match found. create :

function reformatdate() { var sheet = spreadsheetapp.getactivesheet(); var dates = sheet.getrange(2, 6, sheet.getlastrow(), 1).getvalues(); newdates = [] for(var in dates){ var mydate = dates[i][0]; var newdate = mydate.replace(/"/g,''); newdates.push([newdate]); } sheet.getrange(2, 6, sheet.getlastrow(), 1).setvalues(newdates) }

also, used " in code, presumably on purpose... test showed ' instead. made create selection ?

google-apps-script spreadsheet