Google Apps Script create calendar event from shee

2019-08-18 08:06发布

站内问答 / 前沿技术
1165 3 5

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"}

Line 204 corresponds to the command:

 Calendar.Events.insert(event, CAL, {sendNotifications: true, supportsAttachments:true});

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.

Here is the psuedocode for the function

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*/}
    }  
  }

Thank you very much!

Update 12/29/2017:

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.

The error is, as shown below:

<!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>

Or, after parsing that through an html editor:

enter image description here

What does that even mean? I have the advanced service enabled, and the script is enabled to run from anyone. Any ideas?

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.'); 
  }
}

3条回答
放我归山
2楼-- · 2019-08-18 08:50

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.

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.

查看更多
0人赞 添加讨论(0) 举报
爷、活的狠高调
3楼-- · 2019-08-18 08:59

This is a delayed response, but thanks to all who recommended using the POST method. It turns out the proper way to do this is to use URLFetchApp and pass the Script's project Key to authorize the calendar access (I believe you only need to make sure the person executing the script has rights to edit the actual calendar).

Here is basically how to do it in a functional way:

     //GCALENDAR is th e unique ID of the project int it's URL when the script is open for editing
     
     //PROJECTKEY is the unique ID of the project, found in the Project Properties Menu under FILE.
        
        
        //CREATE CALENDAR IN GOOGLE CALENDAR OF CONST CAL
          var data = {
            'event': JSON.stringify(event),
            'cal': CAL,
            'key': PROJECTKEY
          };
          
          var options = {
            'method' : 'post',
            'contentType': 'application/json',
            'payload' : data,
            'muteHttpExceptions': true
          };
          
          
          var answer = UrlFetchApp.fetch("https://script.google.com/macros/s/" + GCALENDAR + "/exec", options).getContentText();
          Logger.log(answer);

查看更多
0人赞 添加讨论(0) 举报
闹够了就滚
4楼-- · 2019-08-18 09:00

Your script is using Advanced Google Services, specifically Calendar. Read the section "Enabling advanced services"; everyone will have to follow those steps to use the script.

Alternatively (in my opinion, this is a better solution), rewrite the script so that it uses the standard CalendarApp service. It also allows you to create an event and then you can add various reminders to that event.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(5)