表单提交触发Google Apps Script创建日历事件报错求助

问题现象 #

• 手动执行createCalendarEvent脚本完全正常，但通过**表单提交触发（on form submit trigger）**自动执行时，抛出错误：Exception: Event start time must be before event end time
• 新增setUpTrigger函数在脚本内创建触发后，仅成功执行一次，随后抛出错误：Exception: Action not allowed

提供的脚本 #

原始脚本（手动运行正常） #

function createCalendarEvent() {
var sheet = SpreadsheetApp.getActiveSheet();
var calendar = 
CalendarApp.getCalendarById("c_71lju3i096qeqsg69bd7togo9g@group.calendar.google.com");

var startRow = 2;  // First row of data to process - 2 exempts my header row
var numRows = sheet.getLastRow();   // Number of rows to process
var numColumns = sheet.getLastColumn();

var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
var data = dataRange.getValues();

var complete = "Done";

for (var i = 0; i < data.length; ++i) {
var row = data[i];
var startTime = new Date(row[0]);  //start Time
var endTime = new Date(row[1]); //end Time
var title = row[2]; //Item Title
var option = row[3]; //event option
var location = row[4]; //location
var eventID = row[6]; //event marked Done


if (eventID != complete) {
  var currentCell = sheet.getRange(startRow + i, numColumns);
  calendar.createEvent(title, startTime, endTime, {description:  option + '\r' , location: 
location});

  currentCell.setValue(complete);
}
}
}

新增触发创建逻辑后的脚本 #

function setUpTrigger() {
ScriptApp.newTrigger('createCalendarEvent')
.forForm('1FAIpQLScQvxc8EUSjGvrrVc6-QE3LwNZOytetIrfOVX-RKrJwS8_ALw')
.onFormSubmit()
.create();
}

function createCalendarEvent() {
var sheet = SpreadsheetApp.getActiveSheet();
var calendar = CalendarApp.getCalendarById("c_71lju3i096qeqsg69bd7togo9g@group.calendar.google.com");

var startRow = 2;  // First row of data to process - 2 exempts my 
header row
var numRows = sheet.getLastRow();   // Number of rows to process
var numColumns = sheet.getLastColumn();

var dataRange = sheet.getRange(startRow, 1, numRows-1, numColumns);
var data = dataRange.getValues();

var complete = "Done";

for (var i = 0; i < data.length; ++i) {
var row = data[i];
var startTime = new Date(row[0]);  //start Time
var endTime = new Date(row[1]); //end Time
var title = row[2]; //Item Title
var option = row[3]; //event option
var location = row[4]; //location
var eventID = row[6]; //event marked Done


if (eventID != complete) {
  var currentCell = sheet.getRange(startRow + i, numColumns);
  calendar.createEvent(title, startTime, endTime, {description:  
option + '\r' , location: location});

  currentCell.setValue(complete);
}
}
}

问题分析与修复建议 #

针对「Event start time must be before event end time」错误 #

1. 时区解析偏差：手动运行时脚本使用本地时区，表单触发默认采用UTC时区，导致时间解析出错。需强制指定时区转换（示例为上海时区，可按需修改）：

   // 替换原时间解析代码
   var startTime = new Date(Utilities.formatDate(row[0], "Asia/Shanghai", "yyyy-MM-dd HH:mm:ss"));
   var endTime = new Date(Utilities.formatDate(row[1], "Asia/Shanghai", "yyyy-MM-dd HH:mm:ss"));
   

2. 无效时间校验：表单可能提交空值或格式错误的时间，需增加过滤逻辑：

   // 在创建事件前添加校验
   if (isNaN(startTime.getTime()) || isNaN(endTime.getTime()) || startTime >= endTime) {
     continue; // 跳过无效时间行
   }
   

针对「Action not allowed」错误 #

1. 手动创建触发替代脚本创建：脚本自动创建触发可能存在权限限制，建议直接在Google Apps脚本编辑器的「编辑」→「当前项目的触发器」中手动配置表单提交触发。
2. 清理重复触发：若坚持用脚本创建触发，需先删除现有同名触发避免冲突：

   function setUpTrigger() {
     // 删除现有同名触发
     var triggers = ScriptApp.getProjectTriggers();
     for (var t = 0; t < triggers.length; t++) {
       if (triggers[t].getHandlerFunction() == 'createCalendarEvent') {
         ScriptApp.deleteTrigger(triggers[t]);
       }
     }
     // 创建新触发
     ScriptApp.newTrigger('createCalendarEvent')
     .forForm('1FAIpQLScQvxc8EUSjGvrrVc6-QE3LwNZOytetIrfOVX-RKrJwS8_ALw')
     .onFormSubmit()
     .create();
   }
   

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