google-sheetssearch-box

Search Box Google sheet

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

Solution

  • 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:

    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