Function to open a Google sheet on the current day at the current time, using the spreadsheet's conditional formatting.

README.md

Philosophy

I have a Google Sheets-driven daily tracker which I use to track every hour of my day for a whole year. This is the fourth year I've been doing this, so I've become mildly obsessed with getting this system to work exactly to my liking.

For quite some time, I've looked at other scripts for Sheets that others have made and the experience was largely disappointing and frustrating. I figured this would be an interesting little project to put out, and I'm publishing it in case someone has the same or similar needs as I did, and hopefully this will help with that.

An illustrative Screenshot

Here is a screenshot of the exact spreadsheet structure and conditional formatting setup that this script was built for. I've included instructions in the script's comments detailing how to set up the conditional formatting.

Enjoy!

openToNow.gs

/**
 * Open to now.
 * 
 * This function uses the spreadsheet's conditional formatting to automatically
 * place the cursor on the current day at the current time. This avoids having
 * to manually scroll/search for the current time.
 * 
 * Conditonal Formatting Setup; these rules are needed for proper functionality
 * 
 *    For the range C1:Z1, containing times 12am - 11pm, the custom formula for
 *    the rule is [=Hour(C1:Z1)=Hour(now())] without brackets.
 * 
 *    For the range A2:A366, containing dates 1/1 - 12/31, the formatting rule
 *    is "Date is" / "today".
 * 
 * Built with Google App Script for Sheets on desktop.
 * @author Trevor Sweet <trevor@setbold.com>
 * @copyright Trevor Sweet 2021
 * @license MIT
 */
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var dayOfYear;                                                                // number day of the year
  var activeColor = "#ffffff";                                                  // color to search for

  var range = sheet.getRange("A1:A366");                                        // A1:A366 contains dates (1/1 - 12/31)
  var bgColors = range.getBackgrounds();                                        // cell background colors in A1:A366

  for (dayOfYear in bgColors) {                                                 // iterate through colors in A1:A366
    // Logger.log("Finding day:" + bgColors[dayOfYear][0]);
    
    if (bgColors[dayOfYear][0].match(activeColor) == activeColor) {             // if color matches defined activeColor
      break;                                                                    // ...stop and keep track of the day
    }
  }

  range = sheet.getRange("C1:Z1");                                              // C1:Z1 contains times (12am - 11pm)
  bgColors = range.getBackgrounds();                                            // cell background colors in C1:Z1

  for (var j in bgColors[0]) {                                                  // iterate though colors in C1:Z1
    // Logger.log("Finding time: " + bgColors[0][j]);

    if (bgColors[0][j].match(activeColor) == activeColor) {                     // if color matches defined activeColor
      sheet.setActiveRange(range.offset(dayOfYear, j, 1, 1));                   // ...stop and set active range to cell
      break;
    }
  }
}