CODEWITHSUNDEEP
datetimegoogle-apps-scriptgoogle-sheets
CodingCat
CodingCat

Reputation: 5411

Reformatting date in google spreadsheet

I'm setting up a spreadsheet for someone else with a form to enter data. One of the columns is supposed to hold a date. The input date format is like this example: "Jan 26, 2013" (there will be a lot of copy & paste involved to collect data, so changing the format at input step is not a real option).

I need this date column to be sortable, but the spreadsheet doesn't recognize this as a date but simply as a string. (It would recognize "Jan-26-2013", I've tried.) So I need to reformat the input date.

My question is: how can I do this? I have looked around and google apps script looks like the way to go (though I haven't found a good example of reformatting yet). Unfortunately my only programming experience is in Python, and of intermediate level. I could do this in Python without a problem, but I don't know any JavaScript. (My Python approach would be:

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

)

I also don't know how I'd go about linking the script to the spreadsheet - would I attach it to the cell, or the form, or where? And how? Any link to a helpful, understandable tutorial etc. on this point would help greatly.

Any help greatly appreciated!

Edit: Here's the code I ended up with:

//Function to filter unwanted " chars from 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 i in dates){
    var mydate = dates[i][0];
    try
      {
      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 like me:

Important: the script will only work if there's an empty row at the bottom of the sheet in question!

Upvotes: 0

Views: 1835

Answers (2)

Omar Bashir
Omar Bashir

Reputation: 11

Solved it, I just had to change the comma to dot and it worked

Upvotes: 1

Serge insas
Serge insas

Reputation: 46794

Just an idea :

If you double click on your date string in the spreadsheet you will see that its real value that makes it a string instead of a date object is this 'Jan 26, 2013 with the ' in front of the string that I didn't add here...(The form does that to allow you to type what you want in the text area, including +322475... for example if it is a phone number, that's a known trick in spreadsheets cells) You could simply make a script that runs on form submit and that removes the ' in the cells, I guess the spreadsheet would do the rest... (I didn't test that so give it a try and consider this as a suggestion).

To remove the ' you can simply use the .replace() method **

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

here are some links to the relevant documentation : link1 link2

EDIT following your comment :

It could be simpler since the replace doesn't generate an error if no match is found. So you could make it like this :

function reformatDate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var dates = sheet.getRange(2, 6, sheet.getLastRow(), 1).getValues();
  newdates = []
  for(var i 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, you used the " in your code, presumably on purpose... my test showed ' instead. What made you make this choice ?

Upvotes: 2

Related Questions