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:

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".
Alternative to TEXTJOIN where the word "Not Answered" is ignored.
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");
  }
}

Welcome. Please edit your question to include the code that you have tried. It's important to show your own efforts - partly so that you get feedback and partly so that you show you've made an effort, and don't just want the code written for you.. — Tedinoz, Sep 15 '19 at 06:01
"Or if it is possible to delete this option in SurveyMonkey?" If its a "required question, then the user should have no option but to answer. If it's not a required question, then surely you want to know that you user did NOT answer.the question. — Tedinoz, Sep 15 '19 at 06:09
Have you read [Auto-Find and Replace in Google Sheets with Scripts](https://webapps.stackexchange.com/q/104157/196152), or [How do I replace text in a spreadsheet with Google Apps Script?](https://stackoverflow.com/q/26480857/1330560)? Perhaps you might care to google "google sheets script find and replace" — Tedinoz, Sep 15 '19 at 06:17
"So my TextJoin will work" I am sure you know what you mean by this reference, but it is not at all clear to anyone reading the question. May I respectfully suggest that you edit your question in order to explain **exactly** what you are trying to achieve. — Tedinoz, Sep 15 '19 at 06:20

Cooper · Accepted Answer · 2019-09-15T23:20:34.137

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

edited Sep 15 '19 at 23:20

answered Sep 15 '19 at 18:29

Cooper

59,616
6
23
54

score 0 · Answer 2 · answered Sep 15 '19 at 23:05

I needed a trigger so this is how I solved the problem. If there is a simpler functional solution write it down. Thank you Cooper.

function onOpen() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  var rg=sh.getDataRange();
  var tf=sh.createTextFinder('Not Answered').findAll();
  replaceNotAnswerWithBlank(ss, sh, rg, tf);
}

function replaceNotAnswerWithBlank(ss, sh, rg, tf) {
  for(var i=0;i<tf.length;i++) {
    sh.getRange(tf[i].getRow(),tf[i].getColumn()).setValue('');
  }
}

Automatically Find&Replace

2 Answers2