javascriptgoogle-sheetsgoogle-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


    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

    harri

    SAMPLE DATA - sheet "365: before

    365 before

    SAMPLE DATA - Person 2

    person 2 match

    person chnage names

    SAMPLE DATA - Person 4

    person4 match

    person 4 chnage names

    SAMPLE DATA - sheet "365" after

    365 after

    SAMPLE - sheet" Readme" after

    readme after