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);
}