Option to delete all child-items

Hi devs. So I just learned the Tree:parent trick. Wouldn’t it be nice to be able to delete the parent item along with its child elements (optionally of course)? Here’s a sample sheet https://drive.google.com/open?id=10Qu9GJW_gNG9mo_LLoyPTMdEgsysfhVpkESY3cBta6E

Thanks :slight_smile:

Interesting! Can you tell us more about your use case?

So in that sheet I’m creating an inventory app. How the app works: first sheet is to create, edit and view the ‘Folder’ and in the other sheet are the items corresponding to each folder (also can create, edit and view).

I managed to link them using the Tree:Parent method but when I need to delete the parent ‘Folder’ I need the option for all associated items/child to be deleted as well instead of deleting them individually. Useful if the child-items list is very long.

Please advice.

Glide can’t do that, and probably won’t for quite a while, but we’ll think about it.

2 Likes

You could write a relatively simple script that would “clean” up the related child sheet. It would scan the child sheet and do a lookup into the parent sheet. If no match the row would be deleted. Probably about 20 lines of code. Add a timed trigger and it could be run periodically.

1 Like

Interesting :thinking: I’ll try to figure that out. You could put a timer in gsheet? Is it through formula or an embedded script?

When you get into the script editor one of the icons is a picture of a clock. That is where you would set up the timed trigger. I lied, the code is actually 44 lines of code but I did throw in some comments to hopefully make it easier to follow. I did use arrays as that is the fastest way to scan through data and write to a sheet in mass. So basically you would set up a trigger to run this code, maybe nightly to clean up the orphan’d children. Here is the sheet. Make a copy of it and then take a look at the script. Note I created copies of the data tabsheets for testing so I could copy the data back for further testing.

/*
  function to clear out any child records that don't have a parent
*/
function deleteOrphanItems(){
  var childSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Items");
  var parentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Folder");
  var colNumOfChildKey = 1;
  var colNumOfParentKey = 1;
  
  deleteChildren(childSheet, parentSheet, colNumOfChildKey, colNumOfParentKey);
  
};

// by using arrays this process should be very fast unless the data rows become very long
// however it is advised to remove all empty trailing rows
function deleteChildren(childSheet, parentSheet, colNumOfChildKey, colNumOfParentKey) {
  var parentKeyArray = parentSheet.getRange(1, colNumOfParentKey, parentSheet.getLastRow(), 1).getValues();
  var childSheetArray = childSheet.getDataRange().getValues();
  // add the top column header row (note if you have more than on column header row this would have to be adjusted)
  var newChildSheetArray = [childSheetArray[0]]; 
  var childKeyValue;
  var foundIt;
  
  // walk the child array starting at the second element (remember arrays are zero based)
  for (var i = 1; i < childSheetArray.length; i++){
    // since the array is zero based and the colum numbers aren't we need the - 1
    childKeyValue = childSheetArray[i][colNumOfChildKey - 1]; 
    foundIt = false;
    // see if the child key is in the parentKeyArray and if it is add the child row to the new array
    for (var j = 1; j < parentKeyArray.length; j++){
      if (childKeyValue == parentKeyArray[j][0]){
        foundIt = true;
        break;
      };
    };
    // this is where we are adding the found child rows
    if (foundIt) {
      newChildSheetArray.push(childSheetArray[i]);
    };
  };
  
  // clear the child sheet contents only not formatting
  childSheet.clearContents();
  // write back all the values
  childSheet.getRange(1,1, newChildSheetArray.length, newChildSheetArray[0].length).setValues(newChildSheetArray);
  
}

3 Likes

Will get back to this. This is a lot to take for a non programmer like me haha. Thanks!

@talkshowhost85 Yeah it can get pretty confusing real fast, but no worries whenever you want to dive into the deep end of the pool there are plenty of YouTube videos that will help you out.