Automatically style past events in a Google Sheets band gig list

Other Questions Google Sheets
1

Hey folks, I need some help with my band’s gig schedule in Google Sheets. We’ve been super busy lately and it’s getting tricky to spot our next show at a glance.

I’m wondering if there’s a way to automatically format the rows for gigs that have already happened. Ideally, I’d like to make them italic, with a strikethrough, and change the font color to gray. This would apply to any row where the date in column C is earlier than today.

I’ve tried searching for a solution, but I’m new to Google Apps Script and haven’t found exactly what I need. If anyone can help out with some code, I’d really appreciate it! And if you could break down the Google Sheets-specific parts, that’d be awesome.

Here’s a basic example of what I’m working with:

function stylePassedGigs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var today = new Date();

  for (var i = 1; i < data.length; i++) {
    var gigDate = new Date(data[i][2]);
    if (gigDate < today) {
      // Need help with formatting this row
    }
  }
}

Thanks in advance for any help!

2

I’ve encountered a similar challenge with my event planning spreadsheet. Here’s a solution that might work for you:

function stylePassedGigs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var today = new Date();

  for (var i = 1; i < values.length; i++) {
    var gigDate = new Date(values[i][2]);
    if (gigDate < today) {
      var rowRange = sheet.getRange(i + 1, 1, 1, sheet.getLastColumn());
      rowRange.setTextStyle(SpreadsheetApp.newTextStyle()
        .setItalic(true)
        .setStrikethrough(true)
        .setForegroundColor('#808080')
        .build());
    }
  }
}

This script uses the TextStyle builder for more efficient formatting. It applies all styles in one go, which can be faster for larger datasets. Remember to set up a time-driven trigger to run this function daily for automatic updates.

3

I’ve dealt with a similar issue for my band’s gig schedule. Here’s a solution that worked for me:

function stylePassedGigs() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var today = new Date();

  for (var i = 1; i < values.length; i++) {
    var gigDate = new Date(values[i][2]);
    if (gigDate < today) {
      var rowRange = sheet.getRange(i + 1, 1, 1, sheet.getLastColumn());
      rowRange.setFontStyle('italic');
      rowRange.setFontLine('line-through');
      rowRange.setFontColor('#808080');
    }
  }
}

This script iterates through your gig list, checks if the date has passed, and applies the formatting you wanted. The key is using the setFontStyle, setFontLine, and setFontColor methods on the row range. You can trigger this script to run daily or manually when needed. Hope this helps!

4

hey sofiap, that’s a cool idea! i’ve used something similar for my band’s schedule. here’s a quick tweak to your code:

if (gigDate < today) {
  sheet.getRange(i+1, 1, 1, sheet.getLastColumn())
    .setFontStyle('italic')
    .setFontLine('line-through')
    .setFontColor('#808080');
}

this should do the trick. good luck with your gigs!