Google Sheets Cheat Sheet

Google Sheets Calendar Template

Here's a Google Sheets calendar template that allows you to build a monthly or yearly calendar for the year of your choice:

shows a monthly calendar from the free google sheets calendar template available for you to download
Free Calendar Sheet

Google Sheets Calendar Template

Create a monthly or yearly calendar for any year with a single click.

Get Template

When you open the sheet you'll see the Builder sheet:

shows the main builder sheet from which the monthly and yearly calendar templates can be created in google sheets

In the 'Year ➜' box: Enter the year you want to make a calendar for.

In the 'Design ➜' box: Choose between Year / Sheet for a yearly calendar and Month / Sheet 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.

shows the process of removing unnecessary sheets from the calendar template

Click here to learn about the Apps Script powering this template.

Yearly Google Sheets Calendar Template

If you want a yearly calendar:

  1. Enter the year you need
  2. Select Year / Sheet in the 'Design ➜' box
  3. 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:

shows the process of creating a yearly calendar template by clicking a single checkbox

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:

shows today's date highlighted using conditional formatting in the yearly calendar

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Monthly Google Sheets Calendar Template

If you want a monthly calendar:

  1. Enter the year you need
  2. Select Month / Sheet in the 'Design ➜' box
  3. 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:

shows the process of creating a monthly calendar template by clicking a single checkbox

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:

shows events entered into the monthly calendar template with today's date highlighted using conditional formatting

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 ToolsScript editor.

onEdit(e) Function

I'm once again using my favorite way to trigger scripts - the onEdit(e) function.

It's a simple trigger that:

  1. Fires every time a user changes a cell value in a sheet, and
  2. 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:

Dark theme
Copy code
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:

Check it out if you're interested:

Dark theme
Copy code
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:

Here it is:

Dark theme
Copy code
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:

Dark theme
Copy code
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

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.