searchspreadsheetmultiple-results

I nead the value of a specific spreadsheet cell when I get the row number as a search answer


I'm new in the forum and also not a programmer, so I apologize in advance for any mistakes.

I use a Google sheet as an order inventory. I use two sheets as datasheets with identical fields, one for current orders and one for old orders. I also have a third sheet which I use as user input form.

I'm trying to code a script for searching both datasheets for a string the users can put in a specific cell in the form, e.g. the name or the phonenumber of a customer. The results I need are all the orderIds which I store in column A in both datasheets.

With the following code that I found here, I get as result the the name of the sheets and the row number. I need the name of the sheet so that the user knows if the customer has other orders, old or current and the value of A column for each result instead of the row number.

function searchString(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var search_string = sheet.getRange("C3").getValue();
  var textFinder = ss.createTextFinder(search_string);
  var search_rows = textFinder.findAll().map(r => ({sheetName: r.getSheet().getSheetName(), code: r.getRow()}));
  var ui = SpreadsheetApp.getUi();
  ui.alert(JSON.stringify(search_rows));
}

I also want to exclude the Form sheet from the search, but that's not so important.

Thanks in advance


Solution

  • I got an working answer from a friend through email and I post the code here.

    `

      function searchString(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = ss.getActiveSheet();
      var search_string = sheet.getRange("G12").getValue();
      var textFinder = ss.createTextFinder(search_string);
      var search_rows = textFinder.findAll().map(r => ({Sheet: r.getSheet().getSheetName(), Code: r.getSheet().getRange(r.getRow(), 1).getValue()}));
      var ui = SpreadsheetApp.getUi();
      ui.alert(JSON.stringify(search_rows));
    }
    

    `

    Thanks for all the usefull code I find here.