Automatically Find&Replace

Question

I work with surveymonkey and when someone doesn't answer my question, my cell is filled with "Not Answered". The goal is to merge multiple rows into one. Possible solutions are:

1. I need a script to automatically find and replace. Which will replace (more precisely delete "Not Answered") with an empty cell. So my TEXTJOIN will work. I've tried some code but it doesn't work for the whole sheet. The problem with the TEXTJOIN function (delimiter, ignore_empty, text1, [text2, ...]) is that the second parameter igrone_empty is boolean and only works with TRUE or FALSE and cannot write "Not Answered" there. So with this I have to find another solution to ignore or delete the word "Not Answered".

2. Alternative to TEXTJOIN where the word "Not Answered" is ignored.

3. Or if it is possible to delete this option in SurveyMonkey?

I have tried this solution but doesn't work for entire sheet/table.

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

Answer 1

Try this:

function replaceNotAnswerWithBlank(e) {
  var ss=e.source;
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var tf=sh.createTextFinder('Not Answered').findAll();
  for(var i=0;i<tf.length;i++) {
    sh.getRange(tf[i].getRow(),tf[i].getColumn()).setValue('');
  }
}

How about this:

function onOpen(e) {
  replaceNotAnswerWithBlank(e);
}