I have created a CRUD webApp based on Googlesheet. I'm getting duplicate search results when I try to search through multiple rows and columns of the html datatable in the web App. This happens when search test matches data in the multiple columns of the same row. How can I get it to display only unique results? How can alter my code to search through only first 3~4 columns?

When I try searching for Test ( Product) it lists 4 rows, but it should just return 1 row.

Attached snippet.

.gs code

function searchData(formObject){  
      var result = [];
      if(formObject.searchtext){//Execute if form passes search text
          var data = Sheets.Spreadsheets.Values.get(globalVariables().spreadsheetId, globalVariables().dataRange).values;
          for(var i=0;i<data.length;i  ){
            for(var j=0;j<data[i].length;j  ){
              if(data[i][j].toLowerCase().search(formObject.searchtext.toLowerCase())!=-1){
                result.push(data[i])
              }
            }
          }
      }
      return result;
    }

.js code

    function handleSearchForm(formObject) {
    google.script.run.withSuccessHandler(createTable).searchData(formObject);
    document.getElementById("search-form").reset();
  }

    <!-- SEARCH FORM-->
<form id="search-form" class="form-inline" onsubmit="handleSearchForm(this)">
  <div class="form-group mx-sm-3 mb-2">
    <label for="searchtext" class="sr-only">Search Text</label>
    <input type="search" class="form-control form-control-sm" id="searchtext" name="searchtext" placeholder="Search">
  </div>
  <button type="submit" class="btn btn-sm btn-primary mb-2">Search</button>

I have tried doing this, but it did not help.

function searchData(formObject){  
      var result = [];
      if(formObject.searchtext){//Execute if form passes search text
          var data = Sheets.Spreadsheets.Values.get(globalVariables().spreadsheetId, globalVariables().dataRange).values;
          for(var i=0;i<data.length;i  ){
              const COLUMN_INDEX = 4;
              if(data[i][COLUMN_INDEX].toLowerCase().search(formObject.searchtext.toLowerCase())!=-1){
                result.push(data[i])
              }
          }
      }
      return result;
    }

CodePudding user response：

From only first 3~4 columns in your question, I understood that you want to search the text from the columns "C" and "D". In this case, how about the following modified script? In this modification, your Google Apps Script is modified.

Modified script 1:

When your script is modified, it becomes as follows.

function searchData(formObject){ 
  const COLUMN_INDEXES = [2, 3]; // 2 and 3 means the columns "C" and "D".
  var result = [];
  if (formObject.searchtext) {
    var data = Sheets.Spreadsheets.Values.get(globalVariables().spreadsheetId, globalVariables().dataRange).values;
    for (var i = 0; i < data.length; i  ) {
      if (COLUMN_INDEXES.some(e => data[i][e].toLowerCase().includes(formObject.searchtext.toLowerCase()))) {
        result.push(data[i]);
      }
    }
  }
  return result;
}

• About const COLUMN_INDEX = 4; in your script, in this case, the column "E" is used. When you want to check the columns "C" and "D", please use the indexes of 2 and 3.

• If you want to check only the column "D", please modify const COLUMN_INDEXES = [2, 3] to const COLUMN_INDEXES = [3].

Modified script 2:

In this modification, Sheets API is not used. Please set your sheet name and Spreadsheet ID.

function searchData(formObject) {
  const sheetName = "Sheet1"; // Please set the sheet name.
  const spreadsheetId = "###"; // Please set the Spreadsheet ID.

  const searchText = formObject.searchtext.toLowerCase();
  const result = SpreadsheetApp
    .openById(spreadsheetId)
    .getSheetByName(sheetName)
    .getDataRange()
    .getValues()
    .filter(r => [r[2], r[3]].some(c => c.toLowerCase().includes(searchText)));
  return result;
}

• In this modified script, searchText is searched from the columns "C" and "D". When the value of searchText is included in the values of columns "C" and "D", the row is retrieved.

• In this script, getDataRange is used. If you want to search the values from other range, please modify the range of above script.

• If you want to check only the column "D", please modify [r[2], r[3]].some(c => c.toLowerCase().includes(searchText))) to [r[3]].some(c => c.toLowerCase().includes(searchText))).

Note:

• When you modified the Google Apps Script, please modify the deployment as a new version. By this, the modified script is reflected in Web Apps. Please be careful this.
• You can see the detail of this in the report of "Redeploying Web Apps without Changing URL of Web Apps for new IDE".

References:

• filter()
• some()