将旧的 RTF URL 设置为编辑的单元格

问：

我的列 F 包含一个富文本格式 （rtf） 的值，该值还包含一个 URL。

当用户编辑此列中的单元格时，根据他们的操作方式（双击文本，或只需单击单元格并开始键入），他们能够覆盖富文本，因此 URL 会消失。

我需要一个可安装的 onEdit 触发器来保存旧 URL，并在编辑 F 列中的单元格时将其作为 RTF 放回单元格。

我知道我可以访问旧的 VALUE （e.oldValue），但我不知道如何访问旧的 URL（因为它是富文本格式，并且在编辑时已被覆盖）。

有什么想法吗？

我必须在这里使用 RTF，因为该列已激活数据验证并且不接受超链接。

编辑：这是用于最初将URL添加为富文本的代码。

    function ShowStatusEdit(e) {
      const editedColumn = e.range.getColumn(); // Get the edited column number
    
      // Check if the edited column is NOT Column N (index 14)
      if (editedColumn !== 14) {
        return; // Exit the function early
      }
    
      const editedValue = e.value; // Get the edited value
    
      // Continue with logic for Column N edits and "Confirmed" value
      if (editedValue === "Confirmed") {
        const ss = e.source;
        const sheet = ss.getActiveSheet();
        const row = e.range.getRow();
        const eventCategory = sheet.getRange(row, 11).getValue();
        const showDate = sheet.getRange(row, 6).getValue();
    
        // Check if the first four characters of eventCategory are not "Sold", "Fest", or "Tour"
        if (eventCategory.indexOf("Sold") !== 0 && eventCategory.indexOf("Fest") !== 0 && eventCategory.indexOf("Tour") !== 0) {
        
      // CALENDAR ENTRY FOR SHOW
        if (showDate instanceof Date) {
          const startTime = new Date(showDate);
          startTime.setHours(19, 0, 0, 0); // Set the time to 19:00
          const endTime = new Date(showDate);
          endTime.setHours(23, 30, 0, 0); // Set the time to 23:30
    
            const calendarId = '[email protected]'; // Replace with your calendar ID
            const calendar = CalendarApp.getCalendarById(calendarId);
            const eventTitle = sheet.getRange(row, 7).getValue();
            const eventDescription = `This is a test event`;
            const guestlist = ['[email protected]'];
    
            const eventOptions = {
              description: eventDescription,
              guests: guestlist.join(),
              sendInvites: true,
              sendUpdates: "all",
            };
    
            const newEvent = calendar.createEvent(eventTitle, startTime, endTime, eventOptions);
    
            // Get the event URL using the CalendarApp event's ID
            var ati = calendar.getId().indexOf("@");
            var splitEventId = newEvent.getId().split('@');
            var EventId = Utilities.base64Encode(splitEventId[0] + " " + calendar.getId().substring(0, ati + 2)).replace(/=/gi, '');
            var eventUrl = "https://calendar.google.com/calendar/u/0/r/eventedit/" + EventId;
    
            // Format the showDate variable as a string in the "DD/MM/YYYY" format
            var formattedDate = Utilities.formatDate(showDate, "GMT+0200", "dd/MM/yyyy");
    
            // Create a new rich text value with the event URL
            const richTextWithUrl = SpreadsheetApp.newRichTextValue()
            .setText(formattedDate)
            .setLinkUrl(eventUrl)
            .build();
    
            // Set the updated rich text value in column 6 (F)
            sheet.getRange(row, 6).setRichTextValue(richTextWithUrl);
    
          } else {
            Logger.log('Invalid show date format: ' + showDate);
          // Handle the case where the show date format is not as expected
          }

}else {
      Logger.log('Event category starts with "Sold," "Fest," or "Tour": ' + eventCategory);
        // Handle the case where the event category starts with "Sold," "Fest," or "Tour"
    }
}
}