How add a Calendar Picker with Data Validation in Google Sheets

The "Date" formats can help a lot, but they don't give you a calendar picker for blank cells, which also means that you can't guarantee the same format for new entries in the future. Don't rely the format options to keep your data clean - use data validation to enforce your date format standards.

Make sure to watch the video to follow along with the code: You're adding Dates WRONG If you don't DO THIS

This "Simple Trigger" will fire off whenever someone makes an edit on your spreadsheet. To make it your own - update Sheet1 to whatever your sheet's name is. Then update the 4 in getRange(2, 4, sheet.getMaxRows(), 1) to the column number of where your dates are being stored/managed.

you can also change the .setHelpText text to whatever you want.

function onEdit(e) {
    const sheet = e.source.getSheetByName('Sheet1')
    const range = sheet.getRange(2, 4, sheet.getMaxRows(), 1)
    const rule = SpreadsheetApp.newDataValidation().requireDate().setAllowInvalid(false).setHelpText("Only valid dates allowed").build();
    range.setDataValidation(rule);
    range.setNumberFormat('mm/dd/yyyy')
}

======= KEEP LEARNING WITH Bootstrapping Tools =======

🎬 MOST RECENT VIDEOS: https://www.youtube.com/c/bootstrappingtools/videos

🤖 Automate with Google Apps Script: https://bit.ly/BootstrappingTools-GoogleAppsScript

💻 Build-Through Videos: https://bit.ly/BootstrappingTools-Lets-Build

🚑 Community Support Videos: https://bit.ly/BootstrappingTools-CommunitySupport

======================================================

👥 Looking for something specific? feedback@bootstrapping.tools

💝 Support us by buying us a coffee

======================================================