Script to run on delete row

Question

I need to run a script to delete a Google Calendar event on deleting a row from Google Spreadsheets.
Do you have any way of getting the data from the row which was deleted? I have this code to create a Calendar event when a row is changed, but it does not work when it is deleted.

function SaveToCalendar(e) {
    const eventCal = CalendarApp.getOwnedCalendarById(CALENDAR_ID);
    const sheet = e.source.getActiveSheet();
    const row = e.source.getActiveRange().getRow();
    const cell = e.source.getActiveRange();
    const name = sheet.getRange(row, 2, 1, 1).getValue();
    const eventName = sheet.getRange(row, 3, 1, 1).getValue();
    const description = sheet.getRange(row, 4, 1, 1).getValue();
    const startDate = sheet.getRange(row, 5, 1, 1).getValue();
    const endDate = sheet.getRange(row, 6, 1, 1).getValue();
    const department = sheet.getRange(row, 7, 1, 1).getValue();
    const eventTitle = `${eventName} | ${department}`;
    if(cell.getValue() == APROVADO) {    
        eventCal.createEvent(eventTitle, new Date(startDate), new Date(endDate), {
            description: description
        });
    } else {
        var events = eventCal.getEvents(new Date(startDate), new Date(endDate))
        var foundEvent = events?.find(e => e.getTitle() === eventTitle);
        foundEvent?.deleteEvent();
    }
}

I added a trigger to On change event. The mentioned script runs based on the trigger. Look at the trigger configuration on the following image. https://ctrl.vi/i/2aP7wZKkL — Samuel R Ferreira, Oct 25 '22 at 20:58
@Cooper, do you know how to take the data from the excluded row? — Samuel R Ferreira, Oct 25 '22 at 21:10
You can probably determine which one was deleted and get most of the information from the calendar by figuring out which was is in the calendar and not in the spreadsheet — Cooper, Oct 25 '22 at 22:03

Rubén · Answer 1 · 2022-10-25T22:31:33.857

Google Apps Script has simple and installable triggers. The change installable trigger for Google Sheets is triggered when changes are done to an spreadsheet including deleting a row. Triggers call a function and send to that funcion an event object. In the case of the change trigger the relevant related event object property is changeType. It is helpful for this case because when a row be deleted the values of this property will be REMOVE_ROW.

Unfortunately this trigger doesn't include a property with data about the context, so the trigger by itself will not provide the details about what row was deleted.

It looks that the best way to handle this, is by comparing the data in the spreadsheet with data stored in another place, i.e. your script might compare the events data in the spreadsheet with the events in the Google Calendar.

If you are open to change a bit your idea, instead of using a trigger activated by the row deletion you might use a script to do the row deletion and the calendar event deletion.

Script to run on delete row

1 Answers1