Google Apps Script超时问题：Spreadsheets服务超时优化求助

问题根源分析 #

你遇到的「Service timed out: Spreadsheets」错误，核心原因是频繁调用SpreadsheetApp服务（循环内逐行读写、反复获取Range），谷歌脚本对服务调用次数和执行时长有严格上限，高频调用极易触发超时。另外表格内大量VLOOKUP实时计算会占用前端资源，导致卡顿。

代码优化方案 #

1. 批量读写数据（核心优化，解决超时）

原代码循环内逐行调用getRange().setValues()，每次调用都要和服务器通信，是最大性能瓶颈。优化方式是先将所有数据存入数组，最后一次性写入表格，同时用脚本计算时长替代表格公式，减少前端计算压力。

// 通用日历导入函数，消除重复代码
function exportGcalToSheet(calendarId, sheetName, dateStart, dateEnd) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const cal = CalendarApp.getCalendarById(calendarId);
  const events = cal.getEvents(new Date(dateStart), new Date(dateEnd));

  // 初始化表头+数据数组
  const data = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event","Event ID"]];
  
  // 批量收集事件数据，脚本内计算时长
  events.forEach(event => {
    const start = event.getStartTime();
    const end = event.getEndTime();
    // 直接计算时长（小时），替代表格公式
    const duration = (end.getTime() - start.getTime()) / (1000 * 60 * 60);

    data.push([
      calendarId,
      event.getTitle(),
      event.getDescription() || "",
      event.getLocation() || "",
      start,
      end,
      duration.toFixed(2),
      event.getVisibility().toString(),
      event.getDateCreated(),
      event.getLastUpdated(),
      event.getMyStatus().toString(),
      event.getCreators().join(","),
      event.isAllDayEvent(),
      event.isRecurringEvent(),
      event.getId()
    ]);
  });

  // 清空表格+一次性写入所有数据（效率提升10倍以上）
  sheet.clearContents();
  sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
  
  // 最后统一排序，移除循环内无效排序操作
  sheet.sort(5, true);
}

// 重构双日历导入函数
function importBoth() {
  const backendSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Backend");
  const [date1, date2, cal1Id, cal2Id] = [
    backendSheet.getRange('B6').getValue(),
    backendSheet.getRange('B7').getValue(),
    backendSheet.getRange('B2').getValue(),
    backendSheet.getRange('B3').getValue()
  ];
  
  exportGcalToSheet(cal1Id, "Cal1 Calendar Import", date1, date2);
  exportGcalToSheet(cal2Id, "Cal2 Calendar Import", date1, date2);
}

// 重构合并日历导入函数
function combined_export_gcal_to_gsheet(){
  const backendSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Backend");
  const [date1, date2, combinedCalId] = [
    backendSheet.getRange('B6').getValue(),
    backendSheet.getRange('B7').getValue(),
    backendSheet.getRange('B1').getValue()
  ];
  
  exportGcalToSheet(combinedCalId, "Combined Schedule Import", date1, date2);
}

2. 优化合并日历事件创建逻辑

原代码存在重复逻辑、未过滤空行等问题，优化后封装通用函数，同时保留错误日志但不中断批量处理：

// 通用事件创建函数
function createEventsFromSheet(sheetName, isAllDay = false) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const calendarId = sheet.getRange('C1').getValue();
  const eventCal = CalendarApp.getCalendarById(calendarId);
  
  // 获取有效数据（过滤空行）
  const data = sheet.getRange(3, 2, sheet.getLastRow() - 2, 6).getValues()
    .filter(row => row[0] !== "");

  data.forEach(row => {
    const [summary, startTime, endTime, guests, description, location] = row;
    const eventOpts = {
      location: location || "",
      description: description || "",
      guests: guests ? `${guests},` : "",
      sendInvites: true // 用布尔值替代字符串
    };

    try {
      isAllDay 
        ? eventCal.createAllDayEvent(summary, new Date(startTime), new Date(endTime), eventOpts)
        : eventCal.createEvent(summary, new Date(startTime), new Date(endTime), eventOpts);
    } catch(error) {
      console.error(`事件创建失败: ${summary}`, error);
      // 记录错误后继续处理，不中断整体流程
    }
  });
  console.log(`${sheetName} 事件创建完成`);
}

// 重构批量创建函数
function CombinedSchedulerALL() {
  createEventsFromSheet("Combined Scheduler u24", false);
  createEventsFromSheet("Combined Scheduler 24up", true);
  MoveDataCSu24();
  MoveDataCSov24();
}

3. 优化数据移动逻辑

// 通用数据归档函数
function moveDataToArchive(sourceSheetName, archiveSheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  const archiveSheet = ss.getSheetByName(archiveSheetName);
  
  // 获取非空行数据
  const data = sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).getValues()
    .filter(row => row[0] !== "");

  if (data.length === 0) return;
  
  // 一次性写入归档表
  archiveSheet.getRange(archiveSheet.getLastRow() + 1, 1, data.length, data[0].length).setValues(data);
  
  // 清空源表数据（保留表头）
  sourceSheet.getRange(2, 1, sourceSheet.getLastRow() - 1, sourceSheet.getLastColumn()).clearContents();
}

// 替换原数据移动函数
function MoveDataCSu24() {
  moveDataToArchive("Combined Schedule Logger u 24hrs", "Combined Schedule Archive");
}

function MoveDataCSov24() {
  moveDataToArchive("Combined Schedule Logger 24up", "Combined Schedule Archive");
}

表格层面优化（解决VLOOKUP卡顿） #

• 替换VLOOKUP为INDEX+MATCH：INDEX+MATCH性能远优于VLOOKUP，例如原公式=VLOOKUP(A2, Sheet2!A:B, 2, FALSE)可替换为=INDEX(Sheet2!B:B, MATCH(A2, Sheet2!A:A, 0))
• 缩小公式范围：避免使用整列（如A:A）作为查找范围，改为实际数据范围（如A2:A1000），减少计算量
• 脚本预处理数据：将需要VLOOKUP计算的结果提前用脚本计算并写入表格，彻底消除实时公式计算
• 使用QUERY批量处理：如果是批量匹配数据，用QUERY函数一次性返回结果，减少单个公式数量

定时触发额外优化 #

• 拆分任务：将importBoth、CombinedSchedulerALL、combined_export_gcal_to_gsheet拆分为三个独立定时任务，避免单个任务执行时间过长
• 缩小同步范围：如果date1和date2覆盖时间过久，会导致事件数量过多，建议只同步最近30天和未来30天的数据

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