How to Automatically Remove Date Validations from All Sheets in Google Sheets?
Recently, while working on a shared Google Sheet, I noticed something odd. Every time someone edited a column that had a date header, it would automatically trigger a date validation. The cell would suddenly expect a proper date format and reject anything else.
This might sound helpful — but in many workflows (like mine), it’s just a nuisance. Especially when your date fields are imported as text or meant to stay flexible.
So I wrote a small Apps Script to fix it. It’s simple, safe, and runs across all sheets.
Let me walk you through it.
The Problem
In many spreadsheets, especially those used by teams, columns named “Invoice Date” or “Due Date” often get a default data validation as soon as someone types into them. This happens when Google Sheets tries to be helpful — but ends up enforcing restrictions that you never asked for.
The issue:
- It slows down data entry.
- It causes errors when pasting bulk data.
- It doesn’t make sense when dates are stored as plain text.
So instead of manually clearing data validations from each date column across all sheets — I automated it.
The Script
Here’s the exact code I used:
function clearDateColumnValidations() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
sheets.forEach(sheet => {
const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
headers.forEach((header, colIndex) => {
if (typeof header === 'string' && header.toLowerCase().includes("date")) {
const range = sheet.getRange(2, colIndex + 1, sheet.getMaxRows() - 1);
range.clearDataValidations();
}
});
});
}
That’s it. No external libraries, no dependencies.
This function loops through all sheets in your file. Then, for each column where the header contains the word “date” (case-insensitive), it removes the data validation from every row below it.
▶️ How to Use It
- Open your Google Sheet
- Go to Extensions → Apps Script
- Paste the code above
- Save it
- Click Run → clearDateColumnValidations
If you want this to happen every time the file opens, just add this inside your onOpen()
function:
function onOpen() {
clearDateColumnValidations();
}
That way, it clears the validations automatically every time someone opens the sheet.
Conclusion
This little script made my spreadsheet easier to use — not just for me, but for everyone on the team. No more unwanted validations. No more interrupted workflows.
It’s a small tweak, but a handy one.
If you’ve got similar issues or want to automate more parts of your spreadsheet, Apps Script is worth exploring. You don’t have to be a full-time coder to make it work for you.
Let your spreadsheet follow your rules.