1

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.

1

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
  • Your first suggestion worked like a charm Michelle!!! Thanks so much! your second suggestion gives me error: TypeError: Cannot read property "range" from undefined. (line 2, file "linebreak") – Johny Stark Sep 16 '17 at 19:04
  • That's because the function doesn't get the event object unless it was launched by a trigger. If you try to run "triggeredFunction" manually, e is undefined so it won't work. – user135384 Sep 16 '17 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.