This is a sample script for retrieving the text positions in the text data using Google Apps Script.
For example, in order to set the rich text style the part of text from the text data, this sample script will be useful.
The sample situation is as follows.
sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5
In this sample, the text positions of sample2
and sample5
are retrieved from this sample text data.
function myFunction() {
const searchTexts = ["sample2", "sample5"];
const sampleText =
"sample1, sample2, sample3, sample4, sample5\nsample1, sample2, sample3, sample4, sample5\nsample1, sample2, sample3, sample4, sample5\n";
const obj = searchTexts.map((t) => ({
text: t,
position: [...sampleText.matchAll(new RegExp(t, "g"))].map((e) => ({
start: e.index,
end: e.index + e[0].length,
})),
}));
console.log(obj);
}
When this script is run, the following result is obtained. You can see the text positions of sample2
and sample5
in the sample text can be seen.
[
{
"text": "sample2",
"position": [
{ "start": 9, "end": 16 },
{ "start": 53, "end": 60 },
{ "start": 97, "end": 104 }
]
},
{
"text": "sample5",
"position": [
{ "start": 36, "end": 43 },
{ "start": 80, "end": 87 },
{ "start": 124, "end": 131 }
]
}
]
The sample situation is as follows.
In this sample situation, it is found that the text style of sample2
and sample5
of the text in a cell is set to the bold and the red color.
function myFunction() {
const searchTexts = ["sample2", "sample5"];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
const range = sheet.getRange("A1");
const style = SpreadsheetApp.newTextStyle()
.setBold(true)
.setForegroundColor("red")
.build();
const richtextValue = range.getRichTextValue();
const sampleText = richtextValue.getText();
const copied = richtextValue.copy();
searchTexts.forEach((t) =>
[...sampleText.matchAll(new RegExp(t, "g"))].forEach((e) =>
copied.setTextStyle(e.index, e.index + e[0].length, style)
)
);
range.setRichTextValue(copied.build());
}
When this script is run, the value is retrieved from a cell "A1". And, the text style of sample2
and sample5
is set to the bold and the red color.
function myFunction() {
const searchTexts = ["sample2", "sample5"];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Please set the sheet name.
const range = sheet.getDataRange();
const style = SpreadsheetApp.newTextStyle()
.setBold(true)
.setForegroundColor("red")
.build();
const richtextValues = range.getRichTextValues().map((r) =>
r.map((richtextValue) => {
const sampleText = richtextValue.getText();
const copied = richtextValue.copy();
searchTexts.forEach((t) =>
[...sampleText.matchAll(new RegExp(t, "g"))].forEach((e) =>
copied.setTextStyle(e.index, e.index + e[0].length, style)
)
);
return copied.build();
})
);
range.setRichTextValues(richtextValues);
}
When this script is run, the values are retrieved from the cells of data range. And, the text style of sample2
and sample5
is set to the bold and the red color.
This script was reflected to RichTextApp as a new method ReplaceTextToRichText
. By this, you can use this using a Goolge Apps Script library.