Google Apps脚本 - createEvent函数不创建日历事件 [英] Google Apps Script - createEvent function not creating calendar event
问题描述
我们正尝试使用Google表单创建在线表单，以接受约会请求。这是该过程应该是什么样子：
We are trying to create an online form using Google Forms to take requests for appointments. This is what the process should look like:
- 用户填写表单并提交他们的信息
- 系统会将电子邮件传送到我们的Gmail，通知我们有新的传入要求（这样做有效）。
- 我们进入Google试算表。有3张。一张名为请求的工作表。另一个名称为已接受。另一个名为已拒绝（已创建）
- 请求表中的最后一列有一个下拉菜单，其值为已接受或已拒绝。根据我们选择的内容，它会将整行移动到相应的工作表中 - 接受或拒绝。
- 如果行被移动到Accepted，它应该创建一个新的日历事件。这是不工作的部分。该行会正确移动到已接受的工作表中，但不会创建新的Google日历活动。
- User fills form and submits their information (this works)
- An email gets sent to our Gmail to notify us of a new incoming request (this works)
- We go into the Google spreadsheet. There are 3 sheets. One sheet called Requests. Another called Accepted. And another called Rejected (these were created)
- Last column in the Requests sheet has a dropdown menu with values of Accepted or Rejected. Depending on what we select, it will move the entire row into the appropriate sheet -- either Accepted or Rejected. (This works up until step 5 below)
- If the row gets moved into Accepted, it should create a new calendar event. This is the part that doesn't work. The row gets moved properly into the Accepted sheet but doesn't create the new Google calendar event.
奇怪的是，该功能手动，它工作。它需要最后一行并创建一个事件。如果我们尝试通过直接调用函数直接通过我们的代码运行函数，它不会创建一个事件。这是我们的代码为moverow.gs
The weird thing is if we run the function manually, it works. It takes the last row and creates an event. If we try to run the function directly via our code by calling the function directly, it doesn't create an event. This is our code for the moverow.gs
（请注意，所有机密，识别信息已被删除，如日历ID）：
(please note that all confidential, identifying information has been removed such as the calendar id's):
function onEdit(e) {
try { moveRow(e) } catch (error) { Browser.msgBox(error) }
}
function moveRow(e) {
var rowStart = e.range.rowStart;
var colStart = e.range.columnStart;
var calendarStaff1Id = "<link to calendar>";
var calendarStaff2Id = "<link to calendar>"
var calendarStaff3Id = "<link to calendar>"
var calendarStaff4Id = "<link to calendar>"
if ( rowStart == e.range.rowEnd && colStart == e.range.columnEnd ) {
var active = e.source.getActiveSheet();
var name = active.getName();
if ( name == "Requests" && colStart == 10 ) {
var value = e.value;
if ( value == "ACCEPTED (S1)" ) {
var rowValues = active.getRange( rowStart + ':' + rowStart ).getValues();
e.source.getSheetByName("Accepted").appendRow(rowValues[0])
active.deleteRow(rowStart);
var calendarId = calendarStaff1Id;
addEvent();
}
if ( value == "ACCEPTED (S2)" ) {
var rowValues = active.getRange( rowStart + ':' + rowStart ).getValues();
e.source.getSheetByName("Accepted").appendRow(rowValues[0])
active.deleteRow(rowStart);
var id = calendarStaff2Id;
addEvent();
}
if ( value == "ACCEPTED (S3)" ) {
var rowValues = active.getRange( rowStart + ':' + rowStart ).getValues();
e.source.getSheetByName("Accepted").appendRow(rowValues[0])
active.deleteRow(rowStart);
var calendarId = calendarStaff3Id;
addEvent();
}
if ( value == "ACCEPTED (S4)" ) {
var rowValues = active.getRange( rowStart + ':' + rowStart ).getValues();
e.source.getSheetByName("Accepted").appendRow(rowValues[0])
active.deleteRow(rowStart);
var calendarId = calendarStaff4Id;
addEvent(calendarId);
}
if ( value == "REJECTED" ) { // Change to the value that will trigger the move
var rowValues = active.getRange( rowStart + ':' + rowStart ).getValues(); // Entire row
e.source.getSheetByName("Rejected").appendRow(rowValues[0]) // Change to your "move to" sheet name
active.deleteRow(rowStart);
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Rejected").activate();
Browser.msgBox("Please contact client to let them know that their request cannot be fulfilled");
}
}
}
}
function addEvent(calendarId) {
var startDtId = 7;
var titleId = 2;
var titleId2 = 6;
var descId = 8;
var formTimeStampId = 1;
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Accepted").activate();
// Switch to sheet Accepted and start the calendar event creation
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Accepted");
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
var lr = rows.getLastRow();
var startDt = sheet.getRange(lr,startDtId,1,1).getValue();
var subOn = "Added :"+sheet.getRange(lr,formTimeStampId,1,1).getValue()+" by: "+sheet.getRange(lr,titleId,1,1).getValue();
var desc = "Comments :"+sheet.getRange(lr,8,1,1);
var title = sheet.getRange(lr,titleId,1,1).getValue()+" - "+sheet.getRange(lr,titleId2,1,1).getValue();
var start = new Date(startDt);
var end = new Date(startDt.valueOf()+60*60*1000);
var loc = 'Central Library';
var cal = CalendarApp.getCalendarById(calendarId);
//Browser.msgBox("Please add the following event into your calendar: " + title + " Start Time: " + start + " End Time: " + end);
var event = cal.createEvent(title, start, end);
};
请帮助！我们在我们的智慧结束。提前感谢。
Please help!! We are at our wits end. Thanks in advance.
推荐答案
一个简单的 onEdit（）任何需要授权的。无论谁拥有脚本所在的文档，如果是简单触发器，则它是匿名的。
A simple onEdit() trigger cannot do anything that requires authorization. It doesn't matter who owns the document the script is in; if it's a simple trigger, then it's anonymous.
而是使用可安装触发器。 （您可以简单地重命名您的函数，然后参阅手动管理触发器中的说明来设置。）
Instead, use an installable trigger. (You can simply rename your function, then go through the instructions in Managing Triggers Manually to set it up.)
这篇关于Google Apps脚本 - createEvent函数不创建日历事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
