Printing the date of a cell in the Google Apps Script console

Question

I wrote in cell D1 the function "=today()", and in google apps script I'm trying to print it in this way:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const ws = ss.getSheetByName("date");
  const valoreCella = ws.getRange("D1").getValue();
  Logger.log(valoreCella);
}

The result is:

How can I get this format?

Answer 1

To get the date in cell date!D1 as a text string in the format it shows in the spreadsheet, use Range.getDisplayValue(), like this:

function getDateAsShown() {
  const ss = SpreadsheetApp.getActive();
  const dateString = ss.getRange('date!D1').getDisplayValue();
  console.log(dateString);
}

To get the date as a text string in another format, use Utilities.formatDate(), like this:

function getDateInFormat() {
  const dateFormat = 'dd/MM/yyyy';
  const ss = SpreadsheetApp.getActive();
  const timezone = ss.getSpreadsheetTimeZone();
  const range = ss.getRange('date!D1');
  const date = range.getValue();
  if (Object.prototype.toString.call(date) !== '[object Date]') {
    console.log(`The value "${range.getDisplayValue()}" is not a date but a ${typeof date}.`);
  }
  const dateString = Utilities.formatDate(date, timezone, dateFormat);
  console.log(dateString);
}

To make sure that the date string you get matches what is shown in the spreadsheet, use Spreadsheet.getSpreadsheetTimeZone() instead of hard-coding the timezone or using the script project's timezone. Otherwise, date values that have no time component and date times that are near midnight may end up showing as a different date.

To put a date in a spreadsheet cell as a proper numeric date in a particular format, use Range.setNumberFormat(), like this:

function test() {
  putTimestampInFormat_('date!D1', 'dd/MM/yyyy');
}

function putTimestampInFormat_(rangeA1, format) {
  const range = SpreadsheetApp.getActive().getRange(rangeA1);
  const timestamp = new Date();
  range.setValue(timestamp).setNumberFormat(format);
}

Answer 2

I get the desired result with this:

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  const ws = ss.getSheetByName("date");
  const valoreCella = ws.getRange("D1").getValue();
  var valoreCella2 = Utilities.formatDate(valoreCella, "GMT+1", "dd/MM/yyyy");
  Logger.log(valoreCella2);
}