Search code examples
google-apps-scriptdate-formatting

How To Properly Format A Date Value That Is Retrieved by App Script


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
}

Solution

  • 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:

    A
    09/20/2021
    Mon Sep 20 2021