Any ideas how to support cascading deletes?

Feature Requests
1

This is a general feature request to support cascading deletes in GlideApp.

Background: My app has a sheet “Exercises” and “Exercise Homework”. Items in homework relate via unique key to exercises, i.e. someone posts an exercise and others can post replies how they completed the exercise.

Challenge: The person who posted the exercise can delete it later. All good so far. But the replies to the exercise were only accessible on the individual exercise page and can no longer be reached and hence deleted from the app. The result is stale data in the homework sheet.

Request: It should be possible to configure the relationship of the items in one sheet to items in another as a “cascading” relationship, so that when the exercise gets deleted, all posted homework items relating to it are also removed.

I haven’t tested how the “Comments” component works, but since it has a built in capability to relate to a unique key item, I’d hope the comments relating to the item are deleted when the item is deleted?

Maybe someone has an idea how to do this via a Google Sheet formula or feature for the moment?

Thanks for your help!

2

Unfortunately, the only way to accomplish this is with a script. I’ve written a script that does something similar, by deleting appropriate cells within a row, when the corresponding cell in that row becomes empty. I’m sure an approach like this would work for you.

One caveat is that if your app is in the free tier, the data won’t refresh that quickly once you run the script. It may take up to 2-3 minutes depending on various things. Something to think about when you compile your script.

3

Thanks for the tipp! I’ll look into scripting this somehow and running a weekly cleanup script.

4

Yes, script work very well (onchange trigger, verify that one of your cell is empty, example à unique id cell, and delete the entire row)

1 Like
5

Sorry for my question, but is it possible to show this script?
This would help a lot, because whenever a record is deleted, this blank line is left in the spreadsheet and messes up the app

6

Hereafter the part of my script managing to delete entire rows, after they have been cleared by Glide :

	if (shtName === "ADMIN_MKTG_MAILING") {
      var activeRng = sht.getActiveRange();
	  var activeCol = activeRng.getColumn();
	  var activeRow = activeRng.getRow();
     
      // If a launched marketing campaign is deleted in Glide, we have to delete the row in the gsheet
      if (activeRow > 2 && sht.getRange(activeRow, 6).getValue() === "") {
        sht.deleteRow(activeRow); // delete the entire row 
      }
    }

This prt of script is in a script linked to the OnChange trigger. Each time a ro is cleared by Glide in the tab ADMIN_MKTG_MAILING of the google sheet, I check if the cell of this row, at the 6th column is empty. In my setup, this cell contains the unique ID of a marketing campaign. If this cell is empty, I delete the entire row (no more blank rows).

Hope this helps,

2 Likes
7

Hi Christophe thanks for the help, I’m going to do a test.

1 Like