Search code examples
regexgoogle-sheetsgoogle-sheets-formulaline-breaksarray-formulas

Include line breaks in Google Sheets cells when getting form responses from Google Forms

I am wondering if anyone can help with this. I am creating a merged document using HTTP Post via Infusionsoft from a Google Form Response. The HTTP Post automatically pulls the data from Infusionsoft and posts it to the Google Form which then adds it to a Google sheet. I am then using Autocrat to automatically create a letter.

I have managed to make all this work, however, the one issue that I am having is that some of the form entries are paragraph text (so, for instance, the body of the letter, which has several paragraphs). When I pull this data into the sheet once the HTTP post has fired, the text within that cell separates the paragraphs with <br><br>. So, for example, it would be:

"Paragraph one.<br><br>
 Paragraph two.<br><br>" etc.

This then merges into the letter with the <br><br> rather than line breaks.

I want it to appear within the merged letter as:

"Paragraph one.

Paragraph two."

within the cell. Ie with line breaks.

Is this possible? Have found another post with this function, but this is the opposite of what I want to achieve.

function lineBreakTest() {
  var cellWithLineBreaks = SpreadsheetApp.getActiveSheet().getRange("q3").getValue();
  Logger.log(cellWithLineBreaks);

  cellWithLineBreaks = cellWithLineBreaks.replace(/\n/g, '<br>');

  Logger.log(cellWithLineBreaks);

  // Post to your Google Site here. Logger.log is just used to demonstrate.

}

I would also want it to apply to the whole column, so whenever autocrat runs and a new row is added, it would apply the same function.

Would this all happen automatically also?

Any help would be amazing.

Solution

  • see this solution if it fits you:

    =ARRAYFORMULA(REGEXREPLACE(A:B, "<br><br>", CHAR(10)&CHAR(10)))

    0

    ...then you can just hide A:B columns

    0