Replace multiple occurrences in String on Google Spreadsheet

So I have a spreadsheet up for the purpose of documenting descriptions of functions found in some python files. TLDR these descriptions are hard to read due to the clutter left over from the files.

So my solution to solve this was:

function onEdit(e) {

  const desFix = ['"', '
'];
  let activeSheet = e.source.getActiveSheet();
  let range = e.range;
  const desc = range.getValue();
  const rdesc = desc.toString();
  
  for (let i=0; i<desFix.length; i++){
  
    const rep = rdesc.replace(desFix[i]," ");
    range.setValue(rep);
  }  
}

But it only works on the first occurrence when I need it to happen multiple times. Everything I’ve found and tried to implement/translate over to spreadsheet api breaks. Any idea of what I need to do to make it run multiple times?

Answer

I believe your goal as follows.

• You want to convert the values of " and to " " in the active range in the Google Spreadsheet.
• You want to run the script using the OnEdit trigger.

Modification points:

• In your script, the same rdesc is used by rdesc.replace(desFix[i]," ") in the for loop. By this, only the 1st at 2nd loop is replaced. I think that this is the reason of your issue.
• And, I think that setValue is used in the for loop, the process cost will be high.
• In your case, I thought that TextFinder might be suitable.

So in this answer, I would like to suggest to modify your script using TextFinder. When your script is modified using TextFinder it becomes as follows.

Modified script:

function onEdit(e) {
  const desFix = ['"', '
'];
  desFix.forEach(f => e.range.createTextFinder(f).matchCase(true).replaceAllWith(" "));
}

• When you use this, for example, please edit a cell. By this, the script is run by OnEdit trigger and " and in the value in the cell are replaced to " ".

Note:

• When you want to run the script with the script editor, you can also use the following script. When you use the following script, please run myFunction() at the script editor. By this, all cell values in the active sheet are checked.

    function myFunction() {
      const desFix = ['"', '
  '];
      const sheet = SpreadsheetApp.getActiveSheet();
      desFix.forEach(f => sheet.createTextFinder(f).matchCase(true).replaceAllWith(" "));
    }
  

References:

• Class TextFinder
• google-apps-scropt
  • I thought that these links might be useful.