No matter what I try, I cannot get an app script to return an existing date that's retrieved from another cell.

In the script below, both mDate (the actual value of L2), and dateString result in a return value of 'Sat Dec 30 1899', which I understand as GS's start date.

I have tried creating a new date object, retrieving the cell's display value, formatting the existing date object with a utility E.g.:

 var formatmDate = Utilities.formatDate(mDate.getValue(), "GMT", mDate.getNumberFormat());

The current actual date value being retrieved is '09/20/2021' (more preferably 'Mon Sep 20 2021', but from what I've read this could be problematic?).

Any help on getting this right is appreciated.

Here are the functions:

function gammaTilt() {
  var ss = SpreadsheetApp.openById(SpreadsheetID);
  var sheet = ss.getSheetByName(SheetName);
  var gt = sheet.getRange("M2").getValue();
  var nextRow = getFirstEmptyRow('N');
  var mDate = sheet.getRange("L2");
  var dateString = mDate.getDisplayValues();

  sheet.getRange(nextRow, 14, 1, 2).setValue([gt,dateString]);
};

// From https://stackoverflow.com/a/9102463/1677912
function getFirstEmptyRow(columnLetter) {
  columnLetter = columnLetter || 'N';
  var rangeN1 = columnLetter   ':'   columnLetter;
  var spr = SpreadsheetApp.getActiveSpreadsheet();
  var column = spr.getRange(rangeN1);
  var values = column.getValues(); // get all data in one call
  var ct = 0;
  while ( values[ct][0] != "" ) {
    ct  ;
  }
  return (ct 1); //  1 for compatibility with spreadsheet functions
}

CodePudding user response：

function getmydate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const dt = Utilities.formatDate(new Date(sh.getRange('A1').getValue()),ss.getSpreadsheetTimeZone(),"E MMM dd yyyy");
  sh.getRange('A2').setValue(dt);
}

Sheet0: