How to fill a multiple string to filter a data in Google Apps Script

Question

I'm going to build web to be used in search for label information My database is in Google Sheets, so I went looking for a Google Sheets fetch and gave it a try. I have no experience with Google App Scripts so I want to fill all and search data form database but it can run just one of them how to do it all (first is .gs second is Index.html) this is my reference.

This is my database : https://docs.google.com/spreadsheets/d/1j-r4gD1zV3e0yrNJTnHezXRlv1w-aOf6UqQs_P2jsRY/edit?usp=sharing

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate()
  .setTitle('System Search Lable')
  .addMetaTag('viewport', 'width=device-width, inital-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}
 
function processForm(formObject){ 
  var concat = formObject.searchtext+formObject.searchtext2+formObject.searchtext3+formObject.searchtext4+formObject.searchtext5; 
  var result = "";
  if(concat){
      result = search(concat);
  }
  return result;
}

function search(searchtext){
  var spreadsheetId   = '1j-r4gD1zV3e0yrNJTnHezXRlv1w-aOf6UqQs_P2jsRY'; 
  var dataRange        = 'Master Barcode!A2:O10';  
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values;
  var ar = [];
data.forEach(function(f) {
    if (~f.toString().toLowerCase().indexOf(searchtext.toString().toLowerCase())) {
      ar.push(f);
    }
  });
  return ar;
}

!DOCTYPE html>

    
        
        
    
    
        
            

            
              
                  
                    
                      Search a Label

The results are here is that I fill in only one field. What I mean is I filled out the required information into each field, however I would like to know a way to fill in all fields in order to make it easier to filter.

user_19144104 · Answer

To filter results based on the values you enter in the web form, pass all search elements to your search function and then check that that they are all either blank or match. This assumes that the search terms and the columns in your sheet are in the same order. Example:

const MAPPING = [7, 10, 2, 13, 11];

function processForm({ searchtext, searchtext2, searchtext3, searchtext4, searchtext5 }){ 
  let searchTerms = [searchtext, searchtext2, searchtext3, searchtext4, searchtext5];
  var result = "";
  if(searchTerms.find(Boolean)){
      result = search(searchTerms);
  }
  return result;
}

function search(searchTerms){
  var spreadsheetId   = '1B0hcELJcoLxiYXNCDx9iRl8JxijwgdFBkte90oPfCnY'; 
  var dataRange        = 'Master Barcode!A46:O1346';  
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values;
  var ar = [];
  data.forEach(function(f) {
    if (searchTerms.every((e, i) => !e || e === f[MAPPING[i]-1])) {
      ar.push(f);
    }
  });
  return ar;
}

How to fill a multiple string to filter a data in Google Apps Script

1 Answers