Google Sheets Calendar Template
Updated: July 9, 2021 Here's a Google Sheets calendar template that allows you to build a monthly or yearly calendar for the year of your choice:
Google Sheets Calendar Template
Create a monthly or yearly calendar for any year with a single click.
Get TemplateWhen you open the sheet you'll see the Builder sheet:
In the 'Year ➜' box: Enter the year you want to make a calendar for.
In the 'Design ➜' box: Choose between for a yearly calendar and for a monthly calendar.
To create a calendar click the 'Build ➜' checkbox.
No tedious updating when January 1st comes around - just click a single checkbox.
To remove all sheets except the Builder sheet, click the 'Remove ➜' checkbox.
Click here to learn about the Apps Script powering this template.
Yearly Google Sheets Calendar Template
If you want a yearly calendar:
- Enter the year you need
- Select in the 'Design ➜' box
- Click the green 'Build ➜' checkbox
And you're done. Some Apps Script runs in the background to automatically insert a yearly calendar in your Google Sheet:
The yearly calendar is great if you want an overview of how the year is progressing.
If the calendar is for this year, today's date will be highlighted in green:
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
Monthly Google Sheets Calendar Template
If you want a monthly calendar:
- Enter the year you need
- Select in the 'Design ➜' box
- Click the green 'Build ➜' checkbox
And you're done. Some Apps Script runs in the background to automatically insert 12 sheets each with a month of the chosen year:
These monthly calendars are large and have space for you to enter data and if a sheet is for the current month, today's date will be highlighted in green:
They're the perfect template for a content calendar or social media calendar in Google Sheets.
Google Sheets Calendar Template Apps Script
I'm now going to provide some insight into how the Apps Script that drives the calendar template works.
You can see the full script by making a copy of the sheet and going to ➜ .
onEdit(e) Function
I'm once again using my favorite way to trigger scripts - the onEdit(e) function.
It's a simple trigger that:
- Fires every time a user changes a cell value in a sheet, and
- Allows you to execute scripts that affect the active spreadsheet without requiring user authorization
This makes it accessible and functional without being scary (authorization would require you to allow the script I've written to manipulate all of the Google Sheets you own, even though it doesn't do that).
The trick with functions is to makes them fail as fast as possible so they don't slow down your sheet.
In this sheet, I only want the script to first if either of the checkboxes in the Builder sheet are ticked. That's why I include a few initial checks that immediately end the function if the conditions are met:
function onEdit(e) {
if (e.oldValue !== 'false' && e.value !== 'TRUE') return;
const cell = e.range;
if (cell.columnStart !== 4) return;
const row = cell.rowStart;
if (row === 5) createCalendar();
if (row === 6) cleanSheet();
if (row ===5 || row===6) cell.setValue(false);
}
Creating The Yearly Calendar
This script takes the year you enter and constructs a 3x4 grid of months in a single sheet named after the year.
It also:
- removes unnecessary rows and columns
- adjusts the column widths
- adds colors and borders
- applies conditional formatting
Check it out if you're interested:
function createYearly(ss, year) {
const lastCol = 25;
const lastRow = 39;
const colWidth = 35;
const firstRow = 4;
const firstCol = 2;
let currentRow = 4;
let currentCol = 2;
const days = ['S', 'M', 'T', 'W', 'T', 'F', 'S'];
const sheet = ss.insertSheet('' + year);
sheet.setHiddenGridlines(true);
sheet.deleteColumns(lastCol + 1, sheet.getMaxColumns() - lastCol);
sheet.deleteRows(lastRow + 1, sheet.getMaxRows() - lastRow);
sheet.setColumnWidths(1, sheet.getMaxColumns(), colWidth);
sheet.setRowHeight(2, 40);
sheet.getRange(2, firstCol, 1, sheet.getMaxColumns() - firstCol)
.merge()
.setValue(year)
.setFontWeight("bold")
.setFontColor(white)
.setBackground(blue)
.setHorizontalAlignment('center')
.setVerticalAlignment('middle')
.setFontSize(18);
sheet.setFrozenRows(2);
for (let i = 0; i < months.length; i++) {
sheet.getRange(currentRow, currentCol)
.setValue(months[i])
.setFontWeight("bold")
.setFontColor(white)
.setBackground(blue)
.setHorizontalAlignment('center');
sheet.getRange(currentRow, currentCol, 1, 7)
.merge();
sheet.getRange(currentRow + 1, currentCol, 1, 7)
.setValues([days])
.setFontWeight("bold");
sheet.getRange(currentRow + 1, currentCol, 7, 7)
.setBorder(true, true, true, true, true, true, blue, null)
.setHorizontalAlignment('center')
.setNumberFormat('d');
const first = new Date(year, i, 1);
let day = first.getDay();
const monthMatrix = new Array(6).fill('').map(() => new Array(7).fill(''));
const monthLength = new Date(year, i + 1, 0).getDate();
for (let j = 1; j <= monthLength; j++) {
const rowIndex = Math.trunc(day / 7);
const colIndex = day % 7;
monthMatrix[rowIndex][colIndex] = new Date(year, i, j);
day++;
}
sheet.getRange(currentRow + 2, currentCol, monthMatrix.length, monthMatrix[0].length).setValues(monthMatrix);
currentRow = firstRow + Math.trunc((i + 1) / 3) * 9;
currentCol = firstCol + ((i + 1) % 3) * 8;
}
const wholeSheet = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
const formattingRule = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=A1=TODAY()')
.setBackground(green)
.setFontColor(white)
.setBold(true)
.setRanges([wholeSheet])
.build();
const formattingRules = sheet.getConditionalFormatRules();
formattingRules.push(formattingRule);
sheet.setConditionalFormatRules(formattingRules);
}
Creating The Monthly Calendar
This script takes the year you enter and constructs one sheet for each month in the year.
It also:
- removes unnecessary rows and columns
- adjusts the column widths
- adds colors and borders
- applies conditional formatting
Here it is:
function createMonthly(ss, year) {
const lastCol = 9;
const lastRow = 41;
const colWidth = 140;
const firstRow = 3;
const firstCol = 2;
const months = [
"January", "February", "March",
"April", "May", "June",
"July", "August", "September",
"October", "November", "December",
]
const days = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
for (let i = 0; i < months.length; i++) {
const sheet = ss.insertSheet(`${months[i].slice(0, 3)} ${year}`);
sheet.setHiddenGridlines(true);
sheet.deleteColumns(lastCol + 1, sheet.getMaxColumns() - lastCol);
sheet.deleteRows(lastRow + 1, sheet.getMaxRows() - lastRow);
sheet.setColumnWidths(firstCol, sheet.getMaxColumns() - firstCol, colWidth);
sheet.setColumnWidths(1, 1, 35);
sheet.setColumnWidths(sheet.getMaxColumns(), 1, 35);
sheet.setRowHeights(1, 2, 10);
sheet.getRange(firstRow, firstCol, 1, 7)
.merge()
.setValue(`${months[i]} ${year}`)
.setFontWeight("bold")
.setFontColor(white)
.setBackground(blue)
.setHorizontalAlignment('center')
.setFontSize(18);
sheet.setFrozenRows(4);
sheet.getRange(firstRow + 1, firstCol, 1, 7)
.setValues([days])
.setFontWeight("bold")
.setFontSize(14)
.setHorizontalAlignment('center')
.setBorder(true, true, true, true, true, true, blue, null);
for (let i = firstRow + 2; i <= lastRow; i++) {
const row = sheet.getRange(i, firstCol, 1, 7);
if (i % 6 === (firstRow + 2)) {
if (i !== lastRow) {
row.setFontSize(12)
.setBorder(true, true, true, true, true, true, blue, null)
.setHorizontalAlignment('center')
.setBackground(grey)
.setNumberFormat('d');
} else {
row.setBorder(true, false, false, false, false, false, blue, null);
}
} else {
row.setBorder(null, true, null, true, true, false, blue, null);
}
}
const first = new Date(year, i, 1);
let day = first.getDay();
const monthMatrix = new Array(36).fill('').map(() => new Array(7).fill(''));
const monthLength = new Date(year, i + 1, 0).getDate();
for (let j = 1; j <= monthLength; j++) {
const rowIndex = Math.trunc(day / 7) * 6;
const colIndex = day % 7;
monthMatrix[rowIndex][colIndex] = new Date(year, i, j);
day++;
}
sheet.getRange(firstRow + 2, firstCol, monthMatrix.length, monthMatrix[0].length).setValues(monthMatrix);
const wholeSheet = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
const formattingRuleOne = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=A1=TODAY()')
.setBackground(green)
.setFontColor(white)
.setBold(true)
.setRanges([wholeSheet])
.build();
const formattingRuleTwo = SpreadsheetApp.newConditionalFormatRule()
.whenFormulaSatisfied('=OR(OFFSET(A1,-1,0)=TODAY(),OFFSET(A1,-2,0)=TODAY(),OFFSET(A1,-3,0)=TODAY(),OFFSET(A1,-4,0)=TODAY(),OFFSET(A1,-5,0)=TODAY())')
.setBackground(lightGreen)
.setRanges([wholeSheet])
.build();
const formattingRules = sheet.getConditionalFormatRules();
formattingRules.push(formattingRuleOne);
formattingRules.push(formattingRuleTwo);
sheet.setConditionalFormatRules(formattingRules);
}
}
Cleaning The Calendar
If you make a monthly calendar and the year ends it can be a pain to remove each of the sheets one-by-one.
Instead you can click the 'Remove ➜' checkbox to run the cleanSheet() function:
function cleanSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
sheets.forEach(sheet => {
if (sheet.getName() !== 'Builder') {
ss.deleteSheet(sheet);
}
});
}
It deletes every sheet that isn't the Builder sheet leaving you with a clean sheet for the new year.
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
