I've been struggling with understanding where I went wrong with the following script. The purpose of the script is to delete a row whenever the unique ID in 'individual_client_sheet' matches an id from the row in the two sheets: 'clients_database' and 'clients_sheet'. So far, the code is able to delete the corresponding row in each sheet but the error message "TypeError: Cannot read property '2' of undefined" keeps pooping up and I cannot comprehend why. Secondly, the script would not reset the fields in 'individual_client_sheet' so it seems like it stops working after deleting the records. Here is the script:

   function deleteClientRecord () {
   // Get Active Sheets
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var shClient = ss.getSheetByName ('individual_client_sheet');
    var shData = ss.getSheetByName ('clients_database');
    var shClientsList = ss.getSheetByName ('clients_sheet');

   // Obtain and verify user response 
    var ui = SpreadsheetApp.getUi();
    var response = ui.alert ('Delete', 'Are you sure you want to remove this record?', ui.ButtonSet.YES_NO);

   if (response == ui.Button.NO) {
    return;
   }

   var searchValue = shClient.getRange('AF2').getValue();
   var shDataValues = shData.getDataRange().getValues();
   var shClientsListValues = shClientsList.getDataRange().getValues();


   // Delete record from 'clients_database'
      var shDataValuesFound = false; 

      for (var i = 0; i < shDataValues.length; i  ) {
      var rowValue = shDataValues[i]; 

       if (rowValue[3] == searchValue) {
        var iRow = i 1;
        shData.deleteRow(iRow);
       }
      }


  // Delete record from All CLients Sheet 
     var shClientsListValuesFound = false; 

     for (var j = 0; i < shClientsListValues.length; j  ) {
     var rowValue2 = shClientsListValues[j];

     if (rowValue2[2] == searchValue) {
      var jRow = j 1;
      shClientsList.deleteRow(jRow);

 ui.alert('Record deleted for - Client #'   shClient.getRange('AF2').getValue()   '');  
  }
 }

 // Delete Fields

 // Checkboxes 
   shClient.getRange('Z10:AA13').setValue(false);
   shClient.getRange('AP5:AQ5').setValue(false);
  
  // Basic Client Info
    shClient.getRange('B5:G5').clearContent(); // Business Name
    shClient.getRange('AF2').clearContent(); // Client ID
    shClient.getRange('B10').clearContent(); // website
    shClient.getRange('B12:B13').clearContent(); // billing add         
    shClient.getRange('B15:B16').clearContent(); // postal add 
    shClient.getRange('B18').clearContent(); // primary contact 
    shClient.getRange('B20').clearContent(); // phone
    shClient.getRange('B22').clearContent(); // email
    shClient.getRange('B26').clearContent(); // additional info 1          
    shClient.getRange('B28').clearContent(); // additional info 2 

  // Client Status 
    shClient.getRange('AN5:AO5').clearContent(); // client status
    shClient.getRange('AP5:AQ5').clearContent().setValue(false); // importance


  shClientsListValuesFound = true;
  shDataValuesFound = true;
 return;
}

CodePudding user response：

I assume it is the error line:

 for (var j = 0; i < shClientsListValues.length; j  ) {

should be j instead of i:

 for (var j = 0; j < shClientsListValues.length; j  ) {