Split & Join Rich Text Values in Google Sheets: GAS107
Introduction
If you are looking for some scripts which can split and join the rich text values in Google Sheets, you may find the right place. In this Google Apps Script project, I am sharing the core functions to handle them in Google Sheets with the demo use cases.
YouTube
Check this project introduction video on YouTube if you prefer to watch video instructions.
Instructions (3 Steps)
Follow the steps below to set up the project from your side.
Step 1
Make a copy of my project to your Google Drive with the link below.
Split & Join Rich Text Value in Google Sheets: GAS107
Step 2
Complete the authorization step by clicking the arrow button “Join” or “Split” in the Spreadsheet. It will ask your authorization to use the script when you run it at the first time, follow the guide below if this is new to you.
A Complete Guide to Authorize GAS Project
Step 3
In this demo app, you can try the “Join” and “Split” functions by providing them the “Input”, “Output”, and “By”(delimiter). Similar to using the plain text “Join” and “Split” formulas, the only difference is the text format will be kept with our script.
- Input: a input range name A1 notation
- Output: a output range name A1 notation
- By: the delimiter to split or join
* The arrow buttons are used to run the “Join” and “Split” scripts.
Utility Functions
A helper function to create a default text style, which is used in the core function “join_”.
const createDefaultTextStyle_ = () => {
return SpreadsheetApp.newTextStyle()
.setBold(false)
.setStrikethrough(false)
.setUnderline(false)
.setItalic(false)
.setFontSize(10)
.setFontFamily("Default")
.setForegroundColor("#000000")
.build();
};
Core Functions
The core function “join_” which can join a list of rich text values to a single rich text value by a delimiter.
/**
* @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();
};
The core function “split_” which can split a text value to a list of rich text values by a delimiter.
/**
* @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());
};
GoogleAppsScript Playlist (Follow My Channel for New Projects)
Links
Hire me on Upwork (work with me)
Comments
Post a Comment