Search multiple sheets for a word

user_15572385 0
Viewed 228

Hello Guys
I have a sheet with multible sheets/pages where i would like to find a value that is stored in a cell. I trigger my script via a picture in a drawing.
In the example below i would like to search for the word Test. With my method i would need to but a picture on every site any search every site manually to find the word.

Word Number Other
Test 2 Hey

I am alredy able to find a value one 1 page and "dispaly" it, but i would like to do it on all off the other sheets.
Here is the Code that I use to find something on the same Page. 

function searchString(){
  var sheet = SpreadsheetApp.getActiveSheet();
  var search_string = sheet.getRange("J22:M23").getValue();
  var textFinder = sheet.createTextFinder(search_string);
  var search_row = textFinder.findNext().getRow();
  var ui = SpreadsheetApp.getUi();
  ui.alert("search row: " + search_row);
}

I would like it diplay the page it was found in and the row it is in, but i cant figure it out.
Thx for your help guys

google-apps-script google-sheets
2 Answers

I believe your goal as follows.

  • You want to search a text from all sheets in a Google Spreadsheet.
    • From var search_string = sheet.getRange("J22:M23").getValue(), the value of search_string is one text.

Modification points:

  • In your script, createTextFinder(findText) of Class Sheet is used. In this case, findText is searched from a sheet. In order to search findText from all sheets in a Google Spreadsheet, you can use createTextFinder(findText) of Class Spreadsheet.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please modify your script as follows.

function searchString(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var search_string = sheet.getRange("J22:M23").getValue();
  var textFinder = ss.createTextFinder(search_string);
  var search_rows = textFinder.findAll().map(r => ({sheetName: r.getSheet().getSheetName(), row: r.getRow()}));
  var ui = SpreadsheetApp.getUi();
  ui.alert("search row: " + JSON.stringify(search_rows));
}
  • In this modified script, as a sample output value, the sheet name and row number are shown.

References:

user_71086530

Based on this code:

function loopThroughSheets(cell) {
     //all sheets
     var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
     
     //loop
     for (var i = 0; i < sheets.length ; i++ ) {
          
          //current sheet and getting current value of cell
          var sheet = sheets[i];
          var val = sheet.getRange(cell).getValue();

          //do your job here     
     }
}
user_131566420
Related