Preserve Formatting When Setting New Cell Value (Google Spreadsheet)

user_13419806 0
Viewed 916

I have the following script which sets a new value for a selected spreadsheet cell. It works as expected but after every entry it clears all the previous formatting from the cell. How can I modify the code to make sure it preserves all the partial formatting, such as font weight/color? Thank you for your help.

function enterName1(options1, activity1, number1) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var cell = sheet.getActiveCell();
  var value = cell.getValue();
  var formattedDate = Utilities.formatDate(new Date(), "GMT+3", "dd.MM.yy' at 'HH:mm");
  Logger.log(formattedDate);
  
  if (value === '') {
      value = value + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
      sheet.getActiveRange().setNumberFormat('@STRING@');
      cell.setValue(value);
  } else {
      value = value + "\n\n" + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
      sheet.getActiveRange().setNumberFormat('@STRING@');
      cell.setValue(value);
  }
}
google-apps-script google-sheets-api google-sheets html javascript
1 Answers

I believe your goal as follows.

  • Question 1: You want to preserve the existing text styles of the text in the cell when the value is added to the cell including the values.
  • Question 2: You want to preserve the existing text styles of the text in the cell when the value is added to the cell including the values. And also, you want to set the text style for formattedDate of the adding text.- Question 3: You want to preserve the existing text styles of the text in the cell when the value is added to the cell including the values. And also, you want to set the text style for formattedDate of the adding text. And also, even when the cell is empty, you want to set the text style for formattedDate of the adding text.

Answer for question 1:

Modification points:

  • In this case, it is required to retrieve the text styles of the existing values and set the text styles after the value was added. "RichTextValue" is used for this.

When your script is modified, it becomes as follows.

Modified script:

From: 
} else {
    value = value + "\n\n" + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
    sheet.getActiveRange().setNumberFormat('@STRING@');
    cell.setValue(value);
}
To: 
} else {
  value = value + "\n\n" + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
  var richTextValue = cell.getRichTextValue();
  var existingStyles = richTextValue.getRuns().map(e => ({start: e.getStartIndex(), end: e.getEndIndex(), style: e.getTextStyle()}));
  var richTexts = SpreadsheetApp.newRichTextValue().setText(value);
  existingStyles.forEach(e => richTexts.setTextStyle(e.start, e.end, e.style));
  cell.setRichTextValue(richTexts.build());
  cell.setNumberFormat('@STRING@');
}
  • In this modified script, the text styles of the existing value are set. So the added text has no text style. Please be careful this.

Answer for question 2:

Modification points:

  • In this case, it is required to retrieve the text styles of the existing values and also set the text style for formattedDate, and then, set the text styles after the value was added. "RichTextValue" is also used for this.

When your script is modified, it becomes as follows.

Modified script:

From: 
} else {
    value = value + "\n\n" + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
    sheet.getActiveRange().setNumberFormat('@STRING@');
    cell.setValue(value);
}
To: 
} else {
  var textStyle = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor("#FF0000").build();  // Please set this for the additional text.
  
  var richTextValue = cell.getRichTextValue();
  var existingStyles = richTextValue.getRuns().map(e => ({start: e.getStartIndex(), end: e.getEndIndex(), style: e.getTextStyle()}));
  var startOffset = (value + "\n\n" + " ").length;
  existingStyles.push({start: startOffset, end: startOffset + formattedDate.length, style: textStyle});
  var richTexts = SpreadsheetApp.newRichTextValue().setText(value + "\n\n" + "到 " + formattedDate + " call " + options1 + number1 + ": " + activity1);
  existingStyles.forEach(e => richTexts.setTextStyle(e.start, e.end, e.style));
  cell.setRichTextValue(richTexts.build());
  cell.setNumberFormat('@STRING@');
}
  • In this modified script, the text styles of the existing value and formattedDate of the adding text are set.
  • As above sample, formattedDate has the bold and red font color.

Answer for question 3:

Modification points:

  • In this case, it is required to retrieve the text styles of the existing values and also set the text style for formattedDate, and then, set the text styles after the value was added. "RichTextValue" is also used for this. And also, when the cell is empty, it is required to set the text style for formattedDate of the adding text.

When your script is modified, it becomes as follows.

Modified script:

From: 
if (value === '') {
    value = value + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
    sheet.getActiveRange().setNumberFormat('@STRING@');
    cell.setValue(value);
} else {
    value = value + "\n\n" + " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
    sheet.getActiveRange().setNumberFormat('@STRING@');
    cell.setValue(value);
}
To: 
var textStyle = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor("#FF0000").build();  // Please set this for the additional text.
if (value === '') {
  value = " " + formattedDate + " call " + options1 + number1 + ": " + activity1;
  var richTexts = SpreadsheetApp
    .newRichTextValue()
    .setText(" " + formattedDate + " call " + options1 + number1 + ": " + activity1)
    .setTextStyle((" ").length, (" " + formattedDate).length, textStyle);
  cell.setRichTextValue(richTexts.build());
  cell.setNumberFormat('@STRING@');
} else {
  var richTextValue = cell.getRichTextValue();
  var existingStyles = richTextValue.getRuns().map(e => ({start: e.getStartIndex(), end: e.getEndIndex(), style: e.getTextStyle()}));
  var startOffset = (value + "\n\n" + " ").length;
  existingStyles.push({start: startOffset, end: startOffset + formattedDate.length, style: textStyle});
  var richTexts = SpreadsheetApp.newRichTextValue().setText(value + "\n\n" + "到 " + formattedDate + " call " + options1 + number1 + ": " + activity1);
  existingStyles.forEach(e => richTexts.setTextStyle(e.start, e.end, e.style));
  cell.setRichTextValue(richTexts.build());
  cell.setNumberFormat('@STRING@');
}

Reference:

user_71086530
Related