Google Sheets Apps Script动态联动下拉框运行缓慢，求性能优化方案

核心优化方向 #

1. 替换串行IF判断为映射表匹配，消除遍历开销 #

原来的25个串行IF判断每次触发都要逐个匹配，改为用「工作表名+触发列」作为key的映射表，直接定位要执行的逻辑，匹配时间从O(n)降到O(1)。
示例映射表结构：

// 提前定义所有联动规则，key为 工作表名|触发列号
const dropdownRules = new Map([
  ['Assembly Info|5', handleAssemblyInfoColE], // 对应原来的E列编辑触发F列下拉
  ['X|1', handleSheetXColA],
  ['Y|3', handleSheetYColC]
  // 剩余22条规则依次添加即可
])

触发时直接取key匹配，不存在直接终止执行：

const activeSheetName = ss.getName()
const triggerCol = activeCell.getColumn()
const ruleKey = `${activeSheetName}|${triggerCol}`
if (!dropdownRules.has(ruleKey) || activeCell.getRow() <=1) return
// 直接执行对应逻辑
dropdownRules.get(ruleKey)(activeCell, cachedData)

2. 缓存公共参考数据，避免重复读表 #

原来每次触发都会调用getValues()读整个参考工作表（比如Plating Recd Info、Incoming Blanks），这类基础数据不会频繁变动，用CacheService做全局缓存，有效期可以设为10-30分钟，数据更新时手动清理缓存即可，读表开销直接减少90%以上。
示例缓存逻辑：

function getCachedData(sheetName, rangeSpec) {
  const cache = CacheService.getScriptCache()
  const cacheKey = `${sheetName}_data`
  const cached = cache.get(cacheKey)
  if (cached) return JSON.parse(cached)
  // 缓存不存在则读表
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
  const data = sheet.getRange(rangeSpec).getValues()
  cache.put(cacheKey, JSON.stringify(data), 1800) // 缓存30分钟
  return data
}

3. 减少单元格读写次数 #

Apps Script中单元格读写是最耗时的操作，优化以下两点：

• 一次性读取当前编辑行需要的所有依赖列值，不要多次调用offset().getValue()
• 仅当触发列的值非空时才执行后续数据验证生成逻辑，空值直接清空目标列验证即可
  示例行数据读取优化：

// 一次性读当前行前6列的所有值，代替多次offset读
const rowVals = ss.getRange(activeCell.getRow(), 1, 1, 6).getValues()[0]
const type = rowVals[0] // A列值
const plVendor = rowVals[2] // C列值
const plvDC = rowVals[3] // D列值
const prodType = rowVals[4] // E列值

4. 优化去重逻辑，替换低效的indexOf去重 #

原来用indexOf做数组去重时间复杂度为O(n²)，换成Set去重复杂度降到O(n)，数据量越大提升越明显：

// 优化前的去重逻辑
// var distinct = (value,index,self) => { return self.indexOf(value) === index; }
// var ssSKUValidationList = ssSKUList.map(x => x[3]).filter(distinct).sort()

// 优化后
const ssSKUValidationList = [...new Set(ssSKUList.map(x => x[3]))].sort()

5. 提前拦截无效触发 #

在函数入口先判断编辑的工作表是否为需要联动的6个工作表、编辑行是否为表头，不符合直接终止执行，避免无意义的逻辑运行。

优化后核心代码示例 #

// 提前定义所有联动规则
const dropdownRules = new Map([
  ['Assembly Info|5', handleAssemblyInfoColE]
  // 其余规则依次添加
])

// 缓存参考数据工具函数
function getCachedData(sheetName, startRow, startCol, colCount) {
  const cache = CacheService.getScriptCache()
  const cacheKey = `${sheetName}_${startRow}_${startCol}_${colCount}`
  const cached = cache.get(cacheKey)
  if (cached) return JSON.parse(cached)
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName)
  const lastRow = sheet.getLastRow()
  if (lastRow < startRow) return []
  const data = sheet.getRange(startRow, startCol, lastRow - startRow + 1, colCount).getValues()
  cache.put(cacheKey, JSON.stringify(data), 1800)
  return data
}

// 单条规则处理函数示例：Assembly Info工作表E列编辑触发
function handleAssemblyInfoColE(activeCell, ss) {
  const targetCell = activeCell.offset(0, 1)
  targetCell.clearContent().clearDataValidations()
  if (activeCell.isBlank()) return
  // 一次性读当前行需要的所有值
  const rowVals = ss.getRange(activeCell.getRow(), 1, 1, 5).getValues()[0]
  const type = rowVals[0]
  let validationList = []
  if (type === 'Plated') {
    const plVendor = rowVals[2]
    const plvDC = rowVals[3]
    const prodType = rowVals[4]
    const PRI_Data = getCachedData('Plating Recd Info', 2, 2, 6)
    validationList = PRI_Data
      .filter(item => item[1] === plVendor && item[2] === plvDC && item[4] === prodType)
      .map(x => x[5])
  } else if (type === 'SS') {
    const ssRecdDate = rowVals[1]
    const prodType = rowVals[4]
    const IB_Data = getCachedData('Incoming Blanks', 2, 1, 4)
    validationList = IB_Data
      .filter(item => item[0] <= ssRecdDate && item[1] === prodType && item[2] === "Assembly - SS Condition")
      .map(x => x[3])
  }
  if (validationList.length === 0) return
  // 去重排序生成验证规则
  const distinctList = [...new Set(validationList)].sort()
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(distinctList).setAllowInvalid(false).build()
  targetCell.setDataValidation(rule)
}

function dynamicDropdown() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  const activeCell = ss.getActiveCell()
  const ruleKey = `${ss.getName()}|${activeCell.getColumn()}`
  // 无效触发直接退出
  if (!dropdownRules.has(ruleKey) || activeCell.getRow() <= 1) return
  // 执行对应规则
  dropdownRules.get(ruleKey)(activeCell, ss)
}

function onEdit() {
  dynamicDropdown()
}

以上优化方案落地后，正常场景下拉框加载时间可降到1秒以内，数据量较大的场景也不会超过2秒。

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