So I have the following data on a google sheet:

What I want to do is to get the date only and the format it to a new column something like this:

I have done the following code for the apps scripts extension that google sheet has, however I have the ongoing issue that only one date is set up on the column and it's not format as date:

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('Sheet1');
  const r = sh.getRange(2,1,sh.getLastRow() - 1,1);
  var v = r.getValues();
  const result = v.map(row => row[0].substring(0,10))
  //const valueL4D = r.substring(0,10)
  r.offset(0,2).setValues(result)
}

Any guidance on this?

CodePudding user response：

Since they're strings(and not dates), use regex with String.replace:

v.map(row => [row[0].replace(/(\d{4})-(\d{2})-(\d{2}).*/g, '$2/$3/$1')])

• \d digit
• {n} number of digits
• ${n} replacement groups for each capture group ()

CodePudding user response：

get the date

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('Sheet1');
  const v = sh.getRange(2,1,sh.getLastRow() - 1).getValues().flat();
  const result = v.map(e => [Utilities.formatDate(new Date(e),Session.getScriptTimeZone(),"MM/dd/yyyy")]);
  sh.getRange(2,2,v.length,v[0].length).setValues(v)
} 

CodePudding user response：

Use the Date constructor, like this:

  const result = v.map(row => row.map(value => new Date(String(value).substring(0, 10))));

Note that timezone offsets may come into play in the event your spreadsheet and script projects use a different timezone setting.

But it would probably be a lot easier to do the same with a spreadsheet formula, like this:

=arrayformula(to_date(value(left(A2:A, 10))))

To format the result, use Format > Number > Custom date and time.