Google Sheets同步Google Calendar重复生成条目 已有修复方案不适用

• 目标：搭建支持将条目导出至Google Calendar的Google Sheets表格，目前导出功能已实现，但每次运行代码都会在日历中重复创建所有条目
• 卡点：作为开发新手，不清楚如何生成「唯一ID」，无法通过ID校验已有条目的方式解决重复问题
• 备注：代码中已移除日历ID，测试代码/表格时需自行填入个人日历ID

原有代码 #

/**
 * Import events from Google Calendar into Google Sheets
 */

function getEvents() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cal = CalendarApp.getCalendarById("[INSERT CAL ID HERE]");
var events = cal.getEvents(new Date("7/1/2022 12:00 AM"), new Date("7/31/2022 11:59 PM")); //array of events - uses square brackets


var lr = ss.getLastRow(); // clean up spreadsheet - clear content when events are removed from the calendar
ss.getRange(2, 1, lr-1, 5).clearContent(); // first row of data, first column of data, lr-1, number of columns of data 
//Error when blank - must have at least one row of text. Row 2 cannot be blank on first run.


for(var i = 0;i<events.length;i++){ //loop through all of the events to find how many things are in the array and run the function for all of them 

  var name = events[i].getTitle(); //first event i=0 - this will run through entries in the timeframe specified and get all titles
  var st = events[i].getStartTime();
  var et = events[i].getEndTime();
  var loc = events[i].getLocation();
  var des = events[i].getDescription();

  ss.getRange(i+2, 1).setValue(name); //assign the variable to a column/location in the sheet. Change the number on each line to change to assign to a new column
  ss.getRange(i+2, 2).setValue(st);
  ss.getRange(i+2, 2).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM"); //set date formatting for st
  ss.getRange(i+2, 3).setValue(et);
  ss.getRange(i+2, 3).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM"); //set date formatting for et
  ss.getRange(i+2, 4).setValue(loc);
  ss.getRange(i+2, 5).setValue(des);
  
  }

}

/**
 * Export event entries from Google Sheets to the Google Calendar
 */

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
    var cal = CalendarApp.getCalendarById("[INSERT CAL ID HERE]");

  var data = ss.getRange("A2:E" + lr).getValues(); //concatentation

  for(var i = 0;i<data.length;i++){ 
    
    //Logger.log(data[i]); 
    // [i] moves each entry to a new line, [0] selects only the column specified

      cal.createEvent(data[i][0], data[i][1], data[i][2], {location: data[i][3], description: data[i][4]});

  }

}

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item for invoking function addEvents()
 */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Calendar Sync')
    .addItem('Sync now', 'addEvents')
    .addToUi();

}

参考示例截图 #

导出至日历的Google Sheets示例

重复创建的核心原因是每次运行导出函数都会无差别调用createEvent新建事件，没有做已存在事件校验。不需要自己生成唯一ID，Google Calendar创建事件时会自动返回全局唯一的eventId，只需要在表格新增一列存储这个ID，同步时先判断ID是否存在即可实现去重：

• 行内已有有效ID：直接更新对应日历事件内容，不新建
• 行内无ID/ID对应事件已被删除：新建事件，拿到返回的ID后写回表格对应列

前置操作 #

在表格F1单元格填入列名事件ID，预留出存储ID的列即可。

修改后可直接使用的代码 #

/**
 * 从Google Calendar导入事件到Google Sheets
 */
function getEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cal = CalendarApp.getCalendarById("[INSERT CAL ID HERE]");
  var events = cal.getEvents(new Date("7/1/2022 12:00 AM"), new Date("7/31/2022 11:59 PM"));

  var lr = ss.getLastRow();
  // 清理范围覆盖到F列的事件ID
  ss.getRange(2, 1, lr-1, 6).clearContent();

  for(var i = 0;i<events.length;i++){
    var eventId = events[i].getId();
    var name = events[i].getTitle();
    var st = events[i].getStartTime();
    var et = events[i].getEndTime();
    var loc = events[i].getLocation();
    var des = events[i].getDescription();

    ss.getRange(i+2, 1).setValue(name);
    ss.getRange(i+2, 2).setValue(st).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM");
    ss.getRange(i+2, 3).setValue(et).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM");
    ss.getRange(i+2, 4).setValue(loc);
    ss.getRange(i+2, 5).setValue(des);
    // 写入事件ID到F列
    ss.getRange(i+2, 6).setValue(eventId);
  }
}

/**
 * 从Google Sheets同步事件到Google Calendar（去重版）
 */
function syncEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("[INSERT CAL ID HERE]");
  // 读取范围覆盖到F列的事件ID
  var data = ss.getRange("A2:F" + lr).getValues();

  for(var i = 0;i<data.length;i++){
    var row = data[i];
    var title = row[0];
    var startTime = row[1];
    var endTime = row[2];
    var location = row[3];
    var description = row[4];
    var eventId = row[5];

    // 跳过标题、时间为空的无效行
    if(!title || !startTime || !endTime) continue;

    if(eventId){
      try {
        // 已有ID，直接更新现有事件
        var existingEvent = cal.getEventById(eventId);
        existingEvent.setTitle(title);
        existingEvent.setTime(startTime, endTime);
        existingEvent.setLocation(location);
        existingEvent.setDescription(description);
      } catch (e) {
        // ID对应事件已被日历端删除时，重新创建并更新ID
        var newEvent = cal.createEvent(title, startTime, endTime, {location: location, description: description});
        ss.getRange(i+2, 6).setValue(newEvent.getId());
      }
    } else {
      // 无有效ID，新建事件并回写ID到表格
      var newEvent = cal.createEvent(title, startTime, endTime, {location: location, description: description});
      ss.getRange(i+2, 6).setValue(newEvent.getId());
    }
  }
}

/**
 * 表格自定义菜单
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Calendar Sync')
    .addItem('立即同步到日历', 'syncEvents')
    .addItem('从日历导入事件', 'getEvents')
    .addToUi();
}

关键改动点 #

• 新增F列存储Calendar返回的原生事件ID，无需自行生成唯一标识
• 替换原有无差别新建的导出逻辑，优先根据ID更新事件，仅无有效ID时才新建
• 增加异常捕获，处理ID对应事件被手动删除的场景，自动重建并更新ID
• 优化Range调用逻辑，减少不必要的表格读写操作
• 增加空行校验，跳过内容不全的无效行

内容的提问来源于stack exchange，提问作者Tanya Vander Vecht