Abstract
One day, you might have a situation where you are required to manage rich texts in Google Spreadsheet using Google Apps Script. In this report, I would like to introduce the basic technique for managing rich texts with Google Apps Script.
Introduction
At Google Spreadsheet, rich texts can be used as a cell value. The rich texts can be also managed by Google Apps Script. Ref When I saw the official document related to the rich texts with Google Apps Script, I’m worried that it might be difficult a little for users to manage the rich texts using Google Apps Script.
So, I have already published 2 libraries of RichTextApp and RichTextAssistant. When these libraries are used, users can manage rich texts on Google Spreadsheet using Google Apps Script. I believe that these libraries are useful for users.
However, I sometimes have seen some questions related to the management of rich texts using Google Apps Script on Stackoverflow. From this situation, in this report, I would like to introduce the basic technique for managing rich texts with Google Apps Script. When this technique can be understood, I believe that you will be able to create a script for managing rich text by yourself. I hope for helping help users understand the method for managing rich texts on Spreadsheet using Google Apps Script.
Sample scripts
In this section, I would like to explain the basic technique for managing rich texts on Spreadsheet with Google Apps Script using several sample scripts.
Sample 1
Fig 1. Changing the font color of a cell value.
In this sample, the font color in a cell value is changed as shown in Fig. 1. In this case, 2 approaches can be considered.
function sample1() {
SpreadsheetApp.getActiveSheet().getRange("A1").setFontColor("red");
}
This function sample1() changes the font color using the method setFontColor of Class Range. When the style of the whole cell value is changed, this method can be used. Unfortunately, this method cannot be used for reflecting the text style in a part of the text of a cell value. Here, the rich text is used.
function sample2() {
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A1");
const value = range.getValue();
const textStyle = SpreadsheetApp.newTextStyle()
.setForegroundColor("red")
.build();
const richTextValue = SpreadsheetApp.newRichTextValue()
.setText(value)
.setTextStyle(textStyle)
.build();
range.setRichTextValue(richTextValue);
}
This function sample2() changes the font color using the method setRichTextValue of Class Range. The text style of SpreadsheetApp.newTextStyle().setForegroundColor("red").build() is set to all texts in the cell. If you want to reflect the font color, the font size, and the bold type, you can also use const textStyle = SpreadsheetApp.newTextStyle().setForegroundColor("red").setFontSize(20).setBold(true).build();. The rich text is set by putting the rich text object including the text style into a cell.
Sample 2
Fig 2. Changing the font color of a single word of a cell value.
In this sample, the font color of a single word of the cell value (sample1 and sample3 of sample1 sample2 sample3 sample4 sample5) is changed as shown in Fig. 2. The sample script is as follows.
function sample3() {
const searchText = "sample1";
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A1");
const value = range.getValue();
const startIndex = value.indexOf(searchText);
const richTextValue = SpreadsheetApp.newRichTextValue()
.setText(value)
.setTextStyle(
startIndex,
startIndex + searchText.length,
SpreadsheetApp.newTextStyle().setForegroundColor("red").build()
)
.build();
range.setRichTextValue(richTextValue);
}
When the function sample3() is run, the top image in Fig. 2 is changed to 2nd image. You can see that the font color of sample1 of the cell value is changed.
function sample4() {
const searchText = "sample3";
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A1");
const richText = range.getRichTextValue();
const text = richText.getText();
const startIndex = text.indexOf(searchText);
const richTextValue = richText
.copy()
.setTextStyle(
startIndex,
startIndex + searchText.length,
SpreadsheetApp.newTextStyle().setForegroundColor("red").build()
)
.build();
range.setRichTextValue(richTextValue);
}
When the function sample4() is run after the function sample3() was run, the font color of sample3 of the cell value is changed as shown in the 3rd image in Fig. 2. Here, the important point is that the result of the function sample4() is obtained while the result of the function sample3() is kept.
On the other hand, when the function sample3() is run again after the function sample4() was run, the font color of only sample1 of the cell value is changed, while the result of the function sample4() is not kept.
The differences between these situations are as follows.
- At the function
sample3(), the rich text is overwritten by a new rich text, and the font color of onlysample1of the cell value is changed. By this, the style is overwritten. - At the function
sample4(), the existing rich text in the cell, that the font color ofsample1of the cell value is changed, is copied, and a new rich text. that the font color ofsample3of the cell value is added to the copied rich text. By this, the style is added to the existing style.
Sample 3
Fig 3. Changing font colors of multiple words in multiple cell values.
In this sample, the font colors of multiple words in multiple cells are changed as shown in Fig. 3. The sample script is as follows.
function sample5() {
const searchText = [
{ text: "sample1", fontColor: "red" },
{ text: "sample3", fontColor: "green" },
{ text: "sample5", fontColor: "blue" },
];
const searchTextObj = searchText.map(({ text, fontColor }) => ({
t: text,
s: SpreadsheetApp.newTextStyle().setForegroundColor(fontColor).build(),
}));
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A1:B3");
const richTextValues = range.getRichTextValues().map((r) =>
r.map((c) => {
const text = c.getText();
if (text) {
searchTextObj.forEach(({ t, s }) => {
let idx = -1;
let offset = 0;
do {
idx = text.indexOf(t, offset);
const len = t.length;
if (idx > -1) {
c = c
.copy()
.setTextStyle(idx, idx + len, s)
.build();
offset = idx + len;
}
} while (idx > -1);
});
}
return c;
})
);
range.setRichTextValues(richTextValues);
}
When this function sample5() is run to the situation of the top image of Fig. 3, the bottom image of Fig. 3 is obtained. You can see the font colors of the words of sample1, sample3, and sample5 are changed to red, green, and blue, respectively. In this script, the texts of searchTexts are searched in every cell, and each style is set to the rich text object using the do-while loop. c = c.copy().setTextStyle(idx, idx + len, s).build() is used in order to add a new rich text to the existing rich text.
Here, there is an important point. When the number values and the date objects are included in the range for using getRichTextValues, the value retrieved by getText() has no value.
Fig 4. Result when number values and date objects are included.
When the cells “B1” and “B2” are a number value and a date object as shown in the top image of Fig. 4, respectively, the results of cells “B1” and “B2” are empty as shown in the bottom image of Fig. 4. Because, in the current stage, getRichTextValue and getRichTextValues cannot retrieve the number values and the date objects. Ref
In the next section, I would like to introduce a script for avoiding this issue.
Sample 4
Fig 5. Changing font colors of multiple words in multiple cell values by considering the number values and the date objects.
In this sample, the font colors of multiple words in multiple cells are changed by considering the number values and the date objects as shown in Fig. 3. The cells “B3” and “B5” have the date objects. The cells “C2:C6” have the number values. And also, the cell “C6” has a function of =AVERAGE(C2:C5). The sample script is as follows.
function sample6() {
const overwriteRichText = true; // If this is true, the rich text is added to the existing rich text. If this is false, the rich text is added after the existing rich text is cleared.
const searchText = [
{ text: "sample1", fontColor: "red" },
{ text: "sample3", fontColor: "green" },
{ text: "sample5", fontColor: "blue" },
];
const searchTextObj = searchText.map(({ text, fontColor }) => ({
t: text,
s: SpreadsheetApp.newTextStyle().setForegroundColor(fontColor).build(),
}));
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("B2:D6");
const values = range.getValues();
const formulas = range.getFormulas();
const richTextValues = range.getRichTextValues().map((r, i) =>
r.map((c, j) => {
const text = c.getText();
if (overwriteRichText === false) {
c = c.copy().setText(text).build();
}
if (text) {
searchTextObj.forEach(({ t, s }) => {
let idx = -1;
let offset = 0;
do {
idx = text.indexOf(t, offset);
const len = t.length;
if (idx > -1) {
c = c
.copy()
.setTextStyle(idx, idx + len, s)
.build();
offset = idx + len;
}
} while (idx > -1);
});
} else if (formulas[i][j] || values[i][j]) {
c = c
.copy()
.setText(formulas[i][j] || values[i][j])
.build();
}
return c;
})
);
range.setRichTextValues(richTextValues);
}
When this function sample6() is run to the top image of Fig. 5, the bottom image can be obtained. You can see that the rich texts of texts are changed while the number values and the date object are kept. Also, the formula of cell “C6” is kept. In order to complement the number values, the date objects, and the formulas, const values = range.getValues(); and const formulas = range.getFormulas(); are used in this script.
In the official document the method setText of Class RichTextValueBuilder, it says that the argument is the string type. Ref However, from the above result, it is found that when the number values and the date object are used to the argument of setText("###"), the inserted values are used as the number values and the date objects, and also, this situation is the same with the formula.
As an additional function, in this script, when const overwriteRichText = true; is modified to const overwriteRichText = false;, the existing rich texts in the cell value are ignored, and the new rich texts are put. When this is true, the new rich texts are added to the existing rich texts.
Summary
In this report, the basic technique for managing rich texts with Google Apps Script was introduced. The following important points were shown.
- By copying the existing rich texts retrieved by
getRichTextValueandgetRichTextValues, new rich texts can be added to the existing rich texts. This logic can be also used to put multiple text styles to multiple words of a cell value. - The methods
getRichTextValueandgetRichTextValuescannot retrieve the number values, the date objects, and the formulas. In this report, a sample script for avoiding this issue was also introduced. - The number values, the date objects, and the formulas can be used as the argument of the method
setTextof Class RichTextValueBuilder.
Appendix
By the way, when you want to achieve the situation of the top image, you can use the following sample script.
function myFunction() {
const searchTextObj = [
{
t: "sample1",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#ff3333")
.setFontSize(20)
.build(),
},
{
t: "sample2",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#ff9933")
.setFontSize(20)
.build(),
},
{
t: "sample3",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#b2ff66")
.setFontSize(20)
.build(),
},
{
t: "sample4",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#3399ff")
.setFontSize(20)
.build(),
},
{
t: "sample5",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#9933ff")
.setFontSize(20)
.build(),
},
{ t: "s", s: SpreadsheetApp.newTextStyle().setFontSize(30).build() },
{
t: "1",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#0066cc")
.setFontSize(25)
.build(),
},
{
t: "2",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#0066cc")
.setFontSize(25)
.build(),
},
{
t: "3",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#0066cc")
.setFontSize(25)
.build(),
},
{
t: "4",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#0066cc")
.setFontSize(25)
.build(),
},
{
t: "5",
s: SpreadsheetApp.newTextStyle()
.setForegroundColor("#0066cc")
.setFontSize(25)
.build(),
},
];
const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange("A1:A1");
const richTextValues = range.getRichTextValues().map((r) =>
r.map((c) => {
const text = c.getText();
if (text) {
searchTextObj.forEach(({ t, s }) => {
let idx = -1;
let offset = 0;
do {
idx = text.indexOf(t, offset);
const len = t.length;
if (idx > -1) {
c = c
.copy()
.setTextStyle(idx, idx + len, s)
.build();
offset = idx + len;
}
} while (idx > -1);
});
}
return c;
})
);
range.setRichTextValues(richTextValues);
}