SearchOnSteroids

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('SearchOnSteroids')
    .addItem('Exact match', 'searchOnSteroidsExactMatch')
    .addItem('Partial match', 'searchOnSteroids')
    .addToUi()
}

function searchOnSteroidsExactMatch() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ui = SpreadsheetApp.getUi()
  const activeSheet = ss.getActiveSheet().getName()
  const cellValue = ss.getActiveCell().getValue()
  const address = cellValue.replace(/\W/g, '').trim().toLocaleLowerCase()
  const sheets = ss.getSheets()

  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() != activeSheet) {
      const valuesRaw = sheets[i].getDataRange().getValues().flat()
      const values = valuesRaw.map(cell => {
        try {
          return cell.replace(/\W/g, '').trim().toLocaleLowerCase()
        } catch (err) {
          null
        }
      })

      if (values.includes(address)) {
        ui.alert(`${sheets[i].getName()} | ${cellValue}`)
        return
      }
    }
  }
  return ui.alert(`${cellValue} is not found`)
}

function searchOnSteroids() {
  const ss = SpreadsheetApp.getActiveSpreadsheet()
  const ui = SpreadsheetApp.getUi()
  const activeSheet = ss.getActiveSheet().getName()
  const cellValue = ss.getActiveCell().getValue()
  const address = cellValue.replace(/\W/g, '').trim().toLocaleLowerCase()
  const sheets = ss.getSheets()

  const output = []

  for (i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() != activeSheet) {
      const valuesRaw = sheets[i].getDataRange().getValues().flat()
      valuesRaw.forEach(cell => {
        try {
          const clean = cell.replace(/\W/g, '').trim().toLocaleLowerCase()
          const regex = new RegExp(address)
          if (regex.test(clean)) {
            output.push(`${sheets[i].getName()} | ${cell}`)
          }
        } catch (err) {
          null
        }
      })
    }
  }

  if (output.length == 0){
    ui.alert(`${cellValue} is not found`)
  } else {
    ui.alert(`Count: ${output.length}\n\n${output.join('\n')}`)
  }
}