Google Sheets App Script - Custom Function to find RichText (Bold/Italic) locations

Question

I'm currently working on a project that's using Google Sheets to keep track of/maintain some translations of text (effectively some meta-data and then 2 columns for the original text and the translation).

The final application has some word-wrapping constraints and a variable-width font, so I created a custom function in app script to apply line breaks in order to facilitate visualizing how it would look.

function preview(text)
{
  if(text.startsWith('=')) // Copy of something else, so we don't care
    return text;

  text = text.trimEnd();
  text = text.replace("<4A>", "\n\n"); // For the sake of the preview, just treat <4A> as a new box
  text = text.replace(/\<&[a-zA-Z0-9]+\>/g,"────────"); // Assume the longest length (8 * 8)
  text = text.replace(/<\*.>/g,""); // Get rid of exit codes
  text = text.replace(/<S.+?>/g,""); // Get rid of special effects
  
  var output = "";
  var boxes = text.split("\n\n"); // Double newline is a forced new text box
  for (var i = 0; i < boxes.length; i++)
  { // For each intentional text box
    var box = boxes[i];
    box = box.replace("<4E>","\n"); // Will technically forcibly always draw on the second line
    
    var lines = box.split('\n');
    var newboxFlag = false; // Flag to indicate if we draw a new line or new box

    for (var j = 0; j < lines.length; j++)
    { // For each intentional line in this box
      words = lines[j].split(' ');
      var word = "";    
      var currentLineLen = 0;
      for(var k = 0; k < words.length; k++)
      {
        word += words[k];
        
        var wordWidth = 0;
        for (var l = 0; l < word.length; l++)
        {
          var char = word.charAt(l);
          wordWidth += getCharacterWidth(char);
        }

        if(wordWidth + currentLineLen > 0x89)
        { // This word won't fit on this line, so it goes to a new line
          
          // Strip the first space, and just assume we don't have a string longer than 136 characters
          word = word.substr(1);
          wordWidth -= getCharacterWidth(' ');
          
          // Add a new line and flip the newboxFlag
          output += '\n';
          if(newboxFlag) output += '\n';
          newboxFlag ^= 1;
          currentLineLen = 0;
        }
        
        currentLineLen += wordWidth
        output += word;
        word = " ";
      }
      
      if (j != lines.length - 1)
      {
        output += '\n';  // line length is reset at the top
        if(newboxFlag) output += '\n';
        newboxFlag ^= 1;
      }
    }
    if(i != boxes.length - 1) output += '\n\n'; // If we're not on the last box, add the double new line
  }
  return output;
}

and I call it with =preview(<CELL>) (getCharacterWidth just pulls an integer from a character <-> width mapping).

This function works fine with raw text that does not contain any bold or italic characters.

The problem is, bold and italic characters often take more space than their normal counterparts, and in some cases will cause the previewed output text to incorrectly omit newlines where there should be some.

For example, the original text would be:

This is italic. This is bold. This is bold and italic.

=preview(text) should ideally be something like:

This is italic.

This is bold.

This is bold

and italic.

but instead, it might end up being like this:

This is italic.

This is bold. This

is bold and italic.

Since the additional width of the bold/italic characters is not being accounted for, the function believes it has enough space to fit another word on the line.

It's OK if I can't output it, but I at least need to be able to identify what text is bold or italic. In Google Sheets, how can I write a custom function that is aware of RichText?

In order to correctly understand about your question, can you provide the sample input and output you expect? — Tanaike
@Tanaike I've added a sample. Let me know if this makes sense. — variantxyz
Have you tried getRichText on the active range? Could you show the full preview function? — TheMaster
Thank you for replying and adding more information. When I saw it, I could understand about This is italic. and This is bold.. But I cannot understand the logic for retrieving This is bold and and italic.. Can I ask you about the detail information of it? — Tanaike
@TheMaster No I have not, I'm not sure how exactly that's doable... I could show you the full preview function, but it's really nothing beyond some javascript text processing. I literally had it return 'typeof(text)' to verify it was seeing it as a string, I'm not sure how exactly to get the active range. — variantxyz

variantxyz variantxyz · Accepted Answer · 2020-08-15T07:28:05

Thanks to TheMaster's comments, I was able to create a solution by changing my function to the following:

function preview(startcol, startrow)
{
  var str = String.fromCharCode(64 + startcol) + startrow;
  var richtext = SpreadsheetApp.getActiveSpreadsheet().getRange(str).getRichTextValue();
  var runs = richtext.getRuns();
...

and setting my call to

=preview(COLUMN(<CELL>), ROW(<CELL>))

By looping over the runs accounting for getTextStyle().IsBold() or .IsItalic(), I can account for the RichText attributes.

if(runs[i].getTextStyle().IsBold() && runs[i].getTextStyle().IsItalic())
{
  wordWidth += getCharacterWidthBoldItalic(char);
}
else if(runs[i].getTextStyle().IsBold())
{
  wordWidth += getCharacterWidthBold(char);
}
else if(runs[i].getTextStyle().IsItalic())
{
  wordWidth += getCharacterWidthItalic(char);
}
else
{
  wordWidth += getCharacterWidth(char);
}

Google Sheets App Script - Custom Function to find RichText (Bold/Italic) locations

1 Answers