I am using this script and an input form to search a Google spreadsheet, this script has a range of one sheet,i.e. Data , but I need to extend my range to all sheets in the same spreadsheet, any ideas?
function doGet(e) {
return HtmlService.createTemplateFromFile("Index").evaluate()
.setTitle("WebApp: Search By Password")
.addMetaTag('viewport', 'width=device-width, initial-scale=1')
.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
/* PROCESS FORM */
function processForm(formObject) {
var concat = formObject.searchtext + formObject.searchtext2;
var result = "";
if (concat) {//Execute if form passes search text
result = search(concat);
}
return result;
}
//SEARCH FOR MATCHED CONTENTS ;
function search(searchtext) {
var spreadsheetId = ' '; //** CHANGE !!!!
var sheetName = "Data"
var range = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getDataRange();
var data = range.getValues();
var ar = [];
data.forEach(function (f) {
if (~[f[8]].indexOf(searchtext)) {
ar.push([f[2], f[3], f[4], f[5], f[6], f[7]]);
}
});
return ar;
}
I believe your goal is as follows.
In this case, I would like to propose modifying the function search as follows.
function search(searchtext) {
return SpreadsheetApp
.getActiveSpreadsheet()
.getSheets()
.flatMap(s =>
s
.getDataRange()
.getValues()
.filter(r => r[8] && r[8].includes(searchtext))
.map(([,,c,d,e,f,g,h]) => [c,d,e,f,g,h])
);
}