如何用可安装onEdit触发器恢复Google Sheets F列富文本旧URL

我的F列单元格存储着包含URL的富文本内容，用户编辑该列时（双击文本或直接单击输入），富文本格式会被覆盖，导致URL丢失。我需要一个可安装的onEdit触发器，在F列单元格被编辑时保留旧URL，并将其以富文本形式恢复到单元格中。我知道可以访问e.oldValue，但不知道如何获取旧URL（因为编辑后富文本已被覆盖）。

备注：该列启用了数据验证，无法直接使用超链接，必须使用富文本。

附初始添加URL为富文本的代码：

function ShowStatusEdit(e) {
  const editedColumn = e.range.getColumn(); // 获取编辑的列号

  // 检查编辑的列是否不是N列（索引14）
  if (editedColumn !== 14) {
    return; // 提前退出函数
  }

  const editedValue = e.value; // 获取编辑后的值

  // 当N列编辑为"Confirmed"时执行逻辑
  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();

    // 检查eventCategory开头是否不是"Sold"、"Fest"或"Tour"
    if (eventCategory.indexOf("Sold") !== 0 && eventCategory.indexOf("Fest") !== 0 && eventCategory.indexOf("Tour") !== 0) {
    
      // 创建日历活动
      if (showDate instanceof Date) {
        const startTime = new Date(showDate);
        startTime.setHours(19, 0, 0, 0); // 设置时间为19:00
        const endTime = new Date(showDate);
        endTime.setHours(23, 30, 0, 0); // 设置时间为23:30

          const calendarId = 'XXXtest@group.calendar.google.com'; // 替换为你的日历ID
          const calendar = CalendarApp.getCalendarById(calendarId);
          const eventTitle = sheet.getRange(row, 7).getValue();
          const eventDescription = `This is a test event`;
          const guestlist = ['xxxtest@gmail.com'];

          const eventOptions = {
            description: eventDescription,
            guests: guestlist.join(),
            sendInvites: true,
            sendUpdates: "all",
          };

          const newEvent = calendar.createEvent(eventTitle, startTime, endTime, eventOptions);

          // 通过日历活动ID获取活动URL
          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;

          // 将showDate格式化为"DD/MM/YYYY"字符串
          var formattedDate = Utilities.formatDate(showDate, "GMT+0200", "dd/MM/yyyy");

          // 创建带URL的富文本
          const richTextWithUrl = SpreadsheetApp.newRichTextValue()
          .setText(formattedDate)
          .setLinkUrl(eventUrl)
          .build();

          // 将富文本设置到F列（第6列）
          sheet.getRange(row, 6).setRichTextValue(richTextWithUrl);

        } else {
          Logger.log('无效的演出日期格式: ' + showDate);
        // 处理日期格式不符合要求的情况
        }

  } else {
        Logger.log('活动类别以"Sold"、"Fest"或"Tour"开头: ' + eventCategory);
        // 处理活动类别开头为指定字符串的情况
    }
}
}

核心逻辑 #

普通onEdit简单触发器无法可靠捕获编辑前的富文本状态，必须使用可安装的onEdit触发器，在编辑发生时立即提取旧单元格的URL，再将用户输入的新文本与旧URL重新绑定为富文本。

代码实现 #

function protectFColumnRichText(e) {
  // 仅处理F列（第6列）的编辑
  if (e.range.getColumn() !== 6) return;

  const targetRange = e.range;
  // 获取编辑前的完整富文本对象
  const oldRichText = targetRange.getRichTextValue();
  if (!oldRichText) return;

  // 提取旧URL
  const oldUrl = oldRichText.getLinkUrl();
  if (!oldUrl) return;

  // 获取用户输入的新文本，若清空则保留原文本
  const newText = e.value || oldRichText.getText();

  // 重新创建带旧URL的富文本
  const newRichText = SpreadsheetApp.newRichTextValue()
    .setText(newText)
    .setLinkUrl(oldUrl)
    .build();

  // 将新富文本写回单元格
  targetRange.setRichTextValue(newRichText);
}

配置步骤 #

1. 打开Google Apps脚本编辑器，粘贴上述代码；
2. 点击左侧「触发器」图标，添加新触发器：
   • 选择函数：protectFColumnRichText
   • 选择部署类型：「Head」
   • 选择事件源：「从电子表格提交」
   • 选择事件类型：「编辑」
3. 授权脚本权限，完成配置。

备选方案（辅助列备份） #

如果担心可安装触发器的延迟，可以用onSelectionChange提前将F列的URL备份到隐藏列（比如AA列），编辑时从备份列读取URL：

// 选中单元格时备份URL到AA列
function backupUrlOnSelection(e) {
  const range = e.range;
  if (range.getColumn() !== 6) return;

  const sheet = range.getSheet();
  const row = range.getRow();
  const richText = range.getRichTextValue();
  const url = richText ? richText.getLinkUrl() : "";
  
  // 保存URL到AA列（可右键隐藏该列）
  sheet.getRange(row, 27).setValue(url);
}

// 编辑时恢复URL
function restoreUrlOnEdit(e) {
  if (e.range.getColumn() !== 6) return;

  const sheet = e.source.getActiveSheet();
  const row = e.range.getRow();
  const backupUrl = sheet.getRange(row, 27).getValue();
  if (!backupUrl) return;

  const newText = e.value || e.range.getRichTextValue().getText();
  const newRichText = SpreadsheetApp.newRichTextValue()
    .setText(newText)
    .setLinkUrl(backupUrl)
    .build();

  e.range.setRichTextValue(newRichText);
}

配置时需要同时添加backupUrlOnSelection的「选择更改」触发器，以及restoreUrlOnEdit的「编辑」触发器。

兼容性说明 #

你提供的ShowStatusEdit代码与上述保护逻辑完全兼容，不会互相干扰。

内容的提问来源于stack exchange，提问作者LionelHutz