Correct date formats in a Google Sheet

The following Google Apps Script is just one way you could search through data within a Google Sheet, pinpoint all date-values (no matter where they are), check their format (e.g. dd/mm/yyyy) and update it to a format of your choosing if necessary.

This function has been useful when American/British date formats have managed to make their way into a Google Sheet.

Screenshot showing difference between American/British date formats

The Code

There are a few bits of code to tease out and explore, starting with us getting all of the Sheets within the file so that we can eventually loop through each of them in-turn:

var ss = SpreadsheetApp.getActiveSpreadsheet();

var allSheets = ss.getSheets();

var allSheetsLength = allSheets.length;

There are 3 For Loops to juggle in this function: one to loop through each Sheet tab, one to loop through each row of data and one to loop through each column of data. We will also be using the 'getNumberFormats()' method in order to determine dates from other format-types (e.g. numbers, formulas).

We need to be able to test the format of each cell of data we loop through in order to ensure we only target date values. The following will return a value of true if a date is found:

var dateCheck = Object.prototype.toString.call(singleValue) === '[object Date]';

We can then compare this with our desired format to know if we need to take any action:

if (cellFormat != "dd/mm/yyyy") {

Set our desired format:

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

Download

Correct date formats in a Google Sheet download (please use 'File' > 'Make a copy' for your own version).