HTML to Rich Text in Google Sheets (RichTextApp)

Question

Using RichTextApp https://github.com/tanaikech/RichTextApp#readme I've been able to convert HTML to Rich Text however this only works for 1 cell at the time, any idea how I could convert this to loop through every row in my spreadsheet?

function convertHTMLToRichText() {
  var ss = SpreadsheetApp.openById("###"); // Please set the Spreadsheet ID.
  var sheet = ss.getSheets()[0];

  // 1. Retrieve HTML from a cell.
  var html = sheet.getRange("A1").getValue();

  // 2. Create Google Document by converting HTML to Google Document as a temporal file.
  var blob = Utilities.newBlob(html, MimeType.HTML, "sample.html");
  var tempDocId = Drive.Files.insert(
    { title: "temp", mimeType: MimeType.GOOGLE_DOCS },
    blob
  ).id;

  // 3. Put the value to a cell as the rich text using the method of "DocumentToSpreadsheet".
  var res = RichTextApp.DocumentToSpreadsheet({
    range: sheet.getRange("A2"),
    document: DocumentApp.openById(tempDocId),
  });
  console.log(res);

  // 4. Remove the temporal file.
  DriveApp.getFileById(tempDocId).setTrashed(true);
}

user_14265469 · Answer

Here you go:

function html_from_B_to_rich_text_in_D() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getRange('B1:B' + sheet.getLastRow());
  var destination_column = 3; // column D
  var values = range.getValues();
  var html_table = '' 
    + values.flat().join('
')
    + '';

  var resource = { requests: [
    { pasteData: { 
      html: true, 
      data: html_table,
      coordinate: { 
        sheetId: sheet.getSheetId(),
        columnIndex: destination_column,
        } 
      }
    }
  ]};
  Sheets.Spreadsheets.batchUpdate(resource, ss.getId());

}

The code makes a html table from the cells of column B and put it as a rich text into column D via batchUpdate().

To use this script you have to add Google Sheets API in Script Editor's Services.

Unfortunately it has quite limited abilities to parse html tags:

HTML to Rich Text in Google Sheets (RichTextApp)

1 Answers