title: Find value in spreadsheet using google script
body: <p>Situation:</p>
  
  <ol>
  <li>1 spreadsheet</li>
  <li>multiple sheets</li>
  <li>1 cell selected (may vary)</li>
  </ol>
  
  <p>What I'd like to do is to find and set focus to the next cell in any sheet that matches the selected cell (case insensitive) upon clicking a button-like image in the spreadsheet. Sort of like a custom index MS Word can create for you.</p>
  
  <p>My approach is:
  - set value of the selected cell as the variable (succeeded)
  - find the first cell that matches that variable (not the selected cell) (no success)
  - set value of found cell as variable2 (no success)
  - set the focus of spreadsheet to variable2 (no success)</p>
  
  <pre><code>function FindSetFocus() 
  
  {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var activecell = sheet.getActiveCell();
  var valueactivecell = activecell.getValue();
  
  //here comes the code :)
  
  }
  </code></pre>
  
  <p>I have found this snippet in the following topic, but I'm having a little trouble setting the input and doing something with the output: <a href="https://stackoverflow.com/questions/10807936/how-do-i-search-google-spreadsheets">How do I search Google Spreadsheets?</a></p>
  
  <p>I think I can replace 'value' with 'valueactivecell', but I don't know how to set the range to search through all sheets in the spreadsheet. Also, I'd like the output to be something I can set focus to using something like  'ss.setActiveSheet(sheet).setActiveSelection("D5");'</p>
  
  <pre><code>/**
   * Finds a value within a given range. 
   * @param value The value to find.
   * @param range The range to search in.
   * @return A range pointing to the first cell containing the value, 
   *     or null if not found.
   */
  function find(value, range) {
    var data = range.getValues();
    for (var i = 0; i &lt; data.length; i++) {
      for (var j = 0; j &lt; data[i].length; j++) {
        if (data[i][j] == value) {
          return range.getCell(i + 1, j + 1);
        }
      }
    }
    return null;
  }
  </code></pre>
  
  <p>also found this but no luck on getting it to work on the selected cell and setting focus: <a href="https://stackoverflow.com/questions/17196655/how-do-i-search-for-and-find-the-coordinates-of-a-row-in-google-spreadsheets?rq=1">How do I search for and find the coordinates of a row in Google Spreadsheets</a> best answer, first code.</p>
  
  <p>Please bear in mind that I'm not a pro coder :) If code samples are provided, please comment inline hehe.</p>
  
  <p>Thanks in advance for any help.</p>
  
  <p>Edit 24/10: Used the code from the answer below and edited it a bit. Now able to look through multiple sheets in a spreadsheet to find the value. The only problem with this bit is: My cell is highlighted yellow, but the cell with the value found isn't selected. See code below for hopping through sheets. I can't get my head around this :)</p>
  
  <pre><code>function SearchAndFind() {
  
  //determine value of selected cell
  var sh = SpreadsheetApp.getActiveSpreadsheet();
  var ss = sh.getActiveSheet();
  var cell = ss.getActiveCell();
  var value = cell.getValue();
  
  //create array with sheets in active spreadsheet
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  //loop through sheets to look for value
  for (var i in sheets) {
  
   //Set active cell to A1 on each sheet to start looking from there
    SpreadsheetApp.setActiveSheet(sheets[i])
    var sheet = sh.getActiveSheet();
    var range = sheet.getRange("A1");
    sheet.setActiveRange(range);
  
  //set variables to loop through data on each sheet
    var activeR = cell.getRow()-1;
    var activeC = cell.getColumn()-1;
    var data = sheets[i].getDataRange().getValues()
    var step = 0
  
  //loop through data on the sheet  
    for(var r=activeR;r&lt;data.length;++r){
      for(var c=activeC;c&lt;data[0].length;++c){
        step++
        Logger.log(step+' -- '+value+'  =  '+data[r][c]);
        if(data[r][c]==''||step==1){ continue };
        if(value.toString().toLowerCase()==data[r][c].toString().toLowerCase()){
           sheet.getRange(r+1,c+1).activate().setBackground('#ffff55');
          return;
        }
      }
    }
   }
  
  }
  </code></pre>