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

google sheets sidebar datepicker png

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>