如何将Google Calendar多活动的多个邮箱导出到Google Sheet单单元格

问题说明 #

需要将指定Google Calendar内多个活动的嘉宾邮箱（包含活动秘书邮箱）导出到Google Sheet，用于每两周向全州成员发布。现有脚本新增「Guest List」列后，单元格仅显示Event Guest，无法提取实际邮箱地址，需整合代码实现将多个嘉宾邮箱导出到单个单元格。

修改后的完整脚本 #

function export_gcal_to_gsheet(){
// Reference Websites:
// https://developers.google.com/apps-script/reference/calendar/calendar
// https://developers.google.com/apps-script/reference/calendar/calendar-event
var mycal = "**redacted**";
var cal = CalendarApp.getCalendarById(mycal);
var events = cal.getEvents(new Date("April 24, 2023 00:00:00 CST"), new Date("May 31, 2023 23:59:59 CST"));
var sheet = SpreadsheetApp.getActiveSheet();
sheet.clearContents();
var header = [["Event Start", "Event Title", "Event Location", "Event Description", "Guest List"]]
var range = sheet.getRange(1,1,1,5);
range.setValues(header);

// 遍历所有日历事件，从第2行开始写入数据
for (var i=0;i<events.length;i++) {
  var row=i+2;
  // 提取嘉宾邮箱并处理为字符串
  var guests = events[i].getGuestList();
  var guestEmails = []; // 初始化空数组，避免push操作报错
  for (var j = 0; j < guests.length; j++){
    guestEmails.push(guests[j].getEmail());
  }
  // 将邮箱数组转为逗号分隔的字符串，无嘉宾时显示提示文本
  var guestListStr = guestEmails.length > 0 ? guestEmails.join(", ") : "无嘉宾";
  
  var details=[[events[i].getStartTime(), events[i].getTitle(), events[i].getLocation(), events[i].getDescription(), guestListStr]];
  var range=sheet.getRange(row,1,1,5);
  range.setValues(details);
}
}

function onOpen() {
  Browser.msgBox('App Instructions - Please Read This Message', '1) Click Tools then Script Editor\n2) Read/update the code with your desired values.\n3) Then when ready click Run export_gcal_to_gsheet from the script editor.', Browser.Buttons.OK);
}

关键修改说明 #

• 初始化数组：新增var guestEmails = [];，解决原代码中未定义变量直接push的报错问题
• 提取邮箱地址：循环遍历活动嘉宾列表，调用getEmail()方法提取每个嘉宾的邮箱并存入数组
• 格式转换：用join(", ")将邮箱数组转为逗号分隔的字符串，确保单个单元格内清晰展示多个邮箱；无嘉宾时显示"无嘉宾"，避免单元格为空
• 替换原内容：将原代码中events[i].getGuestList()替换为处理后的guestListStr，确保单元格显示实际邮箱地址而非默认的Event Guest文本

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