Formatting Text in the googlesheet cell using a script

Question

I have a google sheet that has formatted text in a cell. Some of the text has color and is bold. I use a google script to add new content in the cell. When I run the script, the new content is added but the formatting of the old content is removed.

1) How can I keep the formatting of the old content when executing my script. 2) I want to make the new content bold and colored. How can I format the newcontent that is added to the cell?

Here is the content of my cell before executing the script

Here is my script:

  function CreateIssue() {
  var spreadsheet = SpreadsheetApp.getActive();
  var cell = spreadsheet.getCurrentCell();
  var oldContent = cell.getValue();
  var newContent ='Group:\n\nDescription:\n\nExpected Results:\n\nActual Results:\n\nTest 
  Results:\n\nTest Data:';
  var space = " ";
  cell.setValue(newContent + space + oldContent);

Here is the content of my cell after executing the script

As you can see, the formatting was cleared for the old content.

Answer 1

• You want to add the text to the top of text in the cell and put it to the cell.
• In this case, you want to keep the text style of the original text.
• You want to change the text style of the added text to "bold" and "ForegroundColor".
• You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer? Please think of this as justo one of several possible answers.

Modification points:

When the value is put using setValue, it seems that the text style is cleared. This might be the specification. So in this case, in order to set the text style, I used Class TextStyleBuilder and Class RichTextValueBuilder. The flow of this modified script is as follows.

Flow:

1. Retrieve the cell range.
2. Create the text style of additional texts which are newContent and space.
3. Retrieve the text style of the original texts which is oldContent.
4. Create new RichTextValueBuilder.
5. Set new texts to RichTextValueBuilder.
6. Set the text style to RichTextValueBuilder.
7. Put RichTextValueBuilder to the cell.

Modified script:

When your script is modified, it becomes as follows. Please select a cell including oldContent and run the script. By this, newContent and space are added to oldContent, and the text styles are set.

function CreateIssue() {
  var spreadsheet = SpreadsheetApp.getActive();
  var cell = spreadsheet.getCurrentCell();
  var oldContent = cell.getValue();
  var newContent = 'Group:\n\nDescription:\n\nExpected Results:\n\nActual Results:\n\nTest Results:\n\nTest Data:';
  var space = " ";

  // I added below script.
  var newStyles = [{
    start: 0,
    end: newContent.length,
    style: SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor("green").build()
  }];
  var richTextValue = cell.getRichTextValue();
  var offset = newContent.length + space.length;
  var oldContent = richTextValue.getText();
  if (oldContent.length > 0) {
    richTextValue.getRuns().forEach(function(e) {
      newStyles.push({
        start: offset + e.getStartIndex(),
        end: offset + e.getEndIndex(),
        style: e.getTextStyle()
      });
    });
  }
  var richText = SpreadsheetApp.newRichTextValue().setText(newContent + space + oldContent);
  newStyles.forEach(function(e) {richText.setTextStyle(e.start, e.end, e.style)});
  cell.setRichTextValue(richText.build());
}

• In above script, the additional text is put as the bold and green color. About this, when you want to change the color, please modify it.

Result:

When var newContent = "foo"; is set and select a cell including the text of "bar" which has the text style and run the above script, the following result can be obtained.

Before:

After:

The original text style is kept and the additional text has the text style.

References:

• Class TextStyleBuilder
• Class RichTextValueBuilder

If I misunderstood your question and this was not the direction you want, I apologize.