Join & Split Rich Text Values in Google Sheets

Google Apps Script helper functions for handling rich text values in Google Sheets

Introduction

Join & Split text in Google Sheets with the built-in functions is handy and easy to use for normal text. However, we may need to handle rich text values which contain multiple different text attributes like font color, family, size, bold, italic, strikethrough, underline, hyperlinks. In this case, the split & join functions will not be able to process them. And you are at the right place, I am sharing my custom apps script functions here to handle these use cases. 

Helper Function: createDefaultTextStyle_

This is a helper function to create a default text style for delimiter when joining rich text values, you can decide to create your own default text style by updating the attributes in this function.

const createDefaultTextStyle_ = () => {

 return SpreadsheetApp.newTextStyle()

   .setBold(false)

   .setStrikethrough(false)

   .setUnderline(false)

   .setItalic(false)

   .setFontSize(10)

   .setFontFamily("Default")

   .setForegroundColor("#000000")

   .build();

};

Function: join_

This is the core function used to join a list of rich text values into one single rich text value by using a delimiter, the default delimiter is “\n” which is a new line break.

/**

* @param {GoogleAppsScript.Spreadsheet.RichTextValue[]} values A list of rich text values

* @param {string} by

* @return {GoogleAppsScript.Spreadsheet.RichTextValue} A single rich text value

*/

const join_ = (values, by = "\n") => {

 if (values.length === 0) return null;

 const richTextValue = SpreadsheetApp.newRichTextValue();

 const text = values.map((v) => (v ? v.getText() : "")).join(by);

 if (text === "") return null;

 richTextValue.setText(text);

 let start = 0;

 const defaultStyle = createDefaultTextStyle_();

 values.forEach((value, index) => {

   if (value === null) return;

   const runs = value.getRuns();

   runs.forEach((run) => {

     const text = run.getText();

     if (text === "") return;

     const link = run.getLinkUrl();

     const style = run.getTextStyle();

     const end = start + text.length;

     link && richTextValue.setLinkUrl(start, end, link);

     richTextValue.setTextStyle(start, end, style);

     start = end;

   });

   if (index == values.length - 1) return;

   richTextValue.setTextStyle(start, start + by.length, defaultStyle);

   start = start + by.length;

 });

 return richTextValue.build();

};

Function: split_

This is the core function which is used to split one single rich text value into  a list of rich text values by using a delimiter, the default delimiter is “\n” which is a new line break.

/**

* @param {GoogleAppsScript.Spreadsheet.RichTextValue} value

* @param {string} by

* @return {GoogleAppsScript.Spreadsheet.RichTextValue[]} A list of rich text

* values

*/

const split_ = (value, by = "\n") => {

 const text = value.getText();

 const splittedValues = text.split(by);

 const values = splittedValues.map((v) =>

   SpreadsheetApp.newRichTextValue().setText(v)

 );

 let index = 0;

 let start = 0;

 value.getRuns().forEach((run) => {

   const text = run.getText();

   const style = run.getTextStyle();

   const link = run.getLinkUrl();

   const end = start + text.length;

   if (text.includes(by) === false) {

     link && values[index].setLinkUrl(start, end, link);

     values[index].setTextStyle(start, end, style);

     start = end;

     return;

   }

   if (text === by) {

     index++;

     start = 0;

     return;

   }

   text.split(by).forEach((v) => {

     if (v === "") return;

     const end = start + v.length;

     url && values[index].setLinkUrl(start, end, url);

     values[index].setTextStyle(start, end, style);

     index++;

     start = 0;

   });

 });

 return values.map((v) => v.build());

};

Helper Function for Demo

This helper function can get values of the named ranges in the Spreadsheet, and it also handles some special values like “\n”, “\t”, “\s”.

const getNamedValues_ = () => {

 const ss = SpreadsheetApp.getActive();

 const values = {};

 const specialValues = {

   "\\n": "\n",

   "\\t": "\t",

   "\\s": " ",

 };

 ss.getNamedRanges().forEach((r) => {

   const name = r.getName();

   const value = r.getRange().getDisplayValue();

   if (value in specialValues) {

     return (values[name] = specialValues[value]);

   }

   values[name] = value;

 });

 return values;

};

Demo Use Case Split

This is a demo use case for using core function split_.

const getNamedValues_ = () => {

 const ss = SpreadsheetApp.getActive();

 const values = {};

 const specialValues = {

   "\\n": "\n",

   "\\t": "\t",

   "\\s": " ",

 };

 ss.getNamedRanges().forEach((r) => {

   const name = r.getName();

   const value = r.getRange().getDisplayValue();

   if (value in specialValues) {

     return (values[name] = specialValues[value]);

   }

   values[name] = value;

 });

 return values;

};

Demo Use Case Join

This is a demo use case for using core function join_.

const demoJoin = () => {

 const ss = SpreadsheetApp.getActive();

 const sheet = ss.getActiveSheet();

 const { inputJoin, outputJoin, joinBy } = getNamedValues_();

 const outputRange = sheet.getRange(outputJoin);

 outputRange.clearContent();

 SpreadsheetApp.flush();

 const values = sheet

   .getRange(inputJoin)

   .getRichTextValues()

   .map((v) => v[0]);

 const joinedValue = join_(values, joinBy || "\n");

 outputRange.setRichTextValue(joinedValue);

};

Check on YouTube

Links

Make a copy

Source Code On Github

Hire Me

YouTube

Website

Github

X

Instagram

Leave a Comment