Google Apps脚本禁止从工作表创建日历事件吗？ [英] Google Apps Script create calendar event from sheet forbidden?
问题描述
在我们公司其中一个电子表格中文档绑定的Google Appscript中，我创建了一个脚本，该脚本可将电子表格行转换为Google日历约会。该功能对我来说很好，但对我的同事却不行，即使我们俩都有编辑日历和更改共享权限的权限，我的同事也证明了他可以从calendar.google.com在日历上创建约会。
In a document-bound Google Appscript in one of our company spreadsheets, I've created a script that turns spreadsheet lines into Google calendar appointments. The function works fine for me, but not for my coworker, even though we both have permissions to edit the calendar and change sharing permissions, and my coworker proved he can create appointments on the calendar from calendar.google.com.
他在运行脚本时收到以下错误消息：
He gets the following error message when he runs the script:
{"message":"Forbidden","name":"GoogleJsonResponseException","fileName":"SCHEDULER","lineNumber":204,"stack":"\tat SCHEDULER:204 (createAppointments)\n"}
第204行对应于命令：
Line 204 corresponds to the command:
Calendar.Events.insert(event, CAL, {sendNotifications: true, supportsAttachments:true});
如果他对日历具有编辑权限，为什么要禁止这样做？ Google Apps脚本中的日历服务是否存在问题？而且，我将CAL变量更改为我个人创建的日历，并以相同的权限共享给他。他可以很好地编辑日历。
If he has edit rights to the calendar, why is this forbidden? Is there a problem with the Calendar service in Google Apps Script? What is more, I changed the CAL variable to a calendar I personally created and shared out to him with the same permissions. He can edit that calendar just fine.
这是该函数的伪代码
function createAppointments() {
var CAL = 'companyname.com_1v033gttnxe2r3eakd8t9sduqg@group.calendar.google.com';
for(/*each row in spreadsheet*/)
{
if(/*needs appointment*/)
{
var object = {/*...STUFF...*/};
var coworker = 'coworker@companyname.com';
var timeArgs = {start: /*UTC Formatted time*/, end: /*UTC Formatted time*/}
if(/*All the data checks out*/{
var summary = 'Name of appointment'
var notes = 'Stuff to put in the body of the calendar appointment';
var location = '123 Happy Trail, Monterrey, TX 12345'
//BUILD GOOGLE CALENDAR OBJECT
var event = {
"summary": summary,
"description": notes,
"start": {
"dateTime": timeArgs.start,
"timeZone": TZ
},
"end": {
"dateTime": timeArgs.end,
"timeZone": TZ
},
"guestsCanInviteOthers": true,
"reminders": {
"useDefault": true
},
"location": location
//,"attendees": []
};
event.attendees = [{coworker@companyname.com, displayName: 'coworker name'}];
//CREATE CALENDAR IN GOOGLE CALENDAR OF CONST CAL
Calendar.Events.insert(event, CAL, {sendNotifications: true, supportsAttachments:true});
} else{/*Tell user to fix data*/}
}
}
非常感谢您！
更新12/29/2017：
根据Jason Allshorn和Crazy Ivan，我尝试过调整应用程序。到目前为止，谢谢您的帮助！有趣的是，我同时使用Advanced Calendar Service和CalendarApp遇到了相同的响应。
I've Tried adjusting the app according to Jason Allshorn and Crazy Ivan. Thank you for your help, so far! Interestingly, I have run into the same response using both the Advanced Calendar Service and the CalendarApp.
错误如下所示：
<!DOCTYPE html><html><head><link rel="shortcut icon" href="//ssl.gstatic.com/docs/script/images/favicon.ico"><title>Error</title><style type="text/css">body {background-color: #fff; margin: 0; padding: 0;}.errorMessage {font-family: Arial,sans-serif; font-size: 12pt; font-weight: bold; line-height: 150%; padding-top: 25px;}</style></head><body style="margin:20px"><div><img alt="Google Apps Script" src="//ssl.gstatic.com/docs/script/images/logo.png"></div><div style="text-align:center;font-family:monospace;margin:50px auto 0;max-width:600px">Object does not allow properties to be added or changed.</div></body></html>
>
或者在通过html编辑器解析后：
Or, after parsing that through an html editor:
那是什么意思？我启用了高级服务，并且脚本可以从任何人运行。有任何想法吗？
What does that even mean? I have the advanced service enabled, and the script is enabled to run from anyone. Any ideas?
在测试了尝试运行calendarApp / Advanced Calendar事件创建命令后返回的错误后，我已经确认。
I have confirmed after testing what the error comes back after trying to run the calendarApp/Advanced Calendar event creation command.
这是我的代码，导致我无法做到这一点：
Here is my code that caused me to get this far:
function convertURItoObject(url){
url = url.replace(/\+/g,' ')
url = decodeURIComponent(url)
var parts = url.split("&");
var paramsObj = {};
parts.forEach(function(item){
var keyAndValue = item.split("=");
paramsObj[keyAndValue[0]] = keyAndValue[1]
})
return paramsObj; // here's your object
}
function doPost(e) {
var data = e.postData.contents;
data = convertURItoObject(data);
var CAL = data.cal;
var event = JSON.parse(data.event);
var key = data.key;
var start = new Date(event.start.dateTime);
if(ACCEPTEDPROJECTS.indexOf(key) > -1)
{
try{
var calendar = CalendarApp.getCalendarById(CAL);
calendar.createEvent(event.summary, new Date(event.start.dateTime), new Date(event.end.dateTime), {description: event.description, location: event.location, guests: event.guests, sendInvites: true});}
/*try {Calendar.Events.insert(event, CAL, {sendNotifications: true, supportsAttachments:true});} Same error when I use this command*/
catch(fail){return ContentService.createTextOutput(JSON.stringify(fail));}
e.postData.result = 'pass';
return ContentService.createTextOutput(JSON.stringify(e));
}
else {
return ContentService.createTextOutput('Execution not authorized from this source. See CONFIG of target project for details.');
}
}
推荐答案
A我这边的解决方案是将日历事件创建功能从您的电子表格绑定脚本中抽象到一个单独的独立应用脚本，该脚本在您的许可下以您的名字运行。
A solution from my side would be to abstract the calendar event creation function away from your Spreadsheet bound script to a separate standalone apps-script that runs under your name with your permissions.
然后从工作表绑定脚本调用包含PUT请求的独立脚本，该请求包含更新日历的信息。这样，使用工作表插件的任何人都可以更新calander，而不会造成权限混乱。
Then from your sheet bound script call to the standalone script with a PUT request containing the information needed to update the Calender. This way anyone using your sheet addon can update the calander without any mess with permissions.
工作表绑定脚本可能看起来像这样：
The sheet bound script could look something like this:
function updateCalander(){
var data = {
'event': EVENT,
};
var options = {
'method' : 'post',
'contentType': 'application/json',
'payload' : data
};
var secondScriptID = 'STANDALONE_SCRIPT_ID'
var response = UrlFetchApp.fetch("https://script.google.com/macros/s/" + secondScriptID + "/exec", options);
Logger.log(response) // Expected to see sent data sent back
然后独立脚本如下所示：
Then your standalone script would look something like this:
function convertURItoObject(url){
url = url.replace(/\+/g,' ')
url = decodeURIComponent(url)
var parts = url.split("&");
var paramsObj = {};
parts.forEach(function(item){
var keyAndValue = item.split("=");
paramsObj[keyAndValue[0]] = keyAndValue[1]
})
return paramsObj; // here's your object
}
function doPost(e) {
var CAL = 'companyname.com_1v033gttnxe2r3eakd8t9sduqg@group.calendar.google.com';
var data = e.postData.contents;
data = convertURItoObject(data)
var event = data.event;
try {
Calendar.Events.insert(event, CAL, {sendNotifications: true, supportsAttachments:true});
}
catch(e){
Logger.log(e)
}
return ContentService.createTextOutput(JSON.stringify(e));
}
请注意，需要将独立脚本设置为任何人都可以访问，并且在更新代码时，请确保重新发布代码。如果您不重新发布对独立脚本的调用，则不会使用最新代码。
Please note, the standalone script needs to be set to anyone can access, and when you make updates to the code be sure to re-publish the code. If you don't re-publish your calls to the standalone script are not made to the latest code.
这篇关于Google Apps脚本禁止从工作表创建日历事件吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
