I am trying to send user updates to a cell in google sheets and I wanted keywords to be bolded within a string. I grabbed the following script from script.gs (written by sourabh choraria to give credit) and it worked well in an example I had set up but now is giving me an Illegal Argument error message in the line where it uses setTextStyle()
. Anyone know why?
I'm calling it like this:
setBoldFormat(message,["done","running"],sheet,33,13)
where the message is: "part 1/6 of file creation is: \\n\\n done \\n\\n part 2/6 will begin in a minute"
The error is specifically:
| 4:38:34 PM | Error | Exception: Illegal argument.
I can't share the actual spreadsheet because it's massive and has some sensitive information on it. The function I'm using is as follows:
function setBoldFormat(value, words, sheet, rowIndex, colIndex) {
const range = sheet.getRange(rowIndex, colIndex);
const boldX = SpreadsheetApp.newTextStyle().setBold(true).setUnderline(true).build();
for (let wordIndex in words) {
let word = words[wordIndex];
const richTextValue = range.getRichTextValue().copy();
const startIndex = value.indexOf(word);
if (startIndex > 0) {
const endIndex = startIndex + word.length;
const formattedOutput = richTextValue.setTextStyle(startIndex, endIndex, boldX).build();
range.setRichTextValue(formattedOutput);
SpreadsheetApp.flush();
}
}
}
The error occurs because the M33 content length is too short or this cell is blank. Something is likely missing in the code because the first parameter, value
, is not used.
Below is the code with slight modifications:
range.setValue(value);
SpreadsheetApp.flush();
because the question doesn't mention a reason to use it.function setBoldFormat(value, words, sheet, rowIndex, colIndex) {
const range = sheet.getRange(rowIndex, colIndex);
/* Added this line */
range.setValue(value);
const boldX = SpreadsheetApp.newTextStyle().setBold(true).setUnderline(true).build();
for (let wordIndex in words) {
let word = words[wordIndex];
const richTextValue = range.getRichTextValue().copy();
const startIndex = value.indexOf(word);
if (startIndex > 0) {
const endIndex = startIndex + word.length;
const formattedOutput = richTextValue.setTextStyle(startIndex, endIndex, boldX).build();
range.setRichTextValue(formattedOutput);
//SpreadsheetApp.flush();
}
}
}