Google Sheets switch has mismatched range sizes

0
votes

I have developed a Google sheet that has been working well over the past 2 years. Now, it has suddenly stopped functioning, providing the following error: "SWITCH has mismatched range sizes. Expected row count: 1, column count: 1. Actual row count: number_of_rows_in_sheet, column count: 1."

Essentially the sheet contains a list of items that need to be checked at a certain interval (J=daily, M=monthly, S=semesterly, A=annually). The user checking the item ticks a checkbox, which causes an onEdit script to run, updating the date of lastChecked to today. Based on the interval, a formula calculates the deadline for nextCheck. This formula is in an ArrayFormula for the entire column.

As said, the system worked until a couple of weeks ago, and it still works when not using ArrayFormula, but I would prefer to work with ArrayFormula (easier for the user to add new rows).

A functional copy is available here. Sheet A is with ArrayFormula, B without, and C/D are empty but normally follow the A/B structure.

The error comes from the cell containing this formula (cell G2):

=ArrayFormula(IF(E2:E="","", SWITCH(D2:D, "A", DATE(YEAR(E2:E)+IF(C2:C="",1,C2:C),MONTH(E2:E),DAY(E2:E)), "M", DATE(YEAR(E2:E),MONTH(E2:E)+IF(C2:C="",1,C2:C),DAY(E2:E)), "S", DATE(YEAR(E2:E),MONTH(E2:E)+IF(C2:C="",6,C2:C *6),DAY(E2:E)), "J", DATE(YEAR(E2:E),MONTH(E2:E),DAY(E2:E)+IF(C2:C="",1,C2:C)),"","")))

The code updating the timestamp:

function onEdit(e) {  //On edit...
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const changedCell = e.range;  //Saves the range of the modified cell.
  const sheetName = sheet.getName();
  const sheetsToWatch = ['A', 'B', 'C', 'D'];
  
  if (sheetsToWatch.includes(sheetName)) {
    if(changedCell.getColumn() === 6 && changedCell.isChecked()) {   //If cell is in col 6 (F) and checked
      let newDateRange = sheet.getRange(changedCell.getRow(), 5);
      newDateRange.setValue(new Date());   //Set the current timestamp in column 5 (E) at the same row as modified cell
      
      changedCell.uncheck();               //Remove checkmark from modified cell
    }
  }
}

This is my first post so let me know if I forgot any useful information. Thanks in advance.

2
google-apps-scriptgoogle-sheets

2 Answers

0
votes

You might want to consider using IFS() instead of SWITCH() as a workaround:

Cell G2 Formula:

=ArrayFormula(IF(E2:E="","", IFS(D2:D="A", DATE(YEAR(E2:E)+IF(C2:C="",1,C2:C),MONTH(E2:E),DAY(E2:E)), D2:D="M", DATE(YEAR(E2:E),MONTH(E2:E)+IF(C2:C="",1,C2:C),DAY(E2:E)), D2:D="S", DATE(YEAR(E2:E),MONTH(E2:E)+IF(C2:C="",6,C2:C *6),DAY(E2:E)), D2:D="J", DATE(YEAR(E2:E),MONTH(E2:E),DAY(E2:E)+IF(C2:C="",1,C2:C)))))

enter image description here

I didn't find any concrete root cause why you encountered such error. I'm suspecting it has something to do with the DATE(), SWITCH() and ARRAYFORMULA() combination.

0
votes

Google recently made a breaking change in the behavior of switch() in an array formula context. To work around the issue, try using array instead of a literal as the case parameter, like this:

=arrayformula( 
  switch( 
    D2:D, 
    iferror(D2:D/0, "A"), eomonth(E2:E, if(C2:C, C2:C, 1) * 12 - 1) + day(E2:E), 
    iferror(D2:D/0, "S"), eomonth(E2:E, if(C2:C, C2:C, 1) * 6 - 1) + day(E2:E), 
    iferror(D2:D/0, "M"), eomonth(E2:E, if(C2:C, C2:C, 1) - 1) + day(E2:E), 
    iferror(D2:D/0, "J"), E2:E + if(C2:C, C2:C, 1), 
    iferror(1/0) 
  ) 
)

I would expect this workaround to be future-proof against Google's possible fix to the underlying issue with switch().