CODEWITHSUNDEEP
google-apps-scriptgoogle-calendar-api
D Clark
D Clark

Reputation: 5

Trying to add Guests in Spreadsheet Script to Calendar App

I am trying to add Guests in "Options" for automatically add a schedule from Google Sheets into Calendar. I have watched videos (which don't discuss this and lead to no answers when others ask this question) and don't know enough to find the CalendarApp info helpful. Can someone help? (FYI, I also want to stop duplicating events every time this is run) This is my Script:

function addEvents(){
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var lr = ss.getLastRow();
    var cal = CalendarApp.getCalendarById("[email protected]");

    var events = cal.getEvents(new Date ("02/8/2019 12:00 AM"), new Date("02/28/2019 11:59 PM"));

    for (var i=0;i<events.length;i++){
        var ev = events[i];
        ev.deleteEvent();  
        }

    var data = ss.getRange("A2:F"+ lr).getValues();

    for(var i = 0;i<data.length;i++){

        cal.createEvent(data[i][0], data[i][1], data[i][2], guests:"data[i][3]", "data[i][4]", {description:data[i][5]});

    }
}

Upvotes: 0

Views: 87

Answers (2)

Mateo Randwolf
Mateo Randwolf

Reputation: 2930

Your main problem was that you weren't including guests in the same JavaScript object as description and that guests must be an string including comma separated values. The following script has self explanatory comments and also checks whether an event already exists on the date and time your are trying to insert your event to avoid duplicate events:

function addEvents(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("YOURCALENDARID");
  
  var data = ss.getRange("A2:F"+ lr).getValues();
  
  for(var i = 0;i<data.length;i++){
    // According to the documentation if no event was found on those date times it will return null
    // However if you delete an event this will return an empty array so we also have to check against that
    if(cal.getEvents(data[i][1], data[i][2])==null || cal.getEvents(data[i][1], data[i][2]).length==0 ){
      // Options must be a Javascript object and specifically the paramter guests is a string so your data must be integrated
      // in such a string
      cal.createEvent(data[i][0], data[i][1], data[i][2],{guests: ''+data[i][3]+','+data[i][4]+'', description:data[i][5]});
    }
    
  }
}

Reference

Upvotes: 0

Cooper
Cooper

Reputation: 64040

try this:

cal.createEvent(data[i][0], data[i][1], data[i][2], {guests:`${data[i][3]},${data[i][4]}`, description:data[i][5]});

Reference

Upvotes: 1

Related Questions