HtmlService Table Not Supporting Line Breaks And Hyperlinks From Spreadsheet

I am writing Google Apps Script to display data from spreadsheet into HTML table using HtmlService. But I faced below two challenges: If spreadsheet cell value has line breaks int

Solution 1:

Assuming that you have a variable result that contains the text from a spreadsheet cell, you can replicate the line breaks for HTML by replacing them with <br> tags.

result = result.replace(/\n/g,"<br>");

In my own SheetConverter library, I also replace spaces with non-breaking spaces and encode < symbols to avoid having cell contents interpreted as HTML.

result = result.replace(/ /g,"&nbsp;").replace(/</g,"&lt;").replace(/\n/g,"<br>");

Part 2 of your question isn't readily answerable, in part because there are multiple ways to express a URL in spreadsheets.

• If a cell contains a HYPERLINK() function, you can use Range.getFormula() to retrieve the formula, and get the URL and displayed text from that. However, the URL may be another formula or cell reference, so you could still end up with a non-viable link.

• If a cell contains text that Google Sheets interprets as a URL or Email address, it will be marked as a link in the Sheets GUI, but neither the resulting formatting nor the HREF are available to Google Apps Script.

Feel free to examine the SheetConverter source and take ideas from it, or just use it as a library to simply your efforts.

Solution 2:

A simple solution to show line breaks/spaces
use the html tag <pre>Data retreived here</pre>
You can then apply a css class to the pre element to change the predefined font.

The HTML Preformatted Text (<pre>) represents preformatted text. Text within this element is typically displayed in a non-proportional font exactly as it is laid out in the file. Whitespaces inside this element are displayed as typed.