PKM

Year At a Glance

A Novel way to visualize your year using Google Sheets.

Scott Novis

Mar 1, 2024 • 3 min read

One way to get the most out of your effort is to lay it all out and look at it at once. This year at a glance calendar which I learned about from Ali Abdaal on YouTube.

Why It Matters:

People often overestimate what they can do in a day or a week, but vastly underestimate what can get done in a year. The Year at a Glance Calendar gives you a simple way to lay out the year in a way that you can plan for major events, activities, and holidays. Seeing the year laid out like this can make it simpler to conceptualize the time you will spend achieving your goals and supporting your roles for the upcoming 12 months.

Big Picture:

Basically, if you can sit down and "pencil out" what you want to do in a year, it gives you a framework to unlock more long-range productivity.

How To:

This google sheet is easily copied and the google sheets JavaScript is included so you can generate a year calendar a year of your own choosing.

Basically, make a copy of the sheet, and save it in your google drive, or download it as an Excel sheet (XLS) and mark off the important dates of the year.

JavaScript

function createYearAtAGlanceCalendar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();
  sheet.clear(); // Clear the current sheet before generating the calendar

  // Prompt for year input
  const ui = SpreadsheetApp.getUi();
  const response = ui.prompt('Enter Year', 'Please enter the year for the calendar:', ui.ButtonSet.OK_CANCEL);

  // Exit if Cancel is pressed or no input is given
  if (response.getSelectedButton() != ui.Button.OK || !response.getResponseText()) {
    ui.alert('No year entered. Exiting script.');
    return;
  }

  const year = parseInt(response.getResponseText());
  if (isNaN(year)) {
    ui.alert('Invalid year. Please enter a valid four-digit year.');
    return;
  }

  // Define month header colors
  const monthColors = [
    '#FFCCCC', '#CCFFCC', '#CCCCFF', '#FFFF99',
    '#FFCC99', '#99CCFF', '#FF99CC', '#CC99FF',
    '#99FFCC', '#CCFF99', '#99CC99', '#FFCCFF'
  ];
  
  let currentColumn = 1;
  
  for (let month = 0; month < 12; month++) {
    const date = new Date(year, month, 1);
    const daysInMonth = new Date(year, month + 1, 0).getDate();
    
    // Set month header
    const monthRange = sheet.getRange(1, currentColumn, 1, 2);
    // monthRange.merge().setValue(date.toLocaleString('default', { month: 'long' })).setBackground(monthColors[month]);
    monthRange.merge().setValue(date.toLocaleString('default', { month: 'short' }) + '-' + date.getFullYear().toString().slice(-2)).setBackground(monthColors[month]);
    monthRange.setHorizontalAlignment('center').setFontWeight('bold');
    
    // Set column widths
    sheet.setColumnWidth(currentColumn, 25); // Date column
    sheet.setColumnWidth(currentColumn + 1, 125); // Event column
    
    for (let day = 1; day <= daysInMonth; day++) {
      const currentDate = new Date(year, month, day);
      const rowIndex = day + 1; // Offset by 1 to account for the header
      
      // Write the day of the month
      sheet.getRange(rowIndex, currentColumn).setValue(currentDate.getDate());
      
      // Shade weekends
      if (currentDate.getDay() == 0 || currentDate.getDay() == 6) {
        sheet.getRange(rowIndex, currentColumn, 1, 2).setBackground(monthColors[month]);
      }
    }
    
    currentColumn += 2; // Move to the next set of columns for the next month
  }
  
  // Trim extra rows and columns
  // Assuming X = 24 for the 12 months (2 columns per month)
  const totalColumnsToKeep = 24;
  const totalRowsToKeep = 34;

  // Calculate the total number of columns and rows currently in the sheet
  const totalColumns = sheet.getMaxColumns();
  const totalRows = sheet.getMaxRows();

  // Delete extra columns, if any
  if (totalColumns > totalColumnsToKeep) {
    sheet.deleteColumns(totalColumnsToKeep + 1, totalColumns - totalColumnsToKeep);
  }

  // Delete extra rows, if any
  if (totalRows > totalRowsToKeep) {
    sheet.deleteRows(totalRowsToKeep + 1, totalRows - totalRowsToKeep);
  }
  
  // Optionally, adjust the row heights or apply additional formatting as needed
}

To run the script, you need to use Apps Script to create a new script.

Instructions to Implement the Script

Open your Google Sheet where you want the calendar.
Go to Extensions > Apps Script.
Delete any code in the script editor and paste the code provided above.
Save the script with a name, for example, YearAtAGlanceCalendar.
Run the script by clicking the play/run button or manually calling the createYearAtAGlanceCalendar function within the Apps Script editor.