0

I have 2 sheets "Main" and "Data Storage"

Main: should allow me to search "Data Storage" and locate information form any column(let say Column B') and provide me with information within the entire row belonging to column "B" which has been successful...somewhat, i used "Jane dowe" and was able to get information, but when i tried using other data within the row the script ran but came up blank

issue: I need to be able to use any veritable of column B' to locate information with in the row

What I have so far is :

var SPREADSHEET_NAME = "Data Storage";
var SEARCH_COL_IDX = 1;
var RETURN_COL_IDX = 0;

function searchStr() {
  
  var ss        = SpreadsheetApp.getActiveSpreadsheet();
  var formSS    = ss.getSheetByName("Main"); //Form Sheet
  
  var str       = formSS.getRange("I20").getValue();

  for (var i = 16; i < values.length; i++) {
    var row = values[i];
    if (row[SEARCH_COL_IDX] == str) {
     
      
      formSS.getRange("G3").setValue(row[0]) ;
      formSS.getRange("C5").setValue(row[1]);
      formSS.getRange("C3").setValue(row[2]);
      formSS.getRange("C7").setValue(row[3]);
      formSS.getRange("E7").setValue(row[4]);
      formSS.getRange("G7").setValue(row[5]);
      formSS.getRange("C9").setValue(row[6]);
      formSS.getRange("E9").setValue(row[7]);
      formSS.getRange("G9").setValue(row[8]);
      formSS.getRange("C11").setValue(row[9]);
      formSS.getRange("E11").setValue(row[10]);
      formSS.getRange("G11").setValue(row[11]);
      formSS.getRange("I10").setValue(row[12]);
      formSS.getRange("I5").setValue(row[13]);
      formSS.getRange("I16").setValue(row[14]);
      formSS.getRange("B18").setValue(row[15]);
      
           
      return row[RETURN_COL_IDX];
     
      
    }
  }
}

example of sheet 1 "Main"

example of sheet 2 "Data Storage"

0

You want to enter an employee name, search for that name on another sheet, and return the information for that employee to the initial sheet.

Search Name - Data validation

I simplified the search (and also reduced the risk of error) by using data validation in cell I20 of "Data Storage" - the parameters are "List from a Range", and the range is "Main!C2:C". This range enables you do add more employees to the "Main" sheet and those employee names will automatically appear in the drop down list on "Data Storage".

Processing

  • getDataRange() Return the entire data range for "Main" at the outset.
  • Column C of Sheet "Main" is searched using Javascript indexOf.
    • Column C = var employeedata = datavalues.map(function(e){return e[2];});
    • Search = var searchresult = employeedata.indexOf(searchname);
  • Error checking: indexOf returns a value of -1 if the search term is not present. I added an Alert to provide user feedback, but if Data validation is used, then the likelihood of an error is slim.
  • indexOf provides the basis for selecting the row number of "Main" so that the relevant employee data can be returned
    • var searchdata = data.getRange(searchresult+1, 1, 1, datalastLC).getValues();
  • the data is returned as a row. In my example, I transposed that row to be able to create vertical output.
    • var searcdatavertical = searchdata[0].map(function(elem) {return [elem];});

function wa14849101() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 

  // set up the sheet names
  var datasheetname = "Main"; 
  var searchsheetname = "Data Storage";
  var data = ss.getSheetByName(datasheetname);
  var search = ss.getSheetByName(searchsheetname);

  // get the employee name to search
  var searchname = search.getRange("I20").getValue();
  // Logger.log("DEBUG: search name = "+searchname);

  // get the data on data sheet
  var datalastLC = data.getLastColumn();
  var datavalues = data.getDataRange().getValues();
  // Logger.log(datavalues); // DEBUG

  // get the employee name column to search
  var employeedata = datavalues.map(function(e){return e[2];});//[[e],[e],[e]]=>[e,e,e]
  // Logger.log(employeedata); // DEBUG

  var searchresult = employeedata.indexOf(searchname);
  Logger.log("DEBUG: the index number = "+searchresult+" which means that the row number  = "+searchresult+1); 
  
  if (searchresult == -1){
    // indexOf returns -1 if the element is not present
    // Display a dialog box with a title, message, and "OK" button. 
    var ui = SpreadsheetApp.getUi();
    var response = ui.alert('Employee Search', 'Emplopyee'+searchname+' could not be found on the Data sheet\nProcessing stopped.', ui.ButtonSet.OK);
    return;
  }
  
  // get the employee data
  var searchdata = data.getRange(searchresult+1, 1, 1, datalastLC).getValues();
  // Logger.log(searchdata); //DEBUG
  
  // transpose the row array to a column format
  var searcdatavertical = searchdata[0].map(function(elem) {return [elem];});
  // Logger.log(searcdatavertical); DEBUG
  
   // create the output range
  var outputrange =  search.getRange(1, 11, datalastLC, 1); // column K=11, num of rows = number of data columns
  outputrange.setValues(searcdatavertical);

return;
}

Sample Data

Sample data


Sample search and output

Search

References

map

indexOf

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.