CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
IMTheNachoMan
IMTheNachoMan

Reputation: 5833

Replace text in a run using RichTextValueBuilder in a Google Spreadsheet using Google Apps Script

I am trying to manipulate the rich text value of a field.

I can get a cell's rich text value, I can get the runs, and I can update links but I can't figure out how to remove links or manipulate part of a run. Using this cell as an example:

enter image description here

The word emergency, including some spaces before and after, is a link. I want to keep the link and spaces but I want to remove the link from the spaces.

var richTextValue = range.getRichTextValue();
var newRichTextValue = richTextValue.copy();

// get all the runs
richTextValue.getRuns().forEach(run => {
  // we only need runs that are links
  if (run.getLinkUrl()) {
    // get the run text
    var text = run.getText();

    // if the text of this run starts with spaces
    if (hasLeadingSpaces = text.match(/^\s+/)) {
      var numSpaces = hasLeadingSpaces[0].length;
     
      // now what?
    }
  }
});

I know where the spaces start and end but there doesn't seem to be a way to set text of a run or just part of a RichTextValue.

Upvotes: 2

Views: 776

Answers (1)

Yuri Khristich
Yuri Khristich

Reputation: 14537

For this particular case I can propose this code:

function myFunction() {
  var range = SpreadsheetApp.getActiveSheet().getRange('a1');
  var richTextValue = range.getRichTextValue();
  var all_text = richTextValue.getText();

  var runs = richTextValue.getRuns();
  var link = runs.filter(r => r.getLinkUrl())[0]; // a first link from the cell

  var link_url   = link.getLinkUrl();
  var link_text  = link.getText().trim();
  var link_style = link.getTextStyle();

  all_text  = all_text.replace(/\s+/g, ' '); // hope the extra spaces are only around link
  var start = all_text.indexOf(link_text); // hope the text is unique
  var end   = start + link_text.length;

  const newRichTextValue = SpreadsheetApp.newRichTextValue()
    .setText(all_text)
    .setLinkUrl(start, end, link_url)
    .setTextStyle(start, end, link_style)
    .build();

  range.clear().setRichTextValue(newRichTextValue);
}

But it's probably is not a universal solution. For example it doesn't keep the text styles of the text around the link. Etc.

Upvotes: 2

Related Questions