title: Replace certain cell values in a column
body: <p><strong>Disclaimer</strong>: I am Newb. I understand scripting a little, but writing it is a pain for me, mostly with loops and arrays, hence the following.</p>
  
  <p>I am attempting to pull all of the data from a specific column (in this case H [8]), check each cell's value in that column and if it is a y, change it to Yes; if it's n, change it to No; if it's empty, leave it alone and move onto the next cell.</p>
  
  <p>Here's what I have so far. As usual, I believe I'm pretty close, but I can't set the value of the active cell and I can't see where I'm messing it up. At one point I actually changed ever value to Yes in the column (<em>so thankful for undo in these cases</em>).</p>
  
  <p><strong>Example of Sheet</strong>:</p>
  
  <pre><code>..... COL-H  
  r1... [service] &lt;-- header  
  r2... y  
  r3... y  
  r4... n  
  r5... _  &lt;-- empty  
  r6... y  
  </code></pre>
  
  <p><strong>Intent:</strong> Change all y's to Yes and all n's to No (skip blank cells).</p>
  
  <p><strong>What I've tried so far</strong>:</p>
  
  <p>Function attempt 1</p>
  
  <pre><code>function Thing1() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mySheet");
    var lrow = ss.getLastRow();
    var rng = ss.getRange(2, 8, lrow - 1, 1);
    var data = rng.getValues();
  
    for (var i=0; i &lt; data.length; i++) {
      if (data[i][0] == "y") {
        data[i][0] == "Yes";
      }
    }
  }
  </code></pre>
  
  <p>Function attempt 2</p>
  
  <pre><code>function Thing2() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mySheet");
    var lrow = ss.getLastRow();
    var rng = ss.getRange(2, 8, lrow - 1, 1);
    var data = rng.getValues();
  
    for (var i=0; i &lt; data.length; i++) {
      if (data[i][0] == "n") {
        data.setValue("No");
      } else if (data[i][0] == "y") {
        data.setValue("Yes");
      }
    }
  }
  </code></pre>
  
  <p><strong>Usage</strong>:</p>
  
  <p>Once I'm done here, I want to modify the function so that I can target any column and change one value to another (I already have a method for that, but I need to be able to set the value). It would be like so: =replace(sheet, col, orig_value, new_value). I will post it as well below.</p>
  
  <p>Thanks in advance for the help.</p>
  
  <hr>
  
  <p><strong>Completed Code for searching and replacing within a column</strong></p>
  
  <pre><code>function replace(sheet, col, origV1, newV1, origV2, newV2) {
    // What is the name of the sheet and numeric value of the column you want to search?
    var sheet = Browser.inputBox('Enter the target sheet name:');
    var col = Browser.inputBox('Enter the numeric value of the column you\'re searching thru');
  
    // Add old and new targets to change (Instance 1):
    var origV1 = Browser.inputBox('[Instance 1:] What old value do you want to replace?');
    var newV1 = Browser.inputBox('[Instance 1:] What new value is replacing the old?');
  
    // Optional - Add old and new targets to change (Instance 2):
    var origV2 = Browser.inputBox('[Instance 2:] What old value do you want to replace?');
    var newV2 = Browser.inputBox('[Instance 2:] What new value is replacing the old?');
  
    // Code to search and replace data within the column
    var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet);
    var lrow = ss.getLastRow();
    var rng = ss.getRange(2, col, lrow - 1, 1);
    var data = rng.getValues();
  
    for (var i=0; i &lt; data.length; i++) {
      if (data[i][0] == origV1) {
        data[i][0] = newV1;
      } else if (data[i][0] == origV2) {
        data[i][0] = newV2;
      }
    }
    rng.setValues(data);
  }
  </code></pre>
  
  <p>Hope this helps someone out there. Thanks Again @ScampMichael!</p>