Google Apps Script自定义函数执行超时，求优化方案

问题描述 #

我用Google Apps Script给Google Sheets编写了一个自定义函数，用于计算大矩形内可容纳的小矩形数量（从左下角开始，先填满列再换行），后续还需要获取小矩形在大矩形内的位置。但在Sheets中运行该函数时，总是返回“执行时间超出”错误，而在在线JavaScript编译器中运行仅需不到1秒。我确定问题出在嵌套的while循环，但不知道如何在不牺牲功能的前提下优化，同时也疑惑这么简单的函数为何在App Script中运行如此缓慢。

原代码如下：

function counter(input,input2) {
var mx=input[0];
  var my=input[1];
  var links=input2[0];
  var unten=input2[1];


  // 间距
  var xmin=0.3;
  var ymin=0.3;

  // 小矩形尺寸
  var xlength=mx;
  var ylength=my;

  var counter=0
  var pvx1={}
  var pvy1={}

  // X方向起始位置
  var xPosition=xmin;

  
  while (xPosition<=(unten-xlength-xmin)){
    var yPosition=ymin;
    while (yPosition<=(links-ylength-ymin)){
    
      counter=counter+1;
      yPosition=yPosition+ylength;
    
    }
    xPosition=xPosition+xlength;
  }
  

return counter
}

为何App Script中运行如此缓慢？ #

• 运行环境差异：在线JS编译器要么在本地浏览器执行，要么在高性能服务器直接运行，没有额外的环境开销。但Google Apps Script运行在Google云端，自定义函数调用时需要处理Sheets与脚本引擎间的数据传递、权限校验、单元格格式转换等额外操作，这些开销在循环次数较多时会被无限放大，导致耗时飙升。
• 循环效率低下：嵌套while循环本质是逐次累加计数，当大矩形尺寸较大时，循环次数会呈倍数增长，每一次循环的变量更新、条件判断都会积累耗时。
• 自定义函数执行模式：Google Sheets的自定义函数每次调用都会重新初始化脚本环境，若多个单元格调用该函数，会重复执行逻辑，进一步加剧耗时。

优化方法（保留功能且大幅提速） #

核心思路是用数学计算替代循环计数，直接通过除法和取整得出可容纳的数量，完全去掉嵌套循环：

优化后的代码 #

function counter(input, input2) {
  const mx = input[0];
  const my = input[1];
  const links = input2[0];
  const unten = input2[1];

  // 间距
  const xmin = 0.3;
  const ymin = 0.3;

  // 小矩形尺寸
  const xlength = mx;
  const ylength = my;

  // 计算X方向可容纳的列数：可用宽度除以小矩形宽度，向下取整
  const xCount = Math.floor((unten - 2 * xmin) / xlength);
  // 计算Y方向可容纳的行数：可用高度除以小矩形高度，向下取整
  const yCount = Math.floor((links - 2 * ymin) / ylength);

  // 总数量 = 列数 × 行数
  const total = xCount * yCount;

  // 若后续需要获取每个小矩形的位置，可在此生成坐标数组（示例为左下角坐标）
  const positions = [];
  for (let x = 0; x < xCount; x++) {
    const xPos = xmin + x * xlength;
    for (let y = 0; y < yCount; y++) {
      const yPos = ymin + y * ylength;
      positions.push([xPos, yPos]);
    }
  }

  // 可根据需求返回总数量，或返回包含数量和位置的对象
  // return total;
  return { count: total, positions: positions };
}

优化点说明 #

• 数学计算替代循环：直接通过Math.floor((可用空间) / 小矩形尺寸)计算每行/列的数量，避免了成千上万次的循环迭代，耗时从线性复杂度降为常数复杂度O(1)。
• 变量声明优化：用const替代var，利用块级作用域减少变量查找开销，同时提升代码可读性。
• 位置生成优化：若需要获取位置，用for循环替代while循环（JS中for循环通常比while循环更高效），且仅在需要时生成坐标，避免不必要的计算。
• 清理冗余变量：删除原代码中未使用的pvx1和pvy1变量，减少内存占用。

额外提速建议 #

• 避免重复计算：若多个单元格调用该函数，可考虑使用onEdit触发器批量计算，或一次性将结果写入多个单元格，减少函数调用次数。
• 精简参数传递：尽量让函数接收的参数更简洁，避免传递大数组，降低数据传输的开销。

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