Question

0

299

Views

onEdit() trigger to delete edited cells eventId and then create new one

I have the following script that when called will create events in a google calendar based on inputs from google sheets. The function 'onOpen()' and 'scheduleShifts()' functions work as expected, if you have any improvements I'd be more than happy to implement them.

function onOpen(){
  
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu("Sync to Calendar")
  menu.addItem("Schedule Shifts Now", "scheduleShifts")
  menu.addToUi();
}

function scheduleShifts(){

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("C4").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var signups = spreadsheet.getRange("A8:D12").getValues();

  for (let x = 0; x < signups.length; x++) {
    var shift = signups[x];
    var startTime = shift[0];
    var endTime = shift[1];
    var volunteer = shift[2];
    var eventId = shift[3];
    
    if (eventId == ""){
    var newEvent = eventCal.createEvent(volunteer, startTime, endTime);
    var newEventId = newEvent.getId();
    spreadsheet.getRange(8 + x, 4).setValue(newEventId); 
  }
  }
}

The issue I have is based on the onEdit() trigger, I want the trigger to delete the eventId that corresponds to the edited data, in this case, it is simply the title of the event, and then create a new event based on the edited data. However, when I edit data from the cell array nothing is changed in the google calendar. Any help for this issue would be appreciated.

function onEdit(e){

  var editedData = e.source.getActiveSheet().getRange(8, 3, 1, 5).getValues()[2];
  var event = editedData[3]
   try{
//      print(event);
      event.deleteEventSeries();
      shift[3] = '';
    }
    catch(e){
    }
  }

For clarity on google sheets the columns are: Start Time/ End Time / Volunteer / EventID. with the data being from cell range A8:D12

about 4 years ago · Juan Pablo Isaza

1 answers

Answer question

0

Answer question

Find remote jobs

Accepted Answer · 2022-07-09T09:53:55.197Z

Main issues:

You are using a simple trigger, which cannot be used to access services that require authorization (see Simple Triggers > Restrictions).
The onEdit function is not checking whether the edited cell is in the right range.
The onEdit function is not retrieving the calendar event, just an undefined value, because the array e.source.getActiveSheet().getRange(8, 3, 1, 5).getValues() has only one element.

Solution:

Install your onEdit trigger. You can do that manually, following these steps, or programmatically, by executing installTrigger once (see the code sample below).
Use the event object to check which cell was edited before deleting the corresponding event.
Call CalendarApp.getEventById(iCalId) to get the calendar event you want to retrieve.

Code sample:

function installTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("onEditTrigger")
           .forSpreadsheet(ss)
           .onEdit()
           .create();
}

function onEditTrigger(e) {
  var range = e.range;
  var FIRST_ROW = 8; // First row to check
  var LAST_ROW = 12; // Last row to check
  var TITLE_COL = 3; // Column index where to check changes
  var ID_COL = 5; // Column index with event id
  var colIndex = range.getColumn();
  var rowIndex = range.getRow();
  if (colIndex === TITLE_COL && rowIndex >= FIRST_ROW && rowIndex <= LAST_ROW) {
    var sheet = range.getSheet();
    var eventIdRange = sheet.getRange(rowIndex, ID_COL);
    var eventId = eventIdRange.getValue();
    var event = CalendarApp.getEventById(eventId);
    event.deleteEvent();
    eventIdRange.setValue("");
  }
}

Other issues:

In the spreadsheet you provided, the eventId's are located in column E, but according to scheduleShifts, they are in column D (corresponding to Description in your spreadsheet). I'd suggest you to modify your function accordingly:

function scheduleShifts(){

  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var calendarId = spreadsheet.getRange("C4").getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var signups = spreadsheet.getRange("A8:E12").getValues();

  for (let x = 0; x < signups.length; x++) {
    var shift = signups[x];
    var startTime = shift[0];
    var endTime = shift[1];
    var volunteer = shift[2];
    var description = shift[3];
    var eventId = shift[4];
    
    if (eventId == ""){
      var newEvent = eventCal.createEvent(volunteer, startTime, endTime);
      var newEventId = newEvent.getId();
      spreadsheet.getRange(8 + x, 5).setValue(newEventId); 
    }
  }
}

0

299