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
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.