Origin

Since I couldn't find a suitable project management tool, and my needs are quite specific, I wanted to use Google Sheets, Google Calendar, and Google Apps Script to create one myself. Actually, I didn't write it myself - I had ChatGPT help me write it. The main function of this tool is to accurately display very small units in my projects on my calendar. Since I have many samples that need tracking for stability changes over time, I need a tool that can accurately record the time points for these samples and remind me. Also, this tool needs to be able to display on Outlook, which I use at work, so I can see project progress while working.

Through Google Sheets formulas, I can enter the sample preparation time and calculate the observation time points (such as 1 day, 3 days, 7 days, 14 days, 21 days, 28 days). Then, through Google Apps Script, I add these time points and related tasks to my Google Calendar, and sync to my computer through Outlook. This way, I can see on my work computer which samples need tracking. Additionally, since I frequently adjust and modify based on TransREM load, the App Script automatically starts at 1 AM every day to get the day's information and fill it into my Google Calendar project. This way, I can see project progress on my work computer the next morning.

1. Create Google Sheet

Create a spreadsheet according to your needs to track project progress. Here I created a spreadsheet to track sample preparation progress. The content is as follows:

2. Google Apps Script Code

From the Extension - Apps Script at the top of Google Sheets, enter the Google Apps Script editing interface, then copy the code below, save it, run it, authorize it, and you'll be able to see corresponding events in your calendar.

function addComplexEventsToCalendar() {
  // Get active spreadsheet
  var sheet = SpreadsheetApp.getActiveSheet();

  // Read date data from C3:G100
  var dateData = sheet.getRange('C3:H73').getValues();

  // Read column A content
  var aColumn = sheet.getRange('A3:A73').getValues();

  // Read row 2 content
  var secondRow = sheet.getRange('C2:H2').getValues()[0];

  // Get specific calendar using Calendar ID
  var calendar = CalendarApp.getCalendarById('cebe7a75ebba32d2898d1cd61aa32617cd40c21e81dff4e0e34ac1fbed1e687d@group.calendar.google.com');

  // Get today's date
  var today = new Date();
  today.setHours(0, 0, 0, 0);

  // Iterate through date data
  for (var i = 0; i < dateData.length; i++) {
    for (var j = 0; j < dateData[i].length; j++) {
      // Only process non-empty dates
      if (dateData[i][j]) {
        var eventDate = new Date(dateData[i][j]);
        eventDate.setHours(0, 0, 0, 0);

        // Only process events for today
        if (eventDate.getTime() === today.getTime()) {
          var eventTitle = aColumn[i][0] + ' ' + secondRow[j]; // Use column A and row 2 combination as event name

          // Set start and end times
          var startTime = new Date(eventDate);
          startTime.setHours(9, 0, 0);

          var endTime = new Date(eventDate);
          endTime.setHours(11, 0, 0);

          // Create event
          calendar.createEvent(eventTitle, startTime, endTime);
        }
      }
    }
  }
}

3. Set Scheduled Task

In the Google Apps Script editing interface, click the clock icon on the left, then set up a scheduled task. Here I set it to run once every day at 1 AM, so I can sync the day's work tasks to my Google Calendar.

4. Set Up Outlook Sync

In Google Calendar, click settings, then select the corresponding calendar, then select Public address in iCal format, then add a calendar in Outlook, select From Internet, then paste the copied address, and you'll be able to see events from your Google Calendar.

The benefit of this tool is that it can accurately display very small units in my projects on my calendar, so I can see project progress while working. Additionally, since I frequently adjust and modify based on TransREM load, the App Script automatically starts at 1 AM every day to get the day's information and fill it into my Google Calendar project. This way, I can see project progress on my work computer the next morning.

I searched through all project management tools and couldn't find one that can conveniently use formulas to calculate time. I'm talking about you, Notion - Notion's formulas are really difficult to use. Date input also requires entering one by one, which is very cumbersome.

The combined use of Google Sheets and Google Calendar really meets my expectations.

Another point is that without ChatGPT, I wouldn't be able to write Google Apps Script. Now with ChatGPT or LLM support, I can write code like this. This kind of code is very useful for me, helping me improve efficiency and allowing me to focus more on my work.

By the way, I wrote this article in VSCode using GitHub Copilot, which automatically completes content for me, allowing me to finish this article very quickly. This efficiency improvement is truly amazing.