Replacing Line Breaks in Google Sheets – How to Replace Multiple Line Breaks in a Cell

google sheetsgoogle-apps-script

I have this script that replaces line breaks for a comma and space and triggered by edit event. However, it only replaces one line break per edit event. My sheet is populated by a form (not Google Forms) and in three columns I always receive up to four line breaks in cells of columns M, N and O (result of a multiple-choice fields in the form).

function testReplaceInSheet(){
var sheet = SpreadsheetApp.getActiveSheet()
var range = sheet.getRange("M2:O"); 
replaceInSheet(sheet,'values','test');
}    

function replaceInSheet(sheet, to_replace, replace_with) {    

//get the current data range values as an array
var values = sheet.getRange("M2:O").getValues();
//loop over the rows in the array
for(var row in values){

//use Array.map to execute a replace call on each of the cells in the row.
var replaced_values = values[row].map(function(original_value){
  return original_value.toString().replace("\n",", ");
});

//replace the original row values with the replaced values
values[row] = replaced_values;
}
//write the updated values to the sheet
sheet.getRange("M2:O").setValues(values);
}

With this code I have to edit my sheet every time I need to remove a line break so it only replaces one line break at a time. How do I replace this:
COLUMN M, N, O:

word1
word2
word3
word4

into this:

word1, word2, word3, word4

without having to edit my sheet three times?

I already set the trigger in three ways: "on edit", "on change", and "on form submission" but that does not remove all line breaks after form submission.

Best Answer

pnuts already pointed out the solution; use global flag "g" for global string replacement, replace(/\n/g, ",").

Your script also has a side effect of converting everything to strings. What if the user entered a number or a date? I would do 

return (original_value.replace ? original_value.replace(/\n/g, ",") : original_value);

This performs the replacement if it makes sense (on data types that have .replace method, i.e., strings) and otherwise leaves data unchanged.

Also, consider using Event Objects which allow triggered scripts to act only on the range that was actually modified, instead of the entire data set. As is, your script re-runs over the old cells repeatedly. Along these lines, refocus the script on range instead of sheet as follows:

function triggeredFunction(e) {
  var range = e.range;  // or var range = sheet.getRange("M2:O") if not using event object
  replaceInRange(range, 'values', 'test');
}  

function replaceInRange(range, to_replace, replace_with) {    
   var values = range.getValues();
   newValues = values.map(function(row) {
     return row.map(function(originalValue) {
       return (originalValue.replace ? originalValue.replace(/\n/g, ",") : originalValue);
     });
   });
   range.setValues(newValues);
}

This is cleaner in several ways:

  • The edited range is not hardcoded in the replacing function, it's received as a parameter.
  • Even if the edited range is hardcoded in triggeredFunction, that's only one place where "M2:O" appears. So if the columns change, only one line of code will need a revision.
  • The array iteration is consistently .map instead of a mix of map and for-loop.
  • camelCase is used consistently
Related Topic