Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script

02 Apr 2023, 12:36

GasTips / spreadsheet

In the current stage, Google Spreadsheet can use rich texts in cells. The rich texts can be also managed by Google Apps Script. But, I thought that creating a script for editing the existing rich text in the cell might be a bit complicated. Because, for example, in the current stage, when the text of the rich text of a cell is changed using a script, all text styles are cleared. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

Preparation

When you use the following scripts, please install the Google Apps Script library of RichTextAssistant. You can see how to install it at here.

Sample 1: Set rich text to cell text a cell.

function sample() {
  const sheetName = "Sheet1"; // Please set sheet name.
  const searchTexts = ["sample1", "text 3", "sample5"];
  const textStyle = SpreadsheetApp.newTextStyle()
    .setBold(true)
    .setForegroundColor("red")
    .build();

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const srcRange = sheet.getRange("A1:C3");
  const richTextValues = srcRange.getRichTextValues();
  const updatedRichTextValues = richTextValues.map((r) =>
    r.map((richTextValue) => {
      const object = {
        richText: richTextValue,
        texts: searchTexts,
        textStyle: textStyle,
      };
      return RichTextAssistant.setTextStyleInCellText(object);
    })
  );
  srcRange.setRichTextValues(updatedRichTextValues);
}

When this script is run, the text style of ["sample1", "text 3", "sample5"] including cell values of “A1:C3” is changed to bold type and red font color.

As an important point, about searchTexts in the above script, the regex can be used for searching texts. But, in that case, please use the string pattern instead of the regular expression literal. Ref

Sample 2: Append texts to rich text in a cell.

function sample() {
  const sheetName = "Sheet1"; // Please set sheet name.

  // Source RichText
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const sourceRichTextValue = sheet.getRange("A1").getRichTextValue();

  // Destination RichText
  const appendRichTextValue = sheet.getRange("C1").getRichTextValue();

  const res = RichTextAssistant.appendTexts({
    sourceRichTextValue,
    appendRichTextValue,
  });
  sheet.getRange("E1").setRichTextValue(res);
}

When this script is run, the rich text of “C1” is appended to “A1”, and the updated rich text is put to “E1”.

Sample 3: Insert paragraphs to rich text in a cell.

function sample() {
  const sheetName = "Sheet1"; // Please set sheet name.

  const insertIndexAsParagraph = 2;

  // Source RichText
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const sourceRichTextValue = sheet.getRange("C1").getRichTextValue();

  // Destination RichText
  const destinationRichTextValue = sheet.getRange("A1").getRichTextValue();

  const res = RichTextAssistant.insertParagraphs({
    insertIndexAsParagraph,
    sourceRichTextValue,
    destinationRichTextValue,
  });
  sheet.getRange("E1").setRichTextValue(res);
}

When this script is run, the rich text of “C1” is inserted between the 2nd and 3rd paragraphs of “A1” as a paragraph, and the updated rich text is put to “E1”.

Sample 4: Delete paragraphs from the rich text in a cell.

function sample() {
  const sheetName = "Sheet1"; // Please set sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getRange("A1:C3");
  const richTextValues = range.getRichTextValues();
  const updatedRichTextValues = richTextValues.map((r) =>
    r.map((richTextValue) => {
      const object = { richTextValue, deleteIndexes: [0, 2] }; // 1st and 3rd paragraphs are deleted.
      return RichTextAssistant.deleteParagraphs(object);
    })
  );
  range.setRichTextValues(updatedRichTextValues);
}

When this script is run, the 1st and 3rd paragraphs are deleted from the rich texts of “A1:C3”, and the updated rich text is put to the same range.

Sample 5: Merge rich texts in cells.

function sample() {
  const sheetName = "Sheet1"; // Please set sheet name.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const srcRange = sheet.getRange("A1:C3");
  const richTextValues = srcRange.getRichTextValues();
  let temp1 = RichTextAssistant.createNewRichText();
  richTextValues.forEach((r) => {
    let temp2 = RichTextAssistant.createNewRichText();
    r.forEach((richTextValue) => {
      temp2 = RichTextAssistant.appendTexts({
        sourceRichTextValue: temp2,
        appendRichTextValue: richTextValue,
        lastLineBreak: false,
      });
    });
    temp1 = RichTextAssistant.appendTexts({
      sourceRichTextValue: temp1,
      appendRichTextValue: temp2,
    });
  });
  srcRange.clear().offset(0, 0).setRichTextValue(temp1);
}

When this script is run, the rich texts of “A1:C3” are merged as a single rich text, and the created rich text is put to “A1”.

Sample 6: Add rich text to specific positions

function sample() {
  const sheetName = "Sheet1"; // Please set sheet name.

  // Declare the insert rich text.
  const addText = "(added)";
  const addRichStyle = SpreadsheetApp.newTextStyle()
    .setForegroundColor("red")
    .setBold(true)
    .build();
  const addRichText = SpreadsheetApp.newRichTextValue()
    .setText(addText)
    .setTextStyle(addRichStyle)
    .build();

  // Update rich texts of cells.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const srcRange = sheet.getRange("A1:A2");
  const richTextValues = srcRange.getRichTextValues();
  const res = richTextValues.map((r) =>
    r.map((c) => {
      const idxs = [...c.getText().matchAll(/@(\n|$)/g)].map(
        (e) => e.index + 1
      );
      idxs.reverse().forEach((e) => {
        c = RichTextAssistant.insertTexts({
          insertIndexAsText: e,
          sourceRichTextValue: addRichText,
          destinationRichTextValue: c,
        });
      });
      return c;
    })
  );

  // Put updated rich text to cells.
  srcRange.setRichTextValues(res);
}

When this script is run, a rich text of (added) is added to the lines where the last character is @.