I have this code that works to find the word “Yes” and replace it with a 1. How do I also add in a line for it to find “No” and replace it with a 0?

function fandr() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getActiveSheet();
  var r=s.getDataRange();
  var vlst=r.getValues();
  var i,j,a,find,repl;
  find="Yes"
  repl="1"
  for (i in vlst) {
    for (j in vlst[i]) {
      a=vlst[i][j];
      if (a==find) vlst[i][j]=repl;
    }
  }
  r.setValues(vlst);
}

I'll do you one better!

function fandr(params) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getDataRange();
  var vlst = r.getValues();
  var i,j,replace;

  for (i in vlst) {
    for (j in vlst[i]) {
      replace = params[vlst[i][j]];
      if(replace != undefined) {
        vlst[i][j] = replace;
      }
    }
  }

  r.setValues(vlst);
}

It's always best to write code in such away that the behavior can be changed in the future. This has been rewritten such that it can find and replace anything and everything you tell it to by passing in an object as the parameter.

You would call it using

fandr({"Yes":1, "No":0})

Alright, alright, I get it, that's not exactly what you were asking. The answer to your question is simple, use the "else if" statement

function fandr() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getDataRange();
  var vlst = r.getValues();
  var i,j, a;

  for (i in vlst) {
    for (j in vlst[i]) {
      a = vlst[i][j];
      if(a == "Yes") vlst[i][j] = 1;
      else if(a == "No") vlst[i][j] = 0;
    }
  }

  r.setValues(vlst);
}

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.