CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Steve Haddley
Steve Haddley

Reputation: 37

Google sheets to calendar with invite and edit events

Scenario: I want several users to add titles dates, descriptions, etc to a calendar for discussion events they are holding via google meet. This should be all handled by a trigger on a google sheet. If an event is edited within the google sheet then the event in the calendar should also be edited. I found some code, slightly edited it and it somewhat works.

What Works: events are created and guests are invited and therefore the google meet is created (as long as 1 guest is invited). editing an event also works

What does not work: if the time for 2 events clashes, for example, event one runs from 14:00 - 15:00 and event two runs from 14:30 - 15:30 then only event two is added to the calendar.

Can anyone help with how to enable adding overlapping events?

function create_calendar() {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var index = 2;
var lastRow = sheet.getLastRow();

for (;index <= lastRow; index++){
  
  var taskTitle = sheet.getRange(index, 1, 1, 1).getValue();
  var taskDesc = sheet.getRange(index, 2, 1, 1).getValue();
  var location = sheet.getRange(index, 3, 1, 1).getValue();
  var startDate = sheet.getRange(index, 4, 1, 1).getValue();
  var endDate = sheet.getRange(index, 5, 1, 1).getValue();
  var guests = sheet.getRange(index, 6, 1, 1).getValue();
  
  var type = sheet.getRange(index, 7, 1, 1).getValue();
  var onCalendar = sheet.getRange(index, 8, 1, 1).getValue();
  var status = sheet.getRange(index, 9, 1, 1).getValue();
  
  var sendInvites = true;
  if (onCalendar == 'Yes' && startDate && endDate && status != 'Done')
  {
    var events =  CalendarApp.getCalendarById("INSERT CALENDAR ID HERE").getEvents(startDate, endDate);
    delete_events(events);
    var calendar = CalendarApp.getCalendarById("INSERT CALENDAR ID HERE").createEvent(taskTitle, 
                                                                                      startDate, 
                                                                                      endDate,
                                                                                      {description: taskDesc, 
                                                                                        location: location, 
                                                                                        guests: guests, 
                                                                                        sendInvites: sendInvites}
                                                                                      );

  }
    
}// End of for Loop
  
}// End of CalendarTest Function

Upvotes: 1

Views: 157

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

const ss=SpreadsheetApp.getActive();
const sheet=ss.getSheetByName('Events');
const startRow=1;
let vA=sheet.getRange(startRow,1,sheet.getLastRow()-startRow+1,9).getValues();
vA.forEach(function(r,i){
  var taskTitle = r[0];
  var taskDesc =r[1]; 
  var location =r[2];
  var startDate =r[3];
  var endDate =r[4];
  var guests =r[5];
  var type =r[6];
  var onCalendar =r[7];
  var status =r[8];
  var sendInvites = true;
  if (onCalendar=='Yes' && startDate && endDate && status != 'Done')
  {
    CalendarApp.getCalendarById("INSERT CALENDAR ID HERE").createEvent(taskTitle, startDate, endDate,{description:taskDesc,location:location,guests:guests,sendInvites:sendInvites});
  }
});

Reference:

Upvotes: 2

Related Questions