Google Sheets同步Google Calendar空单元格/重复事件问题咨询

核心问题根因说明 #

• 空单元格中断执行：原有代码未做日期有效性校验，传入空值调用日历接口会直接抛出异常，终止后续所有行的遍历逻辑
• 重复事件生成：原有代码无增量判断逻辑，每次运行全量创建新事件；第二版代码额外追加了全量创建的冗余逻辑，且事件ID回写范围参数错误导致ID无法存入表格，无法匹配已有事件做更新
• 第二个创建函数报错：JS中同名函数会被后定义的覆盖，原有两个createCalendarEvent重名，第一个函数逻辑完全失效，第二个函数运行时因空值等问题直接报错
• 多工作表适配失效：SpreadsheetApp.getActiveSheet()仅能获取当前用户打开的激活工作表，无法遍历4个季度标签页
• 自定义菜单不显示：原有onOpen函数仅创建了菜单容器，未添加任何可点击的菜单项
• 无日期行报错：未做空值拦截，无日期时直接调用接口触发参数错误

前置调整说明 #

仅需在现有表格最右侧追加1列，表头填写itk event ID，用于存储第二个日历的事件ID，原有列结构完全保留，列索引对应关系如下：

完整可用代码 #

// 配置项 - 替换为自己的日历ID
const CONFIG = {
  learningCalId: "替换为你的学习日历ID",
  itkCalId: "替换为你的ITK日历ID",
  headerRowCount: 2, // 表头占2行，从第3行开始读数据
  col: {
    lessonDate: 0,
    articleDate: 1,
    learningEventId: 2,
    lessonCheck: 3,
    articleCheck: 4,
    status: 5,
    title: 6,
    itkEventId: 7
  }
}

// 打开表格时生成自定义菜单
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('日历同步工具')
    .addItem('立即同步所有工作表日程', 'syncAllSheetsToCalendar')
    .addToUi();
}

// 编辑单元格时自动触发同步（需安装可安装触发器）
function onEdit(e) {
  if (!e) return;
  syncSheetToCalendar(e.range.getSheet());
}

// 同步所有工作表到对应日历
function syncAllSheetsToCalendar() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  // 遍历所有4个季度工作表
  allSheets.forEach(sheet => {
    syncSheetToCalendar(sheet);
  })
  SpreadsheetApp.getUi().alert("同步完成，无重复事件生成");
}

// 单个工作表同步逻辑
function syncSheetToCalendar(sheet) {
  const learningCal = CalendarApp.getCalendarById(CONFIG.learningCalId);
  const itkCal = CalendarApp.getCalendarById(CONFIG.itkCalId);
  const lastRow = sheet.getLastRow();
  // 无有效数据行直接返回
  if (lastRow <= CONFIG.headerRowCount) return;
  // 读取所有有效行数据
  const dataRange = sheet.getRange(
    CONFIG.headerRowCount + 1, 
    1, 
    lastRow - CONFIG.headerRowCount, 
    8 // 一共8列
  );
  const schedule = dataRange.getValues();

  // 逐行处理，空值不中断循环
  schedule.forEach((row, index) => {
    const rowNum = CONFIG.headerRowCount + 1 + index;
    const title = row[CONFIG.col.title];
    // 无标题行直接跳过
    if (!title) return;

    // ========== 处理学习日历（lesson内容） ==========
    const lessonDate = row[CONFIG.col.lessonDate];
    const lessonChecked = row[CONFIG.col.lessonCheck];
    let learningEventId = row[CONFIG.col.learningEventId];
    const isLessonDateValid = lessonDate instanceof Date && !isNaN(lessonDate.getTime());

    if (lessonChecked === true && isLessonDateValid) {
      let event;
      if (learningEventId) {
        // 已有事件，更新内容
        event = learningCal.getEventById(learningEventId);
        if (event) {
          event.setTitle(title);
          event.setAllDayDate(lessonDate);
        } else {
          // 事件不存在，新建
          event = learningCal.createAllDayEvent(title, lessonDate);
          learningEventId = event.getId();
          sheet.getRange(rowNum, CONFIG.col.learningEventId + 1).setValue(learningEventId);
        }
      } else {
        // 无事件ID，新建事件
        event = learningCal.createAllDayEvent(title, lessonDate);
        learningEventId = event.getId();
        sheet.getRange(rowNum, CONFIG.col.learningEventId + 1).setValue(learningEventId);
      }
    } else {
      // 未勾选或日期无效，删除已有事件，清空ID
      if (learningEventId) {
        const event = learningCal.getEventById(learningEventId);
        if (event) event.deleteEvent();
        sheet.getRange(rowNum, CONFIG.col.learningEventId + 1).clearContent();
      }
    }

    // ========== 处理ITK日历（article内容） ==========
    const articleDate = row[CONFIG.col.articleDate];
    const articleChecked = row[CONFIG.col.articleCheck];
    let itkEventId = row[CONFIG.col.itkEventId];
    const isArticleDateValid = articleDate instanceof Date && !isNaN(articleDate.getTime());

    if (articleChecked === true && isArticleDateValid) {
      let event;
      if (itkEventId) {
        // 已有事件，更新内容
        event = itkCal.getEventById(itkEventId);
        if (event) {
          event.setTitle(title);
          event.setAllDayDate(articleDate);
        } else {
          // 事件不存在，新建
          event = itkCal.createAllDayEvent(title, articleDate);
          itkEventId = event.getId();
          sheet.getRange(rowNum, CONFIG.col.itkEventId + 1).setValue(itkEventId);
        }
      } else {
        // 无事件ID，新建事件
        event = itkCal.createAllDayEvent(title, articleDate);
        itkEventId = event.getId();
        sheet.getRange(rowNum, CONFIG.col.itkEventId + 1).setValue(itkEventId);
      }
    } else {
      // 未勾选或日期无效，删除已有事件，清空ID
      if (itkEventId) {
        const event = itkCal.getEventById(itkEventId);
        if (event) event.deleteEvent();
        sheet.getRange(rowNum, CONFIG.col.itkEventId + 1).clearContent();
      }
    }
  })
}

部署配置说明 #

• 替换代码中CONFIG部分的两个日历ID为实际的日历ID
• 首次使用点击表格顶部「日历同步工具」-「立即同步所有工作表日程」完成首次全量同步
• 若需要编辑自动同步，在Apps Script编辑器左侧点击「触发器」，添加新触发器：选择onEdit函数，触发源选「从电子表格中」，事件类型选「编辑时」，保存授权即可
• 无日期的行不需要填充占位内容，代码会自动跳过，后续补填日期并勾选对应类型后会自动创建事件；取消勾选或删除日期时会自动删除对应日历事件，不会产生冗余数据

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