How to show duplicates in a UI when searched in google sheets?

Question

I am developing a tracking system for candidates

Attached a search button. But I want a UI to pop up which will show the duplicates if present. And when one of the duplicates is clicked, it will fill the required details in the form

I have made this. But the results are the first row which matches the search text in B4

function Search()
{

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formS = ss.getSheetByName("Form");
  var dataS = ss.getSheetByName("Data");

  var str = formS.getRange("B4").getValue();
  var values = dataS.getDataRange().getValues(); 

  var valuesFound = false; 

  for (var i = 0; i < values.length; i++) 
    {
    var rowValue = values[i]; 
   
    
    if (rowValue[0] == str) {
           
      formS.getRange("B7").setValue(rowValue[0]) ;
      formS.getRange("B9").setValue(rowValue[1]) ;
      formS.getRange("B11").setValue(rowValue[2]) ;
      formS.getRange("B13").setValue(rowValue[3]) ;
      formS.getRange("E7").setValue(rowValue[4]) ;
      formS.getRange("E9").setValue(rowValue[5]) ;
      formS.getRange("E11").setValue(rowValue[6]) ;
      formS.getRange("E13").setValue(rowValue[7]) ;

      return; 
      
      }
  }

if(valuesFound==false){
    var ui = SpreadsheetApp.getUi();
  ui.alert("No record found!");
 }

}

Answer 1

Description

You could get all the first name matches and show them in a dialog, numbered so its easier to pick. Once the user picks the name you can fill out the form.

To use a dropdown and pick a name, that would require a custom dialog. Doable but more complex.

This script is executed from a menu item.

Data

Script

function search() {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let dataS = spread.getSheetByName("Data");
    let str = "John";
    let values = dataS.getDataRange().getValues();
    let rowValue = values.filter( row => row[0] === str );
    let ui = SpreadsheetApp.getUi();
    let prompt = "";
    rowValue.forEach( (row,i) => prompt = prompt.concat((i+1).toString(),": ",row[0], " ",row[1],"\n"));
    if( rowValue.length === 0 ) {
      ui.alert("Name not found ["+str+"]");
      return;
    }
    else if( rowValue.length > 1 ) {
      let response = ui.prompt("Pick a name",prompt,ui.ButtonSet.OK_CANCEL);
      if( response.getSelectedButton() == ui.Button.OK ) {
        str = response.getResponseText();
        str = parseInt(str)-1;
        rowValue = rowValue[str];
      }
    }
    else {
      rowValue = rowValue[0];
    }
    ui.alert(rowValue.toString());
    // Now you can fill out your form with rowValue
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

Reference

• Array.filter()
• Array.forEach()
• SpreadsheetApp Ui prompt

Answer 2

Description

So I decided to show how a custom dialog can be used to display a list of names to pick from. First a Ui dialog is displayed to use a filter name. If no name is specified all names will be listed.

Using HTMLService a custom dialog is build using the the pushed variable option for an HTM Template.

Once a name is picked from the list google.script.run is used to return the name to the server and the form can be built from there.

Code.gs

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createMenu("My Menu");
  menu.addItem("Test","showTest");
  menu.addToUi();
}

function showTest() {
  try {
    let ui = SpreadsheetApp.getUi();
    let response = ui.prompt("What name do you want to search for",ui.ButtonSet.OK_CANCEL);
    if( response.getSelectedButton() == ui.Button.OK ) {
      var name = response.getResponseText();
      if( name === "" ) name = "__All";
    }
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let dataS = spread.getSheetByName("Data");
    let data = dataS.getRange(1,1,dataS.getLastRow(),2).getValues();  // Get range A1:B
    if( name !== "__All" ) {
      data = data.filter( row => row[0] === name );
    }
    if( data.length === 0 ) {
      ui.alert("No names mathcing ["+name+"] found");
      return;
    }
    let html = HtmlService.createTemplateFromFile('HTML_Test');
    html.data = data;
    html = html.evaluate();
    SpreadsheetApp.getUi().showModalDialog(html,"Show Test");
  }
  catch(err) {
    SpreadsheetApp.getUi().alert(err);
  }
}

function pickName(name) {
  try {
    let spread = SpreadsheetApp.getActiveSpreadsheet();
    let dataS = spread.getSheetByName("Data");
    dataS.getRange(1,5).setValue(name);
    // Build your form here
  }
  catch(err) {
    console.log(err);
  }
}

HTML_Test.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <select id="selectName">
      <? for (let i = 0; i < data.length; i++) { ?>
        <option><?= data[i][0]+" "+data[i][1] ?></option>
      <? } ?>
    </select>
    <input type="button" onclick="buttonOnClick()" value="Submit">
    <script>
      function buttonOnClick() {
        let name = document.getElementById("selectName").value;
        google.script.run.pickName(name);
        google.script.host.close();
      }
    </script>
  </body>
</html>

Reference

• HTMLService
• HTML Template
• google.script.run
• google.script.host