javascript google-sheets google-apps-script

Google App Script recursive Loop does not trigger prompt.getResponseText()

I made the following script to identify names that were not matching between two files. This works perfectly fine for the first loop, and it gets to the var prompt perfectly fine on the second loop, however whenever I try to complete the prompt on sheets it seemingly does nothing and just stalls there. It is not because of the input because if I do that input the first time it goes through fine. I must be missing something as to why this isn't looping properly. Any help would be greatly appreciated

function onEdit(e) {
    startPoint();
    
}

function startPoint(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
    var cell = "N5";
    var difference = sheet.getRange(cell).getValue().toFixed(2);

    if (difference > 0){
      yesDifference(difference);
    }else noDifference(difference);
}

function yesDifference(num){
  const ui = SpreadsheetApp.getUi()
    const result = ui.alert(
     'There is a difference of: ' + 
     num
      + '\nWould you like to solve the issue',
      ui.ButtonSet.YES_NO)
    if (result == ui.Button.YES){
      findDifference(num);
    }else{
      return
    }
}

function noDifference(num){
  const ui = SpreadsheetApp.getUi()
    const result = ui.alert(
     'Tips are matching!');
    return
}

function findDifference(num){
  const ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
  var missingNames = sheet.getRange("Z3:Z20").getValues();
  for(var i = 0; i < missingNames.length; i++){
      var person = missingNames[i].toString();
      if(person.length > 1){
        const result = ui.alert(
          'I am not able to match:\n' + person + '\nbetween Harri and R365 would you like to try and find them?',
          ui.ButtonSet.YES_NO);
        if(result == ui.Button.YES){
          findNameMatch(person);
        }
      }
  }
    return
}

function findNameMatch(name){
  const ui = SpreadsheetApp.getUi();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");

  var allNames = sheet.getRange("A2:A100").getValues();
  var filteredNames = [];

  for(var i = 0; i < allNames.length; i++){
    var person = allNames[i].toString();
    if(!(person.length > 1)){
      i = allNames.length;
    }else{
      if(!(filteredNames.includes(person))){
        filteredNames.push(person);
      } 
    }
  }

  var prompt = ui.prompt('Out of the following names:\n\n\n' + filteredNames.join('\r\n') + "\n\n\nPlease enter below which name is supposed to be " + name);

  var fullName = prompt.getResponseText().toString();

  var resp = ui.alert(fullName);

  var firstName = fullName.substring(0, fullName.indexOf(' '));
  var lastName = fullName.substring(fullName.indexOf(' ') + 1);

  var originalFirst = name.substring(0, name.indexOf(' '));
  var originalLast = name.substring(fullName.indexOf(' ') + 1);

  var names = ui.alert(
    'First Name: ' + firstName + "\nLast Name: " + lastName
  )

  changeName(originalFirst, firstName, originalLast, lastName);
  startPoint();
}

function changeName(oldF, correctF, oldL, correctL){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("365");
  var allFNames = sheet.getRange("A2:A100").getValues();
  var allLNames = sheet.getRange("B2:B100").getValues();

  for(var i = 0; i < allFNames.length; i++){
    var name = allFNames[i].toString();
    var lastName = allLNames[i].toString();
    if(!(name.length > 1)){
      i = allFNames.length;
    }else{
      if((name === oldF) &&(lastName === oldL)){
        var newFirst = "A" + (i + 2);
        var newLast = "B" + (i + 2);

        var newFNames = sheet.getRange(newFirst).setValue(correctF);
        var newLNames = sheet.getRange(newLast).setValue(correctL);

        const ui = SpreadsheetApp.getUi();
        const result = ui.alert(
        'The names have been changed at ' + newFirst + ", and " + newLast + " to " + correctF + ", and " + correctL);
        i = allFNames.length;
      }
    }
  }
  return
}

I have created a spreadsheet with minimal data in it to recreate the issue. On the R365 Sheet if you edit one of the names it should trigger the function the same way

https://docs.google.com/spreadsheets/d/1uLOghTaxPURScAsAvwhyPZpmaVh5P7o7E4c88gwn9a4/edit?gid=160981461#gid=160981461

Solution

You want to update name of customers that aren't being matched with existing data but your loop is not working.

Consider this answer:

Differences

'onEdit(e)` is a Simple trigger and is timing out maximum execution time.
- Change the function name name from onEdit(e) to something else, say, updateNames(e)
- Create an Installable onEdit trigger for the new function name.
- If you don't change the function name, then there is a risk that the function will execute twice - once as an Installable trigger and once as Simple trigger. The result is confusing and promotes error.
make use of Event Objects.
- insert Logger.log(JSON.stringify(e)) immediately after updateNames(e).
  - This will display the available Event Objects
