2

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);
}

1 Answer 1

1

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 agree to our terms of service and acknowledge you have read our privacy policy.

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