google-sheetsgoogle-apps-scriptrichtext

Illegal Argument Error Thrown When Trying to setTextStyle() for Rich Text in Google Apps Script


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

Solution

  • 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:

    1. Added range.setValue(value);
    2. Commented out 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();
        }
      }
    }