https://www.pbainbridge.co.uk/2024/06/correct-date-formats-in-google-sheet.html

Correct date formats in a Google Sheet.gs

/**
 * Developed by The Gift of Script: https://www.pbainbridge.co.uk/
 */


function onOpen() {

  SpreadsheetApp.getUi()
    .createMenu('Admin')
    .addItem('Fix Dates', 'fixDates') // label for menu item, name of function to run.
    .addToUi();

}



function fixDates() {

  // get current Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // get array of all Sheets, then its length (for looping through)
  var allSheets = ss.getSheets();
  var allSheetsLength = allSheets.length;
  console.log(allSheetsLength);

  // create Counter to diplay popup at the end informing of how many dates were updated
  var datesChangedCounter = 0;


  // loop through each Sheet in turn ************************************************
  for (var k = 0; k < allSheetsLength; k++) {

    // get single Sheet
    var sheet = allSheets[k];
    var sheetName = sheet.getName();
    console.log("sheetName name is: " + sheetName);


    var allDataRange = sheet.getDataRange();
    var allDataRangeFormats = allDataRange.getNumberFormats();
    var allData = allDataRange.getValues();


    // loop through rows
    for (var i = 0; i < allData.length; i++) {

      var row = i + 1;

      // loop through columns
      for (var j = 0; j < allData[i].length; j++) {

        var column = j + 1;

        // get single cell value
        var singleValue = allData[i][j];

        // test if cell value is a date object
        var dateCheck = Object.prototype.toString.call(singleValue) === '[object Date]';

        if (dateCheck) {

          Logger.log(singleValue);
          Logger.log("Value is a date");

          // get cell format
          var cellFormat = allDataRangeFormats[i][j];
          Logger.log("cellFormat is: " + cellFormat);


          // check if cell format is correct
          if (cellFormat != "dd/mm/yyyy") {

            Logger.log("Cell format needs changing.");

            sheet.getRange(row, column).setNumberFormat("dd/mm/yyyy");

            // increment Counter
            datesChangedCounter++;


          } else {

            //Logger.log("Cell format is fine.");

          };


        } else {

          //Logger.log("Value is not a date");

        };

      };

    };


  };
  // loop through each Sheet in turn ************************************************


  // show confirmation popup box
  var htmlOutput = HtmlService
    .createHtmlOutput(datesChangedCounter + " dates were corrected.")
    .setWidth(320)
    .setHeight(100);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Completed');


};