Apps Script Cell Alignment Based on part of string/date

Question

I am trying to determine the font alignment in each cell in a range based on the cell’s value. Each cell contains a start time and end time (ie. 9:30am-5:30pm) in which I am trying to set the alignment based on the start time:

• Shifts starting between 4:00am and 10:59am : Left Align--------(>4am <11am)
• Shifts starting between 11:00am and 2:00pm : Center Align-----(>=11am <=2pm)
• Shifts starting between 2:01pm and 3:59am : Right Align--------(>2pm <4am)
• Everything else Center Align

Looks like this:

I have read numerous posts that accomplish portions of what I am trying to do but I haven’t been able to create a solution that works, my script so far is probably worth ignoring. Here is an Example Sheet.

As for the script...

function Test1() {
  const wb = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = wb.getActiveSheet();
  const values = sheet.getRange(1,1,26,8).getValues();

then I tried to unsuccessfully use the following approaches however I could not figure out how to search by part of a string or to splice all the values in a range or splice and handle value as Timevalue. Here are the other posts I tried to incorporate:

Align all numbers in range - could not figure out how to search part of string.

Text contains valueCould not figure out the proper way to set the formatting in the "do something section"

Based on value Could only get it to work with full string and only in the first column.

Answer 1

The values in the grid shown in the question are not times of the day but text strings that just look like times of the day. To extract the start time, use String.match(), and to decide which alignment to apply, use Array.find(), like this:

'use strict';

/**
* Simple trigger that runs each time the user opens
* the spreadsheet.
*
* @param {Object} e The onOpen() event object.
*/
function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Align')
    .addItem('Align selected cells by start time', 'alignByValue')
    .addToUi();
}


/**
* Aligns cells by the time of day extracted from their contents.
*
* @param {Range} range The range to align. Defaults to the active range.
* @param {String[][]} values Optional. The display values in the range.
*/
function alignByValue(range = SpreadsheetApp.getActiveRange(), values = range.getDisplayValues()) {
  // version 1.0, written by --Hyde, 4 May 2024
  //  - see https://stackoverflow.com/q/78427740/13045193
  const settings = {

    ////////////////////////////////
    // [START modifiable parameters]
    keyPattern: /(\d\d:\d\d)\s*(am|pm)/,
    _getKey: (s) => `${s[2]}${s[1]}`,
    alignUpperLimits: [
      ['am11:00', 'left'],
      ['pm02:01', 'center'],
      ['pm11:59', 'right'],
    ],
    // [END modifiable parameters]
    ////////////////////////////////

  };
  if (!Array.isArray(values)) values = [[values]];
  const alignments = values.map(row => row.map(value => {
    const keyValue = `0${value.trim().toLowerCase()}`.match(settings.keyPattern); // add leading zero
    if (!keyValue) return;
    const alignment = settings.alignUpperLimits.find(limit => settings._getKey(keyValue) < limit[0]);
    if (!alignment) return;
    return alignment[1];
  }));
  range.setHorizontalAlignments(alignments);
}


/**
* Simple trigger that runs each time the user manually edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  if (!e) {
    throw new Error(
      'Please do not run the onEdit(e) function in the script editor window. '
      + 'It runs automatically when you hand edit the spreadsheet. '
      + 'See https://stackoverflow.com/a/63851123/13045193.'
    );
  }
  alignByValue_(e);
}


/**
* Aligns cells when they are manually edited.
*
* @param {Object} e The onEdit() event object.
*/
function alignByValue_(e) {
  try {
    const parameters = [

      ////////////////////////////////
      // [START modifiable parameters]
      {
        sheetsToWatch: /^(Wk)/i,
        sheetsToExclude: /^(Master)$/i,
        columnStart: 3,
        columnEnd: 9,
        rowStart: 5,
        rowEnd: Infinity,
      },
      // [END modifiable parameters]
      ////////////////////////////////

    ];
    let sheet, sheetName;
    const settings = parameters.find(p =>
      (p.columnStart <= e.range.columnStart && e.range.columnEnd <= p.columnEnd)
      && (p.rowStart <= e.range.rowStart && e.range.rowEnd <= p.rowEnd)
      && (sheetName = sheetName || (sheet = sheet || e.range.getSheet()).getName()).match(p.sheetsToWatch)
      && (!p.sheetsToExclude || !sheetName.match(p.sheetsToExclude))
    );
    if (!settings) return;
    alignByValue(e.range, e.value); // relying on callee to get displayValues when e.value === undefined
  } catch (error) {
    e.source.toast(`${error.message} ${error.stack}`, 'Error in alignByValue_()', 30);
    throw error;
  }
}

See String.match() and Array.find().