Problem with understanding date formats in googleScripts

Question

I made a few functions with GoogleSheets using AppsScripts for simple task a few times in previous years. I always had problems when taking dates from cells/ranges and processing them, but somehow alwaays found a workaround, so that I did not have to deal with it. Well, this time I can not find a workaround, so I will try to explain my problems with the following code:

function getDates(){
 var s = SpreadsheetApp.getActiveSpreadsheet();
 var sht = s.getSheetByName('Dates');
 var date = sht.getRange(2,1).getValues();  
 Logger.log(date[0][0]); //output is Tue Jun 08 18:00:00 GMT-04:00 2021

 var datumFilter= Utilities.formatDate(date[0][0], "GMT+1", "dd/mm/yy");
 Logger.log(datumFilter); //output is 08/00/21

 var outrng = sht.getRange(25,1);  
 outrng.setValue(date);
}

The first targeted cell ('var date') has a value of "9.6.21" in the spreadsheet. The cell is formatted as a date and it opens a calendar when double-clicked. When I set the new cells values (with 'outrng.setValue(date);'), the result is OK, with the same date as in the original cell.

But I do not need to simply transfer the values, I want to implement them in some loops and I have no idea how to simply get the date in the same format or at least the same date in the script as it is in the cell. As you can see from the logger, the values there are different. A simple d/m/yy format would be sufficient.

My spreadsheet settings are set to my local time (Slovenia, GMT+1).

I am guessing that I am missing some basics here. I have spent many hours trying to understand it, so any help is highly appreciated!

Answer 1

Cooper already answered all your questions in the comment. I'd like to add on and show you an example on what it would like and add some modifications.

Code:

function getDates() {
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var sht = s.getSheetByName('Dates');
  // get last row of the sheet
  var lastRow = sht.getLastRow();
  // get your sheet's timezone
  var timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  var output = [];

  // getValues is mostly used for multiple cells returning a 2D array
  // use getValue for single cells to return its actual value
  // but since function name is getDates, I assume column A is all dates
  // so we fetch the whole column (A2:A[lastRow]) except the header
  var dates = sht.getRange("A2:A" + lastRow).getValues();

  // for each date on that column, we format the date to d/M/yy
  // m/mm = minute
  // M/MM = month
  dates.forEach(function ([date]){
    Logger.log(date);
    var datumFilter= Utilities.formatDate(new Date(date), timezone, "d/M/yy");
    Logger.log(datumFilter);
    // collect all dates in an array
    output.push([datumFilter]);
  });
  // assign all the dates in the array onto range B2:B
  sht.getRange(2, 2, output.length, 1).setValues(output);  
}

Sample data:

Logs:

Output:

Note:

• The output on sheets is not equal to logs due to the formatting of my sheet.