DatePicker in Sidebar for Google Sheets
This calendar for Google Sheets is displayed in the sidebar and allows you to easily insert the selected date into one or multiple cells.
You can copy this Google Sheets document from this page.
On this page, you will find the source code of this calendar (if you want to better understand how to create a custom sidebar in Google Sheets, follow this link).
Calendar Preview
Clickable Preview of the Calendar
Apps Script Code
// Custom Menu
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Sheets-Pratique')
.addItem('DatePicker', 'calendar')
.addToUi();
}
// Sidebar
function calendar() {
const html = HtmlService.createTemplateFromFile('calendar').evaluate().setTitle('Calendar');
SpreadsheetApp.getUi().showSidebar(html);
}
// Include for HTML Page
function include(name) {
return HtmlService.createHtmlOutputFromFile(name).getContent();
}
// Inserting the Date into Selected Cells
function insertDate(d) {
SpreadsheetApp.getActiveSheet().getActiveRange().setValue(new Date(d));
}
HTML Code
<!DOCTYPE html>
<html>
<head>
<?!= include('css'); ?>
</head>
<body>
<?!= include('js'); ?>
</body>
</html>
JavaScript Code
<script>
// Display
function displayCalendar(month, year, selection) {
/*********************************************************************/
/** DatePicker created by Sébastien Mathier - Sheets-Pratique.com **/
/*********************************************************************/
const today = new Date().toDateString();
const selectedDate = new Date(selection);
const nextMonth = month + 1 > 11 ? 0 : month + 1;
const previousMonth = month - 1 < 0 ? 11 : month - 1;
const nextYear = month + 1 > 11 ? year + 1 : year;
const previousYear = month - 1 < 0 ? year - 1 : year;
let day = new Date(year, month, 1);
const firstDayOfWeek = day.getDay();
day.setDate(day.getDate() - firstDayOfWeek);
let calendar = `<div id="day_of_week">${['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'][selectedDate.getDay()]}</div><div id="month_year"><span>${selectedDate.getDate()}</span><img onclick="displayCalendar(${nextMonth}, ${nextYear}, '${new Date(nextYear, nextMonth, 1).toDateString()}')" src="data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz48IURPQ1RZUEUgc3ZnIFBVQkxJQyAiLS8vVzNDLy9EVEQgU1ZHIDEuMS8vRU4iICJodHRwOi8vd3d3LnczLm9yZy9HcmFwaGljcy9TVkcvMS4xL0RURC9zdmcxMS5kdGQiPjxzdmcgaGVpZ2h0PSIyNCIgdmlld0JveD0iMCAwIDI0IDI0IiB3aWR0aD0iMjQiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+PHBhdGggZmlsbD0iI2ZmZiIgZD0iTTguNTksMTYuNThMMTMuMTcsMTJMOC41OSw3LjQxTDEwLDZMMTYsMTJMMTAsMThMOC41OSwxNi41OFoiLz48L3N2Zz4=" id="right"><img onclick="displayCalendar(${previousMonth}, ${previousYear}, '${new Date(previousYear, previousMonth, 1).toDateString()}')" src="data:image/svg+xml;base64,PD94bWwgdmVyc2lvbj0iMS4wIiBlbmNvZGluZz0iVVRGLTgiPz48IURPQ1RZUEUgc3ZnIFBVQkxJQyAiLS8vVzNDLy9EVEQgU1ZHIDEuMS8vRU4iICJodHRwOi8vd3d3LnczLm9yZy9HcmFwaGljcy9TVkcvMS4xL0RURC9zdmcxMS5kdGQiPjxzdmcgaGVpZ2h0PSIyNCIgdmlld0JveD0iMCAwIDI0IDI0IiB3aWR0aD0iMjQiIHhtbG5zPSJodHRwOi8vd3d3LnczLm9yZy8yMDAwL3N2ZyI+PHBhdGggZmlsbD0iI2ZmZiIgZD0iTTE1LjQxLDE2LjU4TDEwLjgzLDEyTDE1LjQxLDcuNDFMMTQsNkw4LDEyTDE0LDE4TDE1LjQxLDE2LjU4WiIvPjwvc3ZnPg==" id="left">${['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'][selectedDate.getMonth()]} ${selectedDate.getFullYear()}</div><div id="calendar"><div>SU</div><div>MO</div><div>TU</div><div>WE</div><div>TH</div><div>FR</div><div>SA</div>`;
for (let w = 0; w < 6; w++) {
for (let d = 0; d < 7; d++) {
const dayString = day.toDateString();
const monthDay = day.getMonth();
calendar += `<div class="${monthDay == month ? '' : 'grey'}${dayString == today ? ' today' : ''}${dayString == selection ? ' selected' : ''}" onclick="displayCalendar(${monthDay}, ${day.getFullYear()}, '${dayString}')">${day.getDate()}</div>`;
day.setDate(day.getDate() + 1);
}
}
// Insertion
document.body.innerHTML = `${calendar}</div><div id="button" onclick="google.script.run.insertDate('${selection}')">Insert date into cells</div><a href="https://www.sheets-pratique.com/en" target="_blank">Sheets-Pratique.com</a>`;
}
// Initial Display
window.addEventListener('load', function() {
const date = new Date();
displayCalendar(date.getMonth(), date.getFullYear(), date.toDateString());
});
</script>
CSS Code
<style>
/*********************************************************************/
/** DatePicker created by Sébastien Mathier - Sheets-Pratique.com **/
/*********************************************************************/
body {
margin: 0;
background: #eee;
font-family: Roboto, Arial, sans-serif;
text-align: center;
}
#day_of_week {
background: #473fa5;
color: #fff;
padding: 0.5rem;
font-size: 1.2rem;
}
#button {
padding: 0.9rem 0 0.75rem;
background: #455fbb;
color: #fff;
font-size: 1.05rem;
cursor: pointer;
}
#month_year {
padding: 0.7rem 0 0.5rem;
background: #455fbb;
color: #fff;
line-height: 2rem;
font-size: 1.5rem;
}
#month_year span {
display: block;
font-size: 3rem;
line-height: 3rem;
}
#right, #left {
position: absolute;
height: 3rem;
margin-top: -0.5rem;
cursor: pointer;
}
#right {
right: 0;
}
#left {
left: 0;
}
#calendar {
display: grid;
grid-template-columns: repeat(7, 1fr);
width: 100%;
background: #f9f9f9;
}
#calendar > div {
padding: 0.6rem 0 0.5rem;
font-size: 1rem;
color: #777;
background: #eee;
}
#calendar > div:nth-child(n+8) {
padding: 0 1px 0 0;
color: #333;
cursor: pointer;
line-height: 43px;
background: transparent;
}
#calendar > div:nth-child(n+8):hover {
background: #4f48ad;
color: #fff;
border-radius: 50%;
}
#calendar > div.grey {
color: #b0b0b0;
}
#calendar > div.today {
font-weight:bold;
}
#calendar > div.selected {
background: #556ec5;
color: #fff;
border-radius: 50%;
}
a {
display: block;
padding: 1rem 0;
font-size: 0.95rem;
text-decoration: none;
color: #aaa;
cursor: pointer;
}
a:hover {
text-decoration: underline;
color: #455fbb;
}
</style>