Created
April 5, 2024 11:05
-
-
Save phillypb/cbc8e7826a2d972f577f7f4244f82d97 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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'); | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment