Option to delete all child-items

Feature Requests
7

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