I want users to search for their info from multiple sheet files and display multiple results boxes(row) but currently it's searching from one sheet and displaying only the first result(row) and not displaying the rest results.

What do I need to change in the script?

function submitData(obj){

  var ss = SpreadsheetApp.openById("SHEETID");
  var sheet = ss.getSheetByName("SHEETNAME");
  var  flag  =  1 ;
  var lr = sheet.getLastRow();
      for(var i = 1;i <= lr;i  ){
  var id = sheet.getRange(i, 1).getValue();
      if(id == obj){
      flag = 0;
  var B = sheet.getRange(i, 2).getValue();      
  var C = sheet.getRange(i, 3).getDisplayValue();
  var data ="<div ><h3>Info1:" B "</h3><p>Info2:" C "</p></div>";
  
  return data;

    }
   }
     if(flag==1){ 
       var data = '<div >Data not found</div>';
      return data;

    } 

index.html

 <input id="id" >
  <button type="submit" onclick="info()"> search</button>
  <div id="result"></div>
    <script>
      function info () {
        let obj = document.querySelector('#id').value;
        let updateLocation = document.querySelector('#result');
        updateLocation.innerHTML = "Searching...";

        function onFailure(error){
          let warning = error;
          updateLocation.innerHTML = warning;
        };
       
        function onSuccess(response){
          let result = response;
          updateLocation.innerHTML = result;
        };
        google.script.run.withFailureHandler(onFailure)
                         .withSuccessHandler(onSuccess)
                         .submitData(obj);
      };
    </script>

CodePudding user response：

1. You're only opening a single sheet file in your example. If you want to search multiple you'll have to specify which files you want to open and loop through them. My example below assumes all the files have the same sheet name.
2. Right now you're returning the first matched result, which is causing your search function to exit prematurely. To display multiple results you can push the matched lines to an array, then return that array once all the scanning is complete.

function submitData(obj) {
  //specify what sheet IDs you want to search
  var sheetNames = ['foo', 'bar', 'bla']
  //create an array to push results to
  var inject = []

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

    var sheetName = sheetNames[i]

    var ss = SpreadsheetApp.openById(sheetName);
    var sheet = ss.getSheetByName("SHEETNAME");
    var flag = 1;
    var lr = sheet.getLastRow();

    for (var i = 1; i <= lr; i  ) {
      var id = sheet.getRange(i, 1).getValue();
      if (id == obj) {
        flag = 0;
        var B = sheet.getRange(i, 2).getValue();
        var C = sheet.getRange(i, 3).getDisplayValue();
        var data = "<div class='card'><h3>Info1:"   B   "</h3><p>Info2:"   C   "</p></div>";

        //push result to array
        inject.push(data)

      }
    }
  }

  if (flag == 1) {
    var data = '<div >Data not found</div>';
    return data;
  } else {
    //return joined array as string
    return inject.join()
  }

}

CodePudding user response：

Multi-spreadsheet search

function MultiSearch() {
  const ss = SpreadsheetApp.getActive();
  const idsh = ss.getSheetByName("ssids");
  const rsh = ss.getSheetByName("Results");
  rsh.getRange(2,1,rsh.getLastRow() - 1, rsh.getLastColumn()).clearContent()
  const ids = idsh.getRange(2,1,idsh.getLastRow() - 1,idsh.getLastColumn()).getValues().filter(r => r[2] == "TRUE").map(r => r[1]);
  const r = SpreadsheetApp.getUi().prompt("Search Dialog","Enter Search String",SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if(r.getSelectedButton() == SpreadsheetApp.getUi().Button.OK && r.getResponseText().length > 0) {
    ids.forEach(id => {
      let ss = SpreadsheetApp.openById(id);
      let tf = ss.createTextFinder(r.getResponseText()).findAll();
      tf.forEach(f =>{
        rsh.appendRow([ss.getName,f.getSheet().getName(),f.getRow(),f.getColumn()]);
      })
    })
  }
}

ssids Sheet:

Results Sheet: