Skip to main content
add another two cases
Source Link
doubleunary
  • 15.7k
  • 9
  • 23
  • 59

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 testgetDateAsShown() {
  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);
}

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 test() {
  const ss = SpreadsheetApp.getActive();
  const dateString = ss.getRange('date!D1').getDisplayValue();
  console.log(dateString);
}

To get the date in another format, use Utilities.formatDate().

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);
}
Source Link
doubleunary
  • 15.7k
  • 9
  • 23
  • 59

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 test() {
  const ss = SpreadsheetApp.getActive();
  const dateString = ss.getRange('date!D1').getDisplayValue();
  console.log(dateString);
}

To get the date in another format, use Utilities.formatDate().