How to return matched row data by a key value in Google Sheets

Question

I would like to achieve the following by using Apps Script.:

I have a spreadsheet called "Test." In "Test," there are 9 tabs but I want to search only in "sheet1," "sheet2," "sheet3."

Answer 1

You want to search a course name that many appear in any one of three sheets, and then return some relevant course data.

You have described a dynamic ui-based data entry and reporting format. I will leave this for you to develop. This answer used a very simple "search" form on a "search" sheet. There is a cell to enter the search term, and space to display the results.

These images show the before and after:

---

Blank Search form

---

Populated Search Results

---

The basics of the script are:

• create a new sheet, name it "search", and create headings as per the image: the search term goes in cell B2; the search results headers go on row 6 (the results will appear on row 7).
• the sheet names that hold course data are listed in an array: var datasheets = ["Sheet1","Sheet2","Sheet3"];
• the script loops through the data sheets: var sheet = ss.getSheetByName(datasheets[d]);
• the data on each sheet is obtained: var data = sheet.getRange(startrow,1,LR-startrow+1,5).getValues();
• using the Javascript MAP method, an array of the courses is established: var courses = data.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]
• using the Javascript indexOf method, the script searches for the search term in the courses array: var result = courses.indexOf(searchdata);
• when a match is found, the values for course name, sheet name, status, owner and ID are retrieved and pushed onto an array: e.g. searchresults.push(data[result][3]);// ID
• the search results are updated with the array results
• there is a check to establish whether the search term was found: if (resultcounter ==0){
• if not, then the search results display "No matches": searchresults.push("No matches"); // message

---

---

function so5868680301() {

  var ss = SpreadsheetApp.getActiveSpreadsheet()

  // define the search sheet
  var sheetname= "search";
  var searchsheet  = ss.getSheetByName(sheetname);

  // get the search term
  var searchdata = searchsheet.getRange("B2").getValue();
  //Logger.log(searchdata);//DEBUG

  // define the search results output range
  var searchoutput = searchsheet.getRange(7,1,1,5);
  // clear the search results
  searchoutput.clearContent();

  // the sheets to be searched
  var datasheets = ["Sheet1","Sheet2","Sheet3"]
  //Logger.log(datasheets.length);// DEBUG

  // the start row on the data sheets
  var startrow = 5;
  var resultcounter = 0;


  // loop through the sheets 
  for (var d =0;d<datasheets.length;d++){

    var sheet = ss.getSheetByName(datasheets[d]);
    //Logger.log(sheet.getName());// DEBUG

    // get the Last row on this sheet
    var LR = sheet.getLastRow();

    // define a range for the sheet
    var data = sheet.getRange(startrow,1,LR-startrow+1,5).getValues();
    //Logger.log(data);//DEBUG

    // get the course list in column A as a separate array
    var courses = data.map(function(e){return e[0];});//[[e],[e],[e]]=>[e,e,e]

    // search the courses array for the serachterm
    var result = courses.indexOf(searchdata);

    // if the result is -1, then the sraech term couldn't be found, 
    // otherwise the result is the instance number in the courses array
    if (result !=-1){

      Logger.log(courses); //DEBUG
      Logger.log(result); //DEBUG

      // create an empty array
      var searchresults=[];

      //push the search results onto the array.
      //sheet name
      searchresults.push(courses[result]);// course name
      searchresults.push(sheet.getName());// sheet name
      searchresults.push(data[result][1]);// status
      searchresults.push(data[result][2]);// owner
      searchresults.push(data[result][3]);// ID

      //Logger.log(searchresults);//DEBUG

      searchoutput.setValues([searchresults])
      resultcounter = resultcounter +1;
    }



  }
  if (resultcounter ==0){
    var searchresults=[];
    searchresults.push("No matches"); // message
    searchresults.push(""); 
    searchresults.push(""); 
    searchresults.push(""); 
    searchresults.push(""); 
    //Logger.log(searchresults);//DEBUG
    searchoutput.setValues([searchresults])
  }

}