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