Google Apps Script自定义函数：第二个参数数组为空问题

当在函数内部直接定义数组时，calender_search逻辑符合预期，但通过参数传递数组时，第二个参数alt_activities_range会出现为空的问题。

函数核心逻辑：遍历作为日历的二维数组，找到最后一项（最新）活动，返回alt_activities_range的下一个值；若上一项是该范围的最后值，则从起始位置重新开始。

原示例代码 #

/**
* Looks for the alternate workout in the LAST matching value within the calendar.
* @param {array} input The calendar array to be searched.
* @return The first match starting from the last row (WEEK) and column (DAY) of the @param.
* @customfunction
*/
function calender_search(calendar_range, alt_activities_range) 
{

  /**var calendar_range = [[ , ,          ,             ,            ,     'Chess'],
                        [ , ,          ,             ,   'Pilates',   'Elevate'],
                        [ , ,          ,  'Kundalini',  'Scrabble', 'Gardening'],
                        [ , , 'Darning',    'Crossword',       'Chess',   'Pilates']];        */                                       


 //var alt_activities_range = [ 'Chess', 'Pilates', 'Elevate', 'Kundalini', 'Scrabble', 'Gardening', 'Darning', 'Crossword'];

 

  for (let week = calendar_range.length - 1; week >= 0; week--) 
  {
    for (let day = calendar_range[week].length - 1; day >= 0; day--) 
    {
      //return calendar_range[week][day]; // test where the functionality gets to.
      return alt_activities_range[day]; // array is empty when passed in as a parameter
      

      if (alt_activities_range.indexOf(calendar_range[week][day]) > -1) 
      {
        //NOT entering this if statement!!! return (JSON.stringify(calendar_range)); 
/** 
        if (calendar_range[week][day] === alt_activities_range[alt_activities_range.length - 1]) 
        {
          return alt_activities_range[0];
        }
        // Go to index of match PLUS 1 for NEXT value to be returned
        return alt_activities_range[(alt_activities_range.indexOf(calendar_range[week][day]) + 1) % alt_activities_range.length];
      }
  */  
    }
  }
}

相关表格数据 #

表1：替代活动列表 #

表2：日历数据 #

问题原因与修复方案 #

核心问题 #

1. 提前返回打断逻辑：原代码中return alt_activities_range[day];是测试代码，会直接终止函数，导致后续的查找逻辑完全无法执行。
2. Google Sheets参数格式问题：在Sheets中传递的范围参数是二维数组（即使是单行/单列），比如选中B1:H1和I1的区域，传递的alt_activities_range实际是[['Chess','Pilates',...], ['Crossword']]，而非预期的一维数组，直接使用会导致索引错误或“为空”的错觉。

修复后的代码 #

/**
* 在日历中查找最后一个匹配活动，返回对应的下一个替代活动
* @param {array} calendar_range 要搜索的日历数组
* @param {array} alt_activities_range 替代活动列表数组
* @return 最后一个活动对应的下一个替代活动
* @customfunction
*/
function calender_search(calendar_range, alt_activities_range) 
{
  // 将二维数组扁平化，适配Google Sheets的范围参数格式
  alt_activities_range = alt_activities_range.flat();
  
  // 从最后一周开始倒序遍历
  for (let week = calendar_range.length - 1; week >= 0; week--) 
  {
    // 从每周的最后一天倒序遍历
    for (let day = calendar_range[week].length - 1; day >= 0; day--) 
    {
      const currentActivity = calendar_range[week][day];
      // 跳过空单元格
      if (!currentActivity) continue;
      
      const activityIndex = alt_activities_range.indexOf(currentActivity);
      // 找到匹配的活动后返回下一个替代项（循环）
      if (activityIndex > -1) 
      {
        return alt_activities_range[(activityIndex + 1) % alt_activities_range.length];
      }
    }
  }
  
  // 未找到匹配活动时返回提示
  return "未找到匹配活动";
}

使用说明 #

在Google Sheets中调用函数时，直接选择对应的范围即可，比如：

• 日历范围选择A2:G6（对应表2的日历数据）
• 替代活动范围选择B1:I1（对应表1的替代活动列表）

内容的提问来源于stack exchange，提问作者Drew254