I have one main Google spreadsheet that has many sheets inside it, I am using a google script to search for specific data using name/password but I need to apply the search on all sheets in this main spreadsheet, please help me how to figure this out :
var sheetName = "Data"
var range = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getDataRange();
var data = range.getValues();
var ar = [];
I believe your goal as follows.
In this case, I would like to propose to use TextFinder. When TextFinder is used, the text can be searched from all sheets in a Google Spreadsheet. The sample script is as follows.
function myFunction() {
const spreadsheetId = "###"; // Please set Spreadsheet ID.
const searchText = "sample"; // Please set the seatch text.
const ss = SpreadsheetApp.openById(spreadsheetId);
const ranges = ss.createTextFinder(searchText).matchEntireCell(true).findAll();
const res = ranges.map(r => ({sheet: r.getSheet().getSheetName(), range: r.getA1Notation()}));
console.log(res)
}