-1

I'm trying to write a Google Apps Script to italicize any text in parentheses in a specified range in Google Sheets (partial cell formatting). Ideally it would also remove any existing text-only formatting (bold, strikethrough, text size, color, etc) but not cell formatting or text alignment.

  • I worked out the regex search: /\((.*?)\)/g
  • I understand the RichTextValue class is how to achieve this.
  • As a non-coder, I'm having trouble correctly applying these. I tried modeling after this answer in a similar post.

This is the code I've come up with:

function italicizeTextInParentheses() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange('F:F'); // where to search
  var values = range.getValues();
  var italic = SpreadsheetApp.newTextStyle().setItalic(true).build();

  for (var i = 0; i < values.length; i++) {
    var cellValue = values[i][0];
    if (typeof cellValue === 'string') {
      var textToItalicize = cellValue.match(/\((.*?)\)/g); // what to search
      range.getCell(i + 1, 1).SpreadsheetApp.newRichTextValue()
      .setText(textToItalicize)
      .setTextStyle(italic);
    }
  }
}

How do I fix this to work as intended?

1
  • I updated my answer with the new requirements, including nested parentheses and running from either the menu or the script editor without the need to modify the script. Test version here
    – Blindspots
    Mar 14 at 22:45

2 Answers 2

-1
  1. The onOpen function adds a menu item when the spreadsheet is opened.
    Custom Menu, Normal text to italic

  2. The userInput function adds the ability to specify the column via user input or default to the hardcoded value, currently 6.
    Custom Menu, Input box for column number

  3. You can also run the function from Google's script editor by clicking Run while italicizeTextInParentheses is selected. The defaultCol value will be used which is currently set to 6. script editor run button for 'italicizeTextInParentheses' function

Script

Link to Example Spreadsheet

const defaultCol = 6;

function userInput_() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.prompt("๐„๐ง๐ญ๐ž๐ซ ๐‚๐จ๐ฅ๐ฎ๐ฆ๐ง ๐๐ฎ๐ฆ๐›๐ž๐ซ " + '\n' + "๐˜๐˜ง ๐˜ฏ๐˜ฐ ๐˜ท๐˜ข๐˜ญ๐˜ช๐˜ฅ ๐˜ฆ๐˜ฏ๐˜ต๐˜ณ๐˜บ, ๐™™๐™š๐™›๐™–๐™ช๐™ก๐™ฉ ๐˜ช๐˜ด ๐˜ค๐˜ฐ๐˜ญ๐˜ถ๐˜ฎ๐˜ฏ " + defaultCol);
  let entry = result.getResponseText().match(/^\d{1,2}$/);
  italicizeTextInParentheses(entry);
}

function italicizeTextInParentheses(entry) {
  const column = entry || defaultCol;   // User Input or Default value
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues().map(row => row[column-1]);
  const txtRegex = /\((?:[^()]+|\([^()]*\))*\)/g;
  const parRegex = /(\()|(\))/g;
  const newStyle = SpreadsheetApp.newTextStyle().setItalic(true).build();
  const defaultStyle = SpreadsheetApp.newTextStyle()
    .setItalic(false)
    .setBold(false) 
    .setUnderline(false)
    .setForegroundColor('black')
    .build();

  for (let i = 0; i < values.length; i++) {
    let cell = range.getCell(i+1, column), value = values[i];
    
    if (value && !cell.getFormula()) {
      let txtMatch = [...value.matchAll(txtRegex)];
      
      if (txtMatch[0])  {          
        let parMatch = [...value.matchAll(parRegex)];
        let txtIndex = txtMatch.map(a => [a.index+1, a.index + a[0].length-1]);
        let parIndex = parMatch.map(a => a.index);
        let newRich = cell.getRichTextValue().copy().setTextStyle(defaultStyle).build();
        txtIndex.forEach(e => newRich = newRich.copy().setTextStyle(e[0], e[1], newStyle).build());
        parIndex.forEach(e => newRich = newRich.copy().setTextStyle(e, e+1, defaultStyle).build());
        
        cell.setRichTextValue(newRich);    
      } 
    }
  }
}

function onOpen() {
  SpreadsheetApp.getActive().addMenu(
    "โš™๏ธ Custom Menu",
    [{ name: "(โ€Šnormal textโ€Š) โ‡ข (โ€Š๐˜ช๐˜ต๐˜ข๐˜ญ๐˜ช๐˜คโ€Š)",
       functionName: "userInput_" }]
  );
}
6
  • The menu item appears but fails to ask for user input and runs the script without doing anything. Running it from the Apps Script environment does bring up the user input on the sheet and italicizes properly, but it doesn't clear other text formatting on that text (like bold), and it undesirably does reset cell color, vertical text alignment, and upper borders.
    – gills
    Mar 14 at 18:19
  • Definitely. Just updated my question with those requirements. I was in fact able to eek a script that works really well out of Chat GPT with enough careful wording and retries.
    – gills
    Mar 14 at 18:31
  • I just tested and don't see any issues. I added a link to an anonymous version that you can compare to whatever you did. I'll take a look at your new requirements and adjust.
    – Blindspots
    Mar 14 at 18:50
  • 1
    @doubleunary thanks for catching that typo
    – Blindspots
    Mar 15 at 19:20
  • Gave this another look. It appears to be almost working now, but itโ€ฒs reformatting the full text in each cell that includes parentheses, rather than just what's in the parentheses. Really appreciate your answer (menu stuff is super cool too), but the GPT one I got running has been working great and runs faster (rather than updating one cell at a time, it's all at once), so I'm going to accept that one.
    – gills
    Mar 17 at 21:44
-1

I was able to generate this script with ChatGPT which works great:

function italicizeTextInParentheses() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var range = sheet.getRange('F:F'); // where to search

  var values = range.getValues();
  var italic = SpreadsheetApp.newTextStyle().setItalic(true).build();
  var bold = SpreadsheetApp.newTextStyle().setBold(false).build();

  for (var i = 0; i < values.length; i++) {
    var cellValue = values[i][0];
    
    if (typeof cellValue === 'string') {
      var matches = cellValue.match(/\((?:[^()]+|\([^()]*\))*\)/g); // what to search
      
      if (matches) {
        var richText = SpreadsheetApp.newRichTextValue().setText(cellValue);
      
        for (var j = 0; j < matches.length; j++) {
          var match = matches[j];
          var startIndex = cellValue.indexOf(match);
          var endIndex = startIndex + match.length;
          richText.setTextStyle(startIndex, endIndex, italic);
          richText.setTextStyle(startIndex, endIndex, bold);
        }
        
        range.getCell(i + 1, 1).setRichTextValue(richText.build());
      }
    }
  }
}

It's fast, updating all text in one move rather than one cell at a time. It also uses this regex to include all text within parentheses even if there are nested parentheses:

/\((?:[^()]+|\([^()]*\))*\)/g

I couldn't figure out how to clear other text formatting in one go (font size, bold, strikethrough, color), so for my use case, I'm just having this script unbold the text in addition to italicizing it.

1

Your Answer

By clicking โ€œPost Your Answerโ€, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.