Locate a text string and return the width of the range containing it based on whether it's in a single or merged cell

Question

I have calendar data in my spreadsheet. Each row is a day, and each column is a half-hour increment of time from that day.

When I add events to the calendar I block off the time by merging the relevant columns.

The calendar data is in the range B3:Y77.

In a separate column AA I have a list of event names that match events found in the calendar.

In column AD I want to return the number of cells that are merged in the calendar by the event listed in AA.

For example, if the event is in a merged cell that includes three columns, the value returned would be 3. If the event is in a single unmerged cell 1 would be returned.

I almost succeeded using the Google Apps Script below. It returns the correct value however it only works when I first enter the formula into AD. It doesn't update if I change the event.

For example, I would call the function ContaCelleUnite in AD3 using the formula =ContaCelleUnite(AA3;"B3:Y77")

Can someone help me?

      function createTriggers() {
    
      ScriptApp.newTrigger("onEdit")
        .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
        .onEdit()
        .create();
        
      ScriptApp.newTrigger("onChange")
        .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
        .onChange()
        .create();
    }
    function ContaCelleUnite(specifiText, inputRangeFormula) {
      var sheet = SpreadsheetApp.getActiveSheet();
    
      if (inputRangeFormula && inputRangeFormula.trim() !== "") {
        var inputRange = sheet.getRange(inputRangeFormula).getA1Notation();
        var numColonneTotale = 0;
        var mergedRanges = sheet.getRange(inputRange).getMergedRanges();
    
        for (var i = 0; i < mergedRanges.length; i++) {
          var mergedRange = mergedRanges[i];
          var mergedValues = mergedRange.getValues();
    
          if (mergedValues[0][0].toString().includes(specifiText)) {
            numColonneTotale += mergedRange.getNumColumns();
          }
        }
    
        return numColonneTotale;
      } else {
        return "InputRangeFormula non valida";
      }
    }
    
    function onEdit(e) {
      if (e) {
        var range = e.range;
        var formula = range.getFormula();
    
        if (formula.includes("ContaCelleUnite")) {
          var formulaArgs = formula.match(/[(](.*?)[)]/)[1].split(",");
          var specificText = formulaArgs[0].replace(/"/g, "").trim();
          var inputRangeFormula = formulaArgs[1].trim();
    
          
          var sheet = range.getSheet();
          var inputRange = sheet.getRange(inputRangeFormula).getA1Notation();
    
          var result = ContaCelleUnite(specificText, inputRange);
          Logger.log("Risultato: " + result);
    
       
          range.setValue(result);
        }
      }
    }
    function onChange(e) {
      if (e && e.range) { 
        var range = e.range;
        var cellValue = range.getValue();
        if (cellValue.includes("ContaCelleUnite")) {
          var formulaArgs = cellValue.match(/[(](.*?)[)]/)[1].split(",");
          var specificText = formulaArgs[0].replace(/"/g, "").trim();
          var inputRangeFormula = formulaArgs[1].trim();
    
          var sheet = range.getSheet();
          var inputRange = sheet.getRange(inputRangeFormula).getA1Notation();
    
          var result = ContaCelleUnite(specificText, inputRange);
          Logger.log("Risultato: " + result);
    
          
          range.setValue(result);
        }
      }
}

Answer 1

Overview

The types of changes you are making are not detected by the function and therefore do not trigger a refresh. It sounds like you want to:

1. Pass the address of a range and a string into a function
2. Locate the position of that string within the range associated with the rangeAddress and also determine if the string is located in columns that are merged.
3. Return the number of columns containing the string (1+).

Formula in J2

=BYROW(I2:I25, LAMBDA(r, IF(LEN(r),
   timeSlots(M1, r),IFERROR(1/0))))

The formula calls timeSlots and passes it every string in Column I (I2:I25 in this example).

Function

timeSlots(rangeAddress, string)  - Returns the number of columns that are in use by a meeting:

• rangeAddress  - A string representing the address of a range. In the example, a reference is used to M1 whose value is "A1:G15"
• string  - A text string (or reference to same) that matches a meeting found within the range.

Apps Script

function timeSlots(rangeAddress, string) {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange(rangeAddress);
  const values = range.getValues();

  for (let i = 0; i < values.length; i++) {

    let index = values[i].indexOf(string); 

    if (index >= 0) { 
      
      const strRow = range.getRow() + i;
      const strCol = range.getColumn() + index;
      let width = 1;
  
      range.getMergedRanges().forEach((rng,i) => { 
        (strRow == rng.getRow()) &&
        (strCol == rng.getColumn()) &&
        (width = rng.getWidth())});

    return width;

    } 

  }

}

To return the total time, replace return width with

return width * 0.5

Notes

Use the line numbering reference in the image below along with the notes table.

Line #		Notes
5	values	An array of arrays. Each array member is a row, which itself is an array of column values.
7	loop	Loops through each array (row) in values
9	index	The index/position of the lookup string in each array/row. Will be >=0 if string exists, or -1 if not found
11	if	Condition is that string was found in the current array/row
13	strRow	Row in range where string was matched. Calculated by adding the first-row number in range to the position of the array value. For example, if the range being searched is 4 rows in height, starts on row 15 in the spreadsheet, and string is found in values[i] where i=2, then the actual range row number containing string is 15+2=17
14	strCol	Similarly uses the starting column number of the range added to the index/position where the string was found in values[i] to determine the actual range column number containing the string
15	width	If a string is successfully located it will span a minimum of 1 columns (default).
17	loop	For each merged range returned from range, tries to match its address to the address of the string
20	width	If the addresses match, the width value is replaced with the width of the merged range.
22	return	Returns the value of width to the spreadsheet. Could alternatively return the number hrs. by returning width * 0.5 etc.

_{Line Numbering Reference}

Resources

Links to documentation for some of the methods used:

sheet.getRange(string)  range.getValues() array.indexOf() range.getRow() range.getColumn() array.forEach()