I’m working on a Google Sheets document to track upcoming concerts for my music group. The spreadsheet contains event details with dates stored in column C. Since we’ve been booking more gigs recently, I need a way to quickly identify which events have already passed.
I want to create a script that automatically applies specific formatting to rows where the date in column C is earlier than today’s date. The formatting should include:
- Italic text style
- Strikethrough effect
- Gray font color
I’ve been looking for solutions but haven’t found the exact code I need. While I have some general programming experience, I’m new to Google Apps Script and would really appreciate detailed explanations of the Google Sheets-specific functions and methods used in the solution.
Can someone help me write this script and explain how it works?
I built something similar for conference deadlines last year. Use conditional formatting with your script - don’t replace it entirely. Start with SpreadsheetApp.getActiveSheet() to get your sheet, then getLastRow() to find where your data ends. When comparing dates, convert both to milliseconds with getTime() - Google Sheets gets weird with timezones sometimes. For formatting, create a range object for each row using getRange(row, 1, 1, numberOfColumns) and chain your formatting methods. Watch out for empty cells in your date column - wrap your date comparison in try-catch or check for null values first. Also add a column to mark processed rows so your script doesn’t keep re-processing the same data. It’ll slow down as your event list grows.
You need a function that loops through your data and compares each date to today. I dealt with something similar for project deadlines - here’s what worked. Grab your active sheet and figure out the data range. Use getDataRange() or set exact rows if you know them. For each row, pull the date from column C with getValue() and compare it to new Date(). When a date’s older than today, format the whole row with setFontStyle('italic'), setFontLine('line-through'), and setFontColor('#999999'). Batch your operations instead of formatting cell by cell - it’s way faster. Test the script manually first, then set up a time-driven trigger to run it daily. Just make sure your dates are actual date values, not text strings, or the comparison breaks.
hey dancingbird! u can use getRange().setFontStyle(‘italic’) and setFontLine(‘line-through’) + setFontColor(‘#808080’). just loop thru ur rows, check dates against new Date(), then apply formatting for those past dates. the gsheets api docs r simple once u get started!