Advertisement
RemcoE33

SearchOnSteroids

Jun 26th, 2021
187
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function onOpen(e) {
  2.   SpreadsheetApp.getUi()
  3.     .createMenu('SearchOnSteroids')
  4.     .addItem('Exact match', 'searchOnSteroidsExactMatch')
  5.     .addItem('Partial match', 'searchOnSteroids')
  6.     .addToUi()
  7. }
  8.  
  9. function searchOnSteroidsExactMatch() {
  10.   const ss = SpreadsheetApp.getActiveSpreadsheet()
  11.   const ui = SpreadsheetApp.getUi()
  12.   const activeSheet = ss.getActiveSheet().getName()
  13.   const cellValue = ss.getActiveCell().getValue()
  14.   const address = cellValue.replace(/\W/g, '').trim().toLocaleLowerCase()
  15.   const sheets = ss.getSheets()
  16.  
  17.   for (i = 0; i < sheets.length; i++) {
  18.     if (sheets[i].getName() != activeSheet) {
  19.       const valuesRaw = sheets[i].getDataRange().getValues().flat()
  20.       const values = valuesRaw.map(cell => {
  21.         try {
  22.           return cell.replace(/\W/g, '').trim().toLocaleLowerCase()
  23.         } catch (err) {
  24.           null
  25.         }
  26.       })
  27.  
  28.       if (values.includes(address)) {
  29.         ui.alert(`${sheets[i].getName()} | ${cellValue}`)
  30.         return
  31.       }
  32.     }
  33.   }
  34.   return ui.alert(`${cellValue} is not found`)
  35. }
  36.  
  37. function searchOnSteroids() {
  38.   const ss = SpreadsheetApp.getActiveSpreadsheet()
  39.   const ui = SpreadsheetApp.getUi()
  40.   const activeSheet = ss.getActiveSheet().getName()
  41.   const cellValue = ss.getActiveCell().getValue()
  42.   const address = cellValue.replace(/\W/g, '').trim().toLocaleLowerCase()
  43.   const sheets = ss.getSheets()
  44.  
  45.   const output = []
  46.  
  47.   for (i = 0; i < sheets.length; i++) {
  48.     if (sheets[i].getName() != activeSheet) {
  49.       const valuesRaw = sheets[i].getDataRange().getValues().flat()
  50.       valuesRaw.forEach(cell => {
  51.         try {
  52.           const clean = cell.replace(/\W/g, '').trim().toLocaleLowerCase()
  53.           const regex = new RegExp(address)
  54.           if (regex.test(clean)) {
  55.             output.push(`${sheets[i].getName()} | ${cell}`)
  56.           }
  57.         } catch (err) {
  58.           null
  59.         }
  60.       })
  61.     }
  62.   }
  63.  
  64.   if (output.length == 0){
  65.     ui.alert(`${cellValue} is not found`)
  66.   } else {
  67.     ui.alert(`Count: ${output.length}\n\n${output.join('\n')}`)
  68.   }
  69. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement