Google Apps Script运行时优化咨询：三段代码提速方案

针对你提到的三个耗时模块，以下是具体优化方法，可大幅降低总运行时长：

1. 优化工作表获取逻辑 var roster = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]; #

原代码每次触发都调用getActiveSpreadsheet().getSheets()[0]，会额外加载所有工作表数据，且很多场景下该变量并未被使用。

优化点： #

• 直接通过工作表名称获取目标表，避免遍历所有工作表：e.source.getSheetByName("Roster")
• 将表的获取移至条件判断内部，仅当需要时才执行，减少无意义的API调用

修改示例： #

将原代码开头的：

console.time('myFunction');
var sheet = e.source.getActiveSheet();
var col = e.range.getColumn();
var roster = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
console.timeEnd('myFunction');

改为：

console.time('myFunction');
var sheet = e.source.getActiveSheet();
var col = e.range.getColumn();
console.timeEnd('myFunction');

并将后续的判断条件sheet != roster改为：

if (columnMappings[col] && sheet.getName() !== "Roster")

2. 优化JSON字典加载 function loadIconDictionary() #

原代码每次字典为空时都从Drive读取JSON，单次耗时406ms，是主要性能瓶颈。

优化方案（分场景）： #

场景1：使用可安装触发器（拥有Drive权限）

利用CacheService缓存字典数据，仅第一次加载时读取Drive，后续直接从缓存获取：

var ICON_DICTIONARY = {};

function loadIconDictionary() {
  var cache = CacheService.getScriptCache();
  var cachedData = cache.get("ICON_DICT");
  
  // 优先读取缓存
  if (cachedData) {
    return JSON.parse(cachedData);
  }

  var fileId = '1Qe_ST11nJIBnPGX_98N0Etd2iHiG70cd';
  var file = DriveApp.getFileById(fileId);
  if (!file) {
    Logger.log('未找到目标文件');
    return {};
  }

  var fileContent = file.getBlob().getDataAsString();
  if (!fileContent) {
    Logger.log('文件内容为空');
    return {};
  }

  try {
    var parsedData = JSON.parse(fileContent);
    // 缓存1小时（可根据更新频率调整）
    cache.put("ICON_DICT", JSON.stringify(parsedData), 3600);
    return parsedData;
  } catch (e) {
    Logger.log('JSON解析错误: ' + e);
    return {};
  }
}

场景2：使用简单触发器onEdit（无Drive权限）

将字典数据存于当前表格的隐藏工作表中，通过批量读取替代Drive调用：

1. 创建名为IconDictionary的工作表，设置为隐藏
2. A列存键（类名），B列存图片URL
3. 修改加载函数：

var ICON_DICTIONARY = {};

function getIconDictionary() {
  var cache = CacheService.getScriptCache();
  var cachedData = cache.get("ICON_DICT");
  
  if (cachedData) {
    return JSON.parse(cachedData);
  }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dictSheet = ss.getSheetByName("IconDictionary");
  if (!dictSheet) {
    Logger.log('未找到字典工作表');
    return {};
  }

  // 批量读取所有数据
  var data = dictSheet.getDataRange().getValues();
  var dict = {};
  for (var i = 1; i < data.length; i++) { // 跳过表头行
    dict[data[i][0]] = data[i][1];
  }

  cache.put("ICON_DICT", JSON.stringify(dict), 3600);
  return dict;
}

3. 优化单元格值读取 var currentCell = sheet.getRange(currentRow, columnMappings[col].sourceCol).getValue(); #

原代码重复调用getRange()和getValue()，单次耗时357ms，核心问题是不必要的API调用。

优化点： #

• 单单元格编辑场景：直接使用触发事件的e.range.getValue()，无需重新获取单元格
• 批量编辑场景：一次性读取整列数据到数组，从数组中取值，避免循环内重复调用API

修改示例： #

单编辑版本优化：

将原代码中的：

var currentCell = sheet.getRange(currentRow, columnMappings[col].sourceCol).getValue();

替换为：

var currentCell = e.range.getValue(); // 直接使用触发事件的单元格值

批量编辑版本优化：

将循环内的单个单元格读取改为批量读取：

// 批量读取源列和目标列数据
var sourceValues = sheet.getRange(currentRow, columnMappings[col].sourceCol, numRows, 1).getValues();
var targetValues = sheet.getRange(currentRow, targetCol, numRows, 1).getValues();

var valuesToSet = [];  
for (var i = 0; i < numRows; i++) {
  var currentCell = sourceValues[i][0]; // 从数组取值，无API调用
  var cellValue = '';

  if (ICON_DICTIONARY.hasOwnProperty(currentCell)) {
    cellValue = '=IMAGE("' + ICON_DICTIONARY[currentCell] + '")';
  }
  else if (currentCell !== "" || currentCell === false) {
    cellValue = targetValues[i][0]; // 从数组取目标列原有值
  }

  valuesToSet.push([cellValue]);
}

优化后完整代码（单编辑版本） #

var ICON_DICTIONARY = {};

//Loads icon if a user fills in a class name
function userEdit(e) {
  console.time('total');
  var sheet = e.source.getActiveSheet();
  var col = e.range.getColumn();

  // Define the column mappings for your specific case
  var columnMappings = {
    1: { sourceCol: 1, targetCol: 2 },
    7: { sourceCol: 7, targetCol: 8 },
    13: { sourceCol: 13, targetCol: 14 }
  };

  // Check if the edited column is in the mappings
  if (columnMappings[col] && sheet.getName() !== "Roster") {
    var currentRow = e.range.getRow();
    var targetCol = columnMappings[col].targetCol;

    // Load dictionary with cache
    if (Object.keys(ICON_DICTIONARY).length === 0) {
      ICON_DICTIONARY = loadIconDictionary();
    }

    // Get cell value directly from event
    var currentCell = e.range.getValue();

    // Batch set target value
    var targetRange = sheet.getRange(currentRow, targetCol);
    if (ICON_DICTIONARY.hasOwnProperty(currentCell)) {
      targetRange.setValue('=IMAGE("' + ICON_DICTIONARY[currentCell] + '")');
    }
    else if (currentCell === "") {
      targetRange.setValue('');
    }
  }
  console.timeEnd('total');
}

function loadIconDictionary() {
  var cache = CacheService.getScriptCache();
  var cachedData = cache.get("ICON_DICT");
  
  if (cachedData) {
    return JSON.parse(cachedData);
  }

  var fileId = '1Qe_ST11nJIBnPGX_98N0Etd2iHiG70cd';
  var file = DriveApp.getFileById(fileId);
  if (!file) {
    Logger.log('未找到目标文件');
    return {};
  }

  var fileContent = file.getBlob().getDataAsString();
  if (!fileContent) {
    Logger.log('文件内容为空');
    return {};
  }

  try {
    var parsedData = JSON.parse(fileContent);
    cache.put("ICON_DICT", JSON.stringify(parsedData), 3600);
    return parsedData;
  } catch (e) {
    Logger.log('JSON解析错误: ' + e);
    return {};
  }
}

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