CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Samuel R Ferreira
Samuel R Ferreira

Reputation: 67

Script to run on delete row

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();
    }
}

Upvotes: 0

Views: 941

Answers (1)

Wicket
Wicket

Reputation: 38200

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.

Related

References

Upvotes: 1

Related Questions