Auto-styling Google Sheets rows with past dates using Apps Script

Other Questions Google Sheets
1

I created a schedule tracker in Google Sheets for my music group to track our upcoming performances. With so many gigs recently, it’s becoming difficult to quickly spot which events are coming up next.

I want to automatically style rows where the date in column C has already passed. Specifically, I’d like past events to appear with italic text, strikethrough formatting, and gray font color.

I’ve done some research but can’t find the exact solution I need. While I have some coding experience, this is my first time working with Google Apps Script. Could someone help me with the code and explain the Google Sheets-specific functions?

Any guidance would be greatly appreciated!

2

one thing to watch out for - make sure you clear the old formatting first or you’ll get weird layering effects. I use clearFormat() on the range before applying new styles, otherwise rows that switch back to future dates still look grayed out. also double check your sheet permissions if the script doesn’t seem to be running automatically

3

I ran into a similar issue with my project tracking spreadsheet and found that adding a condition to preserve formatting for current dates works better. The solution above is solid, but you might want to consider adding today.setHours(0,0,0,0) before the comparison to avoid timezone issues that can cause today’s events to get marked as past due. Also worth mentioning that if you have any merged cells or special formatting you want to preserve, you’ll need to be more selective about which properties you’re changing. I learned this the hard way when it overwrote some conditional formatting I had set up for priority levels. For the automatic execution, I’d recommend starting with a daily trigger around midnight rather than hourly to avoid hitting quota limits. You can always adjust the frequency later based on how often your schedule changes.

4

Here’s a working solution that I’ve used for similar tracking scenarios. You’ll need to create a function in Apps Script that runs automatically to check dates and apply formatting.

function formatPastDates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var dateRange = sheet.getRange(2, 3, lastRow - 1, 1); // Column C starting from row 2
  var dates = dateRange.getValues();
  var today = new Date();
  
  for (var i = 0; i < dates.length; i++) {
    if (dates[i][0] instanceof Date && dates[i][0] < today) {
      var row = sheet.getRange(i + 2, 1, 1, sheet.getLastColumn());
      row.setFontStyle('italic')
         .setFontLine('line-through')
         .setFontColor('#808080');
    }
  }
}

To make this run automatically, set up a time-driven trigger in Apps Script that executes this function daily. The key functions here are getRange() for selecting cells, getValues() for reading data, and the formatting methods like setFontStyle(). Make sure your dates in column C are properly formatted as dates, not text strings, otherwise the comparison won’t work correctly.