- change startPoint(e) and function startPoint(e){
In startpoint(e) include a test for sheet, column and row, such as:
if (e.range.getSheet().getName() == "README" && e.range.rowStart == 5 && e.range.columnStart == 14 ){
- this will ensure that unrelated edits can be made without invoking the entire function
error in var originalLast = name.substring(fullName.indexOf(' ') + 1);
- change to var originalLast = name.substring(name.indexOf(' ') + 1);
delete startPoint(); from the last line of function findNameMatch(name){
- The loop relies on for(var i = 0; i < missingNames.length; i++){ in function findDifference
- returning to startPoint() restarts the entire function rather than allowing the "for" loop to play out.
A number of Logger statements have been included to track the values of variables, and the progress of routines. These slow processing and the OP may wish to display those that appear helpful at any point in time.
It is not clear whether the value in Column M on sheet "365" should be updated with the "difference".

function updateNames(e) {
  // Logger.log(JSON.stringify(e)) // DEBUG
  startPoint(e);
    
}

function startPoint(e){
  // test for edited sheet and column and row
  if (e.range.getSheet().getName() == "README" && e.range.rowStart == 5 && e.range.columnStart == 14 ){ // cell 'N5"
    // edit is in correct place, continue processing 
    // Logger.log("DEBUG: edit was in N5 on Readme - continue processing")
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
    var cell = "N5";
    var difference = sheet.getRange(cell).getValue().toFixed(2);
    if (difference > 0){
      yesDifference(difference);
    }else{
       noDifference(difference);
    }
  }
  else{
    // edit is NOT in correct place, stop processing 
    // Logger.log("DEBUG: edit was NOT N5 on Readme - stop processing")
    return
  }
}

function yesDifference(num){
  const ui = SpreadsheetApp.getUi()
    const result = ui.alert(
     'There is a difference of: ' + 
     num
      + '\nWould you like to solve the issue',
      ui.ButtonSet.YES_NO)
    if (result == ui.Button.YES){
      // solve the difference
      // Logger.log("DEBUG: solve the difference")
      findDifference(num);
    }else{
      // do nothing
      // Logger.log("DEBUG: Do nothing")
      return
    }
}

function noDifference(num){
  const ui = SpreadsheetApp.getUi()
    const result = ui.alert(
     'Tips are matching!');
    return
}

function findDifference(num){
  const ui = SpreadsheetApp.getUi();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");
  var missingNames = sheet.getRange("Z3:Z20").getValues();
  for(var i = 0; i < missingNames.length; i++){
      var person = missingNames[i].toString();
      if(person.length > 1){
        const result = ui.alert(
          'I am not able to match:\n' + person + '\nbetween Harri and R365 would you like to try and find them?',
          ui.ButtonSet.YES_NO);
        if(result == ui.Button.YES){
          // Logger.log("DEBUG: the name of the person is "+person)
          findNameMatch(person);
        }
      }
  }
    return
}

function findNameMatch(name){
  const ui = SpreadsheetApp.getUi();

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("README");

  var allNames = sheet.getRange("A2:A100").getValues();
  var filteredNames = [];

  for(var i = 0; i < allNames.length; i++){
    var person = allNames[i].toString();
    if(!(person.length > 1)){
      i = allNames.length;
    }else{
      if(!(filteredNames.includes(person))){
        // Logger.log("DEBUG: filtered names doesn't include "+person+", so person added to filtered names")
        filteredNames.push(person);
      } 
    }
  }

  var prompt = ui.prompt('Out of the following names:\n\n\n' + filteredNames.join('\r\n') + "\n\n\nPlease enter below which name is supposed to be " + name);

  var fullName = prompt.getResponseText().toString();

  var resp = ui.alert(fullName);
  // Logger.log("DEBUG: name chosen for findNameMatch = "+fullName)
  var firstName = fullName.substring(0, fullName.indexOf(' '));
  var lastName = fullName.substring(fullName.indexOf(' ') + 1);

  var originalFirst = name.substring(0, name.indexOf(' '));
  var originalLast = name.substring(name.indexOf(' ') + 1);
  // Logger.log("DEBUG: findNameMatch: first name = "+firstName+", last name = "+lastName+", Original first  = "+originalFirst+", Original last = "+originalLast)
  var names = ui.alert(
    'First Name: ' + firstName + "\nLast Name: " + lastName
  )

  changeName(originalFirst, firstName, originalLast, lastName);
  
}

function changeName(oldF, correctF, oldL, correctL){
  // Logger.log("DEBUG: changename: oldF = "+oldF+", correct F = "+correctF+", oldL = "+oldL+", correct L = "+correctL)
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("365");
  var allFNames = sheet.getRange("A2:A100").getValues();
  var allLNames = sheet.getRange("B2:B100").getValues();

  for(var i = 0; i < allFNames.length; i++){
    var name = allFNames[i].toString();
    var lastName = allLNames[i].toString();
    // Logger.log("DEBUG: changeName: i:"+i+", name:"+name+", last name:"+lastName+", name length:"+name.length)
    if(!(name.length > 1)){
      i = allFNames.length;
    }else{
      if((name === oldF) &&(lastName === oldL)){
        // Logger.log("DEBUG: name <> old F and last name <> oldL")
        var newFirst = "A" + (i + 2);
        var newLast = "B" + (i + 2);
        // Logger.log("DEBUG: cells: newFirst:"+newFirst+", newlast:"+newLast)
        var newFNames = sheet.getRange(newFirst).setValue(correctF);
        var newLNames = sheet.getRange(newLast).setValue(correctL);
        // Logger.log("DEBUG: Updated newfirst and newlast with correctF and correctL")
        const ui = SpreadsheetApp.getUi();
        const result = ui.alert(
        'The names have been changed at ' + newFirst + ", and " + newLast + " to " + correctF + ", and " + correctL);
        i = allFNames.length;
        // Logger.log("DEBUG: i is set to allFnames length: "+i)
      }else{
        // Logger.log("DEBUG: if unsuccessful")
      }
    }
  }
  return
}

SAMPLE DATA - sheet "Harri" before

SAMPLE DATA - sheet "365: before

SAMPLE DATA - Person 2

SAMPLE DATA - Person 4

SAMPLE DATA - sheet "365" after

SAMPLE - sheet" Readme" after