Google Apps Script无法为其他表单提交者创建日历事件求助

问题描述 #

我用Google Apps Script实现了表单提交后自动创建日历事件的功能：自己提交表单时，事件能正常同步到提交者日历和对应中心日历；但其他用户提交时，无法创建日历事件。提交者邮箱就是日历ID，且已确认权限设置，触发器显示执行完成，但执行日志提示“User's calendar not found.”。

原代码 #

function createCalendarEvent(e) {
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();

  // Check the response for the question "What are you doing today?"
  var activityQuestion = "What are you doing today?";
  var activityResponse = getFormResponseByTitle(itemResponses, activityQuestion);

  if (activityResponse === "Reviewing actions") {
    // If the response is "Reviewing actions", do not create a calendar event
    return;
  }

  var form = FormApp.getActiveForm();
  var formTitle = form.getTitle();

  // Adjust the following values to match the question titles in your form
  var dateQuestion = "Date actions will be reviewed";
  var startTimeQuestion = "Review Start Time";
  var endTimeQuestion = "Review End Time";
  var centreNameQuestion = "Centre Name";
  var centreNumberQuestion = "Centre Number";

  var eventTitle = "AOM Productive Visit actions review - ";
  var eventDescription = "";

  var date = "";
  var startTime = "";
  var endTime = "";
  var centreName = "";
  var centreNumber = "";

  // Retrieve the date, start time, end time, centre name, and centre number from the form responses
  date = getFormResponseByTitle(itemResponses, dateQuestion);
  startTime = getFormResponseByTitle(itemResponses, startTimeQuestion);
  endTime = getFormResponseByTitle(itemResponses, endTimeQuestion);
  centreName = getFormResponseByTitle(itemResponses, centreNameQuestion);
  centreNumber = getFormResponseByTitle(itemResponses, centreNumberQuestion);

  // Merge the date, start time, and end time into datetime values
  var startDateTimeString = date + " " + startTime;
  var endDateTimeString = date + " " + endTime;
  var startDateTime = new Date(startDateTimeString);
  var endDateTime = new Date(endDateTimeString);

  // Create the calendar event
  var eventTitleWithCentre = eventTitle + centreName;

  // Get the form submitter's email from the form response
  var userEmail = e.response.getRespondentEmail();

  // Get the user's calendar
  var calendars = CalendarApp.getOwnedCalendarsByName(userEmail);
  var userCalendar = (calendars.length > 0) ? calendars[0] : null;

  if (!userCalendar) {
    Logger.log("User's calendar not found.");
    return;
  }

  // Add the link to the submitted form in the event description
  eventDescription +=
    "**Please click on the following link to review the actions from your centre visit -**\n" +
    "https://drive.google.com/drive/folders/15xJKC5_HAC6sDoZhhOV7fFK5knQ-mKBH?usp=drive_link" + "\n\n";

  // Create the calendar event with the updated event description on the user's calendar
  var event = userCalendar.createEvent(eventTitleWithCentre, startDateTime, endDateTime, { description: eventDescription });

  // Invite the center to the event
  var centreEmail = centreNumber + "mgr01@xxxxxxxxxx.com";
  event.addGuest(centreEmail);
}

// Function to get the form response by question title
function getFormResponseByTitle(itemResponses, questionTitle) {
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    var title = itemResponse.getItem().getTitle();
    if (title === questionTitle) {
      return itemResponse.getResponse();
    }
  }
  return null;
}

问题根源 #

原代码使用CalendarApp.getOwnedCalendarsByName(userEmail)获取提交者日历，但该方法仅能获取当前脚本运行账号（表单所有者）拥有的日历。其他用户的个人日历不属于脚本运行账号的“自有日历”，因此无法通过此方法找到，导致日志提示“User's calendar not found.”。

修复方案 #

方案一：通过日历ID直接访问（推荐，适合团队内部场景） #

利用用户邮箱作为日历默认ID的特性，改用CalendarApp.getCalendarById(userEmail)访问提交者日历，前提是提交者已将自己的日历共享给脚本运行账号（表单所有者）。

修改后的代码 #

function createCalendarEvent(e) {
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();

  // 跳过"Reviewing actions"类型的事件创建
  var activityQuestion = "What are you doing today?";
  var activityResponse = getFormResponseByTitle(itemResponses, activityQuestion);
  if (activityResponse === "Reviewing actions") {
    return;
  }

  // 提取表单响应数据
  var dateQuestion = "Date actions will be reviewed";
  var startTimeQuestion = "Review Start Time";
  var endTimeQuestion = "Review End Time";
  var centreNameQuestion = "Centre Name";
  var centreNumberQuestion = "Centre Number";

  var date = getFormResponseByTitle(itemResponses, dateQuestion);
  var startTime = getFormResponseByTitle(itemResponses, startTimeQuestion);
  var endTime = getFormResponseByTitle(itemResponses, endTimeQuestion);
  var centreName = getFormResponseByTitle(itemResponses, centreNameQuestion);
  var centreNumber = getFormResponseByTitle(itemResponses, centreNumberQuestion);

  // 转换为日期时间对象
  var startDateTime = new Date(date + " " + startTime);
  var endDateTime = new Date(date + " " + endTime);
  var eventTitleWithCentre = "AOM Productive Visit actions review - " + centreName;

  // 获取提交者邮箱
  var userEmail = e.response.getRespondentEmail();
  if (!userEmail) {
    Logger.log("无法获取提交者邮箱，请确保表单已开启收集邮箱功能");
    return;
  }

  // 修复：通过日历ID（提交者邮箱）直接访问日历
  var userCalendar;
  try {
    userCalendar = CalendarApp.getCalendarById(userEmail);
  } catch (err) {
    Logger.log("访问提交者日历失败：" + err.message);
    Logger.log("请确认提交者已将日历共享给脚本运行账号（表单所有者邮箱）");
    return;
  }

  if (!userCalendar) {
    Logger.log("未找到提交者对应的日历");
    return;
  }

  // 构建事件描述
  var eventDescription = "**请点击以下链接查看中心访问的行动项 -**\n" +
    "https://drive.google.com/drive/folders/15xJKC5_HAC6sDoZhhOV7fFK5knQ-mKBH?usp=drive_link\n\n";

  // 创建日历事件并添加中心嘉宾
  var event = userCalendar.createEvent(eventTitleWithCentre, startDateTime, endDateTime, { description: eventDescription });
  var centreEmail = centreNumber + "mgr01@xxxxxxxxxx.com";
  event.addGuest(centreEmail);
}

// 根据问题标题获取表单响应
function getFormResponseByTitle(itemResponses, questionTitle) {
  for (var i = 0; i < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    if (itemResponse.getItem().getTitle() === questionTitle) {
      return itemResponse.getResponse();
    }
  }
  return null;
}

注意事项 #

• 确保表单已开启收集提交者邮箱功能：在表单设置中勾选“要求登录以查看此表单”和“收集电子邮件地址”。
• 要求所有提交者将个人日历共享给表单所有者：共享权限至少设置为“更改和管理事件”，确保脚本能创建事件。

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