This is a GAS library for copying the rich text with the text styles from Google Document to Google Spreadsheet or from Google Spreadsheet to Google Document using Google Apps Script (GAS). And, also the rich texts in the cells can be converted to HTML format. Furthermore, the text length to fit in the cell width can be automatically adjusted.

Google Spreadsheet can use the rich text as the cell value. But I thought that it is difficult for me to directly edit the rich text in a cell. So I wanted to copy the rich text, that I edited at the Google Document, to the cell of Google Spreadsheet. But, unfortunately, when the rich text in Google Document is manually copied to a cell in Google Spreadsheet, the text style is removed. By this, only text values are copied. It seemed that this was the current specification. So in order to achieve above, I created this as a library.

This library can do as follows.

1. Rich text in Google Document can be copied to the cell of Google Spreadsheet with keeping the text style.

2. Rich text in the cell of Google Spreadsheet can be copied to Google Document with keeping the text style.

3. Rich texts in the cells can be converted to HTML format.

4. Text length to fit in the cell width can be automatically adjusted by changing the font size.

In the current stage, the rich text of Google Spreadsheet can use the following text styles. Ref1, Ref2

• Font family of the text
• Font size of the text in points
• Font color of the text
• bold
• italic
• strikethrough
• underlined

1Ka6RcIG6G9P8AhkJtjy1DAnHk1_rShFPWtdCQ2bxwMsrRE8cfn0cDXBv

In order to use this library, please install this library as follows.

1. Create a GAS project.

   • You can use this library for the GAS project of both the standalone type and the container-bound script type.

2. Install this library.

   • Library's project key is 1Ka6RcIG6G9P8AhkJtjy1DAnHk1_rShFPWtdCQ2bxwMsrRE8cfn0cDXBv.

This library uses V8 runtime. So please enable V8 at the script editor.

This library use the scope of https://www.googleapis.com/auth/spreadsheets.

But in the following sample script, https://www.googleapis.com/auth/documents is also used.

In this method, the text in Google Document can be put to the cell of Google Spreadsheet with keeping the text style.

function DocumentToSpreadsheet() {
  // From
  var doc = DocumentApp.openById("###");

  // To
  var ss = SpreadsheetApp.openById("###");
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A1");

  var res = RichTextApp.DocumentToSpreadsheet({ range: range, document: doc });
  console.log(res);
}

In this sample script, the text of Document is put to the cell "A1" of the 1st sheet in the Spreadsheet as the rich text.

In this method, the text in the cell of Google Spreadsheet can be put to the Google Document with keeping the text style.

function SpreadsheetToDocument() {
  // From
  var ss = SpreadsheetApp.openById("###");
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A1");

  // To
  var doc = DocumentApp.openById("###");

  var res = RichTextApp.SpreadsheetToDocument({ range: range, document: doc });
  console.log(res);
}

In this sample script, the text of the cell "A1" of the 1st sheet in the Spreadsheet is put to the Document with the text style with the append method.

In this method, the rich texts in the cells on Google Spreadsheet are converted to the HTML format.

function RichTextToHTMLForSpreadsheet() {
  var ss = SpreadsheetApp.openById("###");
  var sheet = ss.getSheets()[0];
  var range = sheet.getRange("A1:A2");
  var res = RichTextApp.RichTextToHTMLForSpreadsheet({ range: range });
  console.log(res);
}

• When the value of cell on Spreadsheet in above demonstration image is converted to HTML format using this method, it can obtain this result. https://jsfiddle.net/7e3mc10p/
• When the range is only one cell, the string value of the HTML format is returned.
• When the range is the multiple cells, the 2 dimensional array including the HTML format is returned.
• This method was answered for this thread.

In this method, the text length to fit in the cell width can be automatically adjusted by changing the font size.

function AutoResizeFontForSpreadsheet() {
  const ss = SpreadsheetApp.openById("###");
  const sheet = ss.getSheetByName("Sheet1");
  const object = {
    range: sheet.getRange("A1:A6"),
    toLarge: true,
  };
  const res = RichTextApp.AutoResizeFontForSpreadsheet(object);
  console.log(res);
}

In this demonstration, the lengths of texts in the cells "A1:A6" are matched to the cell width by changing the font size.

Above case, toLarge is true.

Above case, toLarge is false.

In this method, the range on Google Spreadsheet is converted to a HTML table. Using this method, for example, you can send the specific range in the Spreadsheet as an email by including a HTML table.

function RangeToHTMLTableForSpreadsheet() {
  const ss = SpreadsheetApp.openById("###");
  const sheet = ss.getSheetByName("Sheet1");
  const object = { range: sheet.getRange("B6:D9") };
  const htmlTable = RichTextApp.RangeToHTMLTableForSpreadsheet(object);
  console.log(htmlTable);
}

• In this case, when backgroundColor: false is used in object as follows, the HTML table doesn't include the background color of the cells. The default value is true. So when const object = { range: sheet.getRange("B6:D9") }; is used, the background color of cells are included in the HTML table.

  const object = {
    range: sheet.getRange("B6:D9"),
    backgroundColor: false,
  };

When you use the following script using the above response value, the converted HTML table can be also converted to a PDF file using the following script.

// htmlTable is from the above script.
const pdf = Utilities.newBlob(htmlTable, MimeType.HTML).getAs(MimeType.PDF);
DriveApp.createFile(pdf);

When this method is used, the following result can be obtained.

The sample Spreadsheet is as follows.

When the above sample script is used for this method, you can retrieve the following result.

You can also see the output HTML table at https://jsfiddle.net/oq9x458e/.

In this method, the text in a cell is converted to the richtext.

function ReplaceTextToRichText() {
  const sheet = SpreadsheetApp.openById("###").getSheetByName("Sheet1"); // Please set the sheet name.

  const range1 = sheet.getRange("A1:A2");
  const texts1 = ["sample2", "sample5"];
  const textStyle1 = SpreadsheetApp.newTextStyle()
    .setBold(true)
    .setForegroundColor("red")
    .build();

  const range2 = sheet.getRange("B1:B2");
  const texts2 = ["sample1", "sample3"];
  const textStyle2 = SpreadsheetApp.newTextStyle()
    .setItalic(true)
    .setBold(true)
    .setForegroundColor("green")
    .build();

  const object = {
    replaceTextToRichText: [
      { range: range1, texts: texts1, textStyle: textStyle1 },
      { range: range2, texts: texts2, textStyle: textStyle2 },
    ],
  };
  const res = RichTextApp_test.ReplaceTextToRichText(object);
}

• In this method, the object is required to be like {replaceTextToRichText: [{ range: range1, texts: texts1, textStyle: textStyle1 },,,]}. Please use the key of replaceTextToRichText. Please be careful this.
• About each element of replaceTextToRichText,
  • range : Range object of the cells you want to use this library.
  • texts : This is an array. Set the texts you want to change to richtext.
  • textStyle : TextStyle object. Ref

When the above sample script is used, the following situation is obtained.

As an additional information, when the following script is run using the above sample input situation, the following result is obtained.

function ReplaceTextToRichText() {
  const sheet = SpreadsheetApp.openById("###").getSheetByName("Sheet1"); // Please set the sheet name.

  const range1 = sheet.getRange("A1:A2");
  const texts1 = ["sample2", "sample5"];
  const textStyle1 = SpreadsheetApp.newTextStyle()
    .setBold(true)
    .setForegroundColor("red")
    .build();

  const range2 = sheet.getRange("A1:A2");
  const texts2 = ["sample1", "sample3"];
  const textStyle2 = SpreadsheetApp.newTextStyle()
    .setItalic(true)
    .setBold(true)
    .setForegroundColor("green")
    .build();

  const object = {
    replaceTextToRichText: [
      { range: range1, texts: texts1, textStyle: textStyle1 },
      { range: range2, texts: texts2, textStyle: textStyle2 },
    ],
  };
  const res = RichTextApp_test.ReplaceTextToRichText(object);
}

In this method, the existing richtext data is kept. So, you can add new text styles to the cell.

When this library is used, the HTML data in a cell on Google Spreadsheet can be converted to the rich text and put to the cell. The flow of this is as follows.

1. Retrieve HTML from a cell.
2. Create Google Document by converting HTML to Google Document as a temporal file.
   • In this case, Drive API is used.
3. Put the value to a cell as the rich text using the method of "DocumentToSpreadsheet".
4. Remove the temporal file.

In this sample script, it supposes that the HTML data is put to a cell "A1" of the 1st tab on Google Spreadsheet, and the converted rich text is put to the cell "A2".

In this sample script, Drive API of Advanced Google services is used. So before you use this, please enable Drive API at Advanced Google services.

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);
}

• As an important point, when the HTML data is converted to Google Document, there is the case that the font family is changed. I think that this might be the current specification of Drive API. So please be careful this.

As the limitation, in the current stage, the table, list and images cannot be used with the rich text of Google Spreadsheet. So please use only the texts with the text style.

MIT

Tanaike

If you have any questions and commissions for me, feel free to tell me.

• v1.0.0 (February 19, 2020)

  1. Initial release.

• v1.1.0 (June 16, 2020)

  1. Add new method of RichTextToHTMLForSpreadsheet. The method of RichTextToHTMLForSpreadsheet can convert the rich texts in the cells to the HTML format.

• v1.1.1 (June 16, 2020)

  1. About the method of RichTextToHTMLForSpreadsheet, I forgot to convert hyperlinks to HTML. This was modified.

• v1.1.2 (June 16, 2020)

  1. When one row and several columns are used as the range, only 1st column is returned. This bug was removed.

• v1.1.3 (June 17, 2020)

  1. The variable name for the error processing was not correct. The bug was removed.
  2. Added a sample script for using this library.

• v1.2.0 (July 3, 2020)

  1. Added a new method of AutoResizeFontForSpreadsheet. This method can automatically adjust the text length to fit in the cell width by changing the font size.

• v1.3.0 (October 20, 2021)

  1. Added a new method of RangeToHTMLTableForSpreadsheet. In this method, the range on Google Spreadsheet is converted to a HTML table. Using this method, for example, you can send the specific range in the Spreadsheet as an email by including a HTML table.

• v1.3.1 (February 8, 2022)

  1. Modified the method of DocumentToSpreadsheet. Before this update, the paragraphs with only line breaks have been ignored. From this version, such paragraphs are included in the result value of the cell.

• v1.4.0 (May 25, 2022)

  1. Added a new method of ReplaceTextToRichText. In this method, the text in a cell is converted to the richtext.

TOP