Google-sheets – Fine tuning a find and replace code

google sheetsgoogle-apps-script

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

Best Answer

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

Related Solutions

Google-sheets – Auto-Find and Replace in Google Sheets with Scripts

If you highlight the column & press Ctrl-H you should be able to replace the string MKE how you want.

Before:

After:

Edit:
To auto fire this replacement:

In your sheet choose Tools|Script Editor from the menu & paste the code below which act on cells c1 to c10 on the sheet. The OnOpen method should fire when the sheet is opened, or can be fired directly from the script editor. I've not changed the UI to add the function to the menu, but that is also possible.

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("c1:c10");
  var to_replace = "MKE";
  var replace_with = "Milwaukee, WI";
  replaceInSheet(sheet,range, to_replace, replace_with);
}

function replaceInSheet(sheet, range, to_replace, replace_with) {
  //Confirm
  var ui = SpreadsheetApp.getUi(); 
  var spread = SpreadsheetApp.getActiveSpreadsheet();

  var result = ui.alert(
     "Will update " + to_replace + " to " + replace_with + " ",
     'Are you sure you want to continue?',
      ui.ButtonSet.YES_NO);

  // Process the user's response.
  if (result == ui.Button.YES) {

    // User clicked "Yes".
    spread.toast("Will update " + to_replace + " to " + replace_with + " ", "ALERT");

    var data  = range.getValues();

    var oldValue="";
    var newValue="";
    var cellsChanged = 0;

    for (var row=0; row<data.length; row++) {
      for (var item=0; item<data[row].length; item++) {
        oldValue = data[row][item];
        newValue = data[row][item].replace(to_replace, replace_with);
        if (oldValue!=newValue)
        {
          cellsChanged++;
          data[row][item] = newValue;
        }
      }
    }
    range.setValues(data);
    spread.toast(cellsChanged + " cells changed", "STATUS");
  }
  else {
    // User clicked "No" or X in the title bar.
    spread.toast("No action taken", "ABANDONED");
  }
}

Google-sheets – Find and replace Google Script

Here is a workaround, but in means two things:

you will need to insert a new column (but you can hide the one that has the decimal points)
It will convert the numbers into strings so you will not be able to use them for calculations (but they will look right). You will still be able to do any calculations you need using the original data (in the 'hidden' column)

Assuming that the number you want to reformat it in cell A1, then in another cell (e.g. B1), insert the following formula:

=text(A1,"00")&","&right(text(A1,"00.00"),2)

You can also copy and "Paste values only" (Ctrl+Shift+V) so that you can then remove the original column (if you don't need it for calculations)