0

I'm working with a number of dates in Google Sheets, but it seems to always enter the date as a string.

sheet.getRange(3,11,sheet.getLastRow()-3, 1).setValue(Utilities.formatDate(new Date(), "UTC-5", "dd/MM/yyyy"));

is how I enter the date, but when I test it with

Logger.log(typeof extractedDate); it tells me that its a string.

If I'm working with hundreds of dates, it seems to me that it would be a huge waste of time/resources to have to parse each date as a string each time and convert it.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Tzvi L
  • 45
  • 1
  • 7

1 Answers1

0

Instead of

sheet.getRange(3,11,sheet.getLastRow()-3, 1).setValue(Utilities.formatDate(new Date(), "UTC-5", "dd/MM/yyyy"));

use

sheet.getRange(3,11,sheet.getLastRow()-3, 1).setValue(new Date());

The above will pase the current date and time to the spreadsheet which will be displayed accordingly to the cell formatting.

If you want to pass the date without the time, instad of new Date() use

new Date(new Date().getFullYear(),new Date().getMonth() , new Date().getDate())

Better alternative

var now = new Date();
var today = now.setHours(0,0,0,0);
sheet.getRange(3,11,sheet.getLastRow()-3, 1).setValue(today);

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • "*If you want to pass the date without the time…*" it's less code (and more efficient) to do `new Date(new Date().setHours(0,0,0,0))`. ;-) – RobG Jun 30 '20 at 01:37
  • thank you @Rubén But now it only shows as this very long number. How do I address that? And what time zone is it set for? – Tzvi L Jun 30 '20 at 05:20
  • 1
    @Rubén—nor does more. The less code creates 2 dates and calls 1 method vs 4 dates and 3 methods for the longer code, so a lot more efficient (as well as being a lot less to type/send/parse/etc.). ;-) – RobG Jun 30 '20 at 06:41
  • @TzviL—date objects don't have a timezone, they're always created as UTC. The date created in the above answer will be for the current date at 00:00:00 in the timezone of the host system, so might be a different date UTC. As long as you use local methods (i.e. those without UTC or ISO in the name), you'll get values for the host system timezone. – RobG Jun 30 '20 at 06:47