Google表格开关的范围大小不匹配 [英] Google Sheets switch has mismatched range sizes

问题描述

我开发了一个Google表格，该表格在过去两年中一直运行良好.现在，它突然停止工作，并提供以下错误:"SWITCH具有不匹配的范围大小.预期行数:1，列数:1.实际行数: rows_in_sheet ，列数:1."

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."

基本上，工作表包含需要按一定间隔检查的项目列表(J =每天，M =每月，S =每学期，A =每年).用户选中该项目会在复选框上打勾，这将导致运行onEdit脚本，从而将lastChecked的日期更新为今天.根据时间间隔，公式将计算nextCheck的截止日期.该公式位于整个列的ArrayFormula中.

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.

如前所述，该系统可以工作到几周前，并且在不使用ArrayFormula的情况下仍然可以运行，但是我更喜欢使用ArrayFormula(便于用户添加新行).

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具有ArrayFormula，B没有，而C/D为空，但通常遵循A/B结构.

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.

错误来自包含此公式的单元格(单元格G2):

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)),"","")))

更新时间戳的代码:

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.

推荐答案

Google最近在数组公式上下文中对 switch()的行为进行了重大更改.要变通解决此问题，请尝试使用数组而不是文字作为 case 参数，如下所示:

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) 
  ) 
)

我希望这种变通办法能够证明Google将来可能通过 switch()来解决潜在问题的可能性.

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

这篇关于Google表格开关的范围大小不匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！