Search Box Google sheet

user_15565411 0
Viewed 1264

I want to make search box in google sheet.it should be work as slicer. Table of data need to filter using that search box.enter image description here

I want to create search box as this excel search box using Google sheet. enter image description here

search-box google-sheets
2 Answers

here is a mockup https://docs.google.com/spreadsheets/d/139PfSbmEEGt-1kXZAO5mdBJ73vhnliNXNL51R7T1wOI/edit?usp=sharing

function onEdit(e) {
  var sh = e.source.getActiveSheet()
  var cel = e.source.getActiveRange();
  var crit = [];
  var count = 0;
  if (cel.getRow()==1){
    try{
      sh.getFilter().remove();
    }
    catch(e){
    }
    for (var col=1;col<=sh.getLastColumn();col++){
      if (!sh.getRange(1,col).getValue() && sh.getRange(2,col).getValue() != ''){
        crit.push([sh.getRange(2,col).getValue()])
        count++
      }
    }
  }
  if (count != 0){
    var filter = sh.getRange('A4:J').createFilter();
      var criteria = SpreadsheetApp.newFilterCriteria()
        .setHiddenValues(crit)
        .build();
    filter.setColumnFilterCriteria(1, criteria);
  }
};

In A2 :

=transpose(unique(A5:A))

could probably be improved ! enter image description here

user_150193800

Here is what I've done:

Step 1: Define the filters

You already did it in the picture so I'm not explaining how it's done. Simply define the filter in the sheet.

Step 2: Let's make the trigger

Before starting you need to note 2 things: the name of the sheet that has the search box, and the cell that does the search.

Then go to Tools > Script editor and paste the following code:

const SHEET_NAME = 'Sheet1'
const SEARCH_CELLS = ['E10', 'E11', null]
function onEdit(e) {
  // Get edition range and sheet
  const { range } = e
  const sheet = range.getSheet()

  // If we are not in the correct cell, return
  if(sheet.getName() !== SHEET_NAME) return

  // Get the filter to be able to control it
  const filter = sheet.getFilter()

  // Number of columns to check
  const nCols = Math.min(SEARCH_CELLS.length, filter.getRange().getWidth())
  for (let i = 0; i < nCols; i++) {
    // Get the cell as A1 notation
    const cell = SEARCH_CELLS[i]
    
    // Ignore column if it has no cell with the value
    if (!cell) continue
  
    // Get the range used to search
    const searchRange = sheet.getRange(cell)
    const row = searchRange.getRow()
    const col = searchRange.getColumn()

    // If the search cell was edited set the value
    // This checks if the point (row, col) is in the box defined by the edited cells range
    if(range.getRow() <= row && range.getLastRow() >= row && range.getColumn() <= col && range.getLastColumn() >= col) {
      updateFilter(sheet, filter, i, searchRange.getValue())
    }
  }
}

function updateFilter(sheet, filter, columnOffset, value) {
  // Get the range of the values of the filter
  const filterRange = filter.getRange()
  
  // Get the column that we are filtering
  const column = filterRange.getColumn() + columnOffset

  if (value) {
    // Get all values except the selected value
    // Change this to change the logic of what to hide
    const hiddenValues = sheet.getRange(filterRange.getRow(), column, filterRange.getHeight(), 1)
      .getValues()
      .flat()
      .filter(v => v != value) // notice the non-strict equality

    // Set the criteria to hide them all
    const criteria = SpreadsheetApp.newFilterCriteria()
      .setHiddenValues(hiddenValues)
      .build()
    filter.setColumnFilterCriteria(column, criteria)
  } else {
    // If there is not value, hide nothing
    filter.removeColumnFilterCriteria(column)
  }
}

Then change the constants on the top:

  • The name of the sheet
  • The list of all the search cell's A1 notation. The list should be in order, that is, the first cell is for the first column, the second cell for the second, and so on ans so forth. Set it to null if you don't want to search for that column.

I tried adding comments explaining step by step but feel free to ask if something is not clear or confusing.

(Optional) Step 3: Add a dropdown the the search cell

If you want a drop down on the search cell that autocompletes and allows you to select it, do this (simple) step.

Select the search cell and right click it. Go to Data validation. In Criteria select List from range and add the range of all the values of the first column of the filter. Click Save and you did it.

References

user_147798040
Related