Google Sheets脚本：编辑日期时保留单元格富文本URL求助

背景 #

F列已启用短日期验证，所有单元格均包含指向日历事件的富文本URL。需求是编辑F列日期时同步更新对应日历事件的日期，但当前遇到问题：手动输入合规格式的日期时，单元格内的富文本URL会被覆盖删除，且无法通过e.oldValue获取原URL。需要修改脚本，确保无论用户用日期选择器还是手动输入，修改日期后原URL都能作为富文本保留，且URL不随日历事件变化。

原脚本 #

function DateEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var editedRange = e.range;
  var editedColumn = editedRange.getColumn();
  
  if (editedColumn == 6) {

  // Get event URL
      var orgNumberFormats = editedRange.getNumberFormats();
      editedRange.setNumberFormat("@");
      var richTextValue = editedRange.getRichTextValue();
      editedRange.setNumberFormats(orgNumberFormats);
      var eventUrl = richTextValue.getLinkUrl();

      // Get event ID
      if (eventUrl) {
        var eventIdEncoded = eventUrl.split("/").pop(); // Get the encoded part from the URL
        try {
          eventIdEncoded = eventIdEncoded.replace(/-/g, '+').replace(/_/g, '/'); // Revert URL-safe Base64 encoding
          eventIdEncoded += "==".substring(0, 2 - eventIdEncoded.length % 2); // Add padding if needed
          var eventIdDecoded = Utilities.base64Decode(eventIdEncoded);
          
          var eventId = Utilities.newBlob(eventIdDecoded).getDataAsString();
          var decodeEventId = eventId.split(" ")[0];
    
        } catch (e) {
          Logger.log("Error decoding and extracting the event ID: " + e);
        }
      } else {
        Logger.log("Cell contains no URL.");
      }

    // Edit Event
    try {
      var newDateStr = editedRange.getValue(); // Assuming the date is in "dd/mm/yyyy" format

      // Split the date string into parts
      var dateParts = newDateStr.split('/');
      var day = dateParts[0];
      var month = dateParts[1];
      var year = dateParts[2];

      // Create a new Date object with the adjusted date
      var newDate = new Date(year, month - 1, day); // Subtract 1 from the month because months are zero-based
  
      if (!isNaN(newDate.getTime())) {
        // The date string was successfully converted to a Date object
        // Now, you can proceed with your code to update the event date

        Logger.log("Event ID: " + decodeEventId)
  
        var calendarId = 'XYZSample@group.calendar.google.com'; // Replace with your calendar ID
        var calendar = CalendarApp.getCalendarById(calendarId);
        var event = calendar.getEventById(decodeEventId);
  
        if (event) {
          // Get the existing start and end times
          var startTime = event.getStartTime();
          var endTime = event.getEndTime();
  
          // Create new Date objects with the adjusted date but the same time
          var newStartTime = new Date(newDate);
          newStartTime.setHours(startTime.getHours(), startTime.getMinutes());
  
          var newEndTime = new Date(newDate);
          newEndTime.setHours(endTime.getHours(), endTime.getMinutes());
  
          // Update the event's start and end times with the new date
          event.setTime(newStartTime, newEndTime);


Logger.log("Event date updated successfully, and guests have been notified.");
        } else {
          Logger.log("Event not found with ID: " + decodeEventId);
        }
      } else {
        Logger.log("Invalid date format in the edited cell: " + newDateStr);
      }
    } catch (e) {
      Logger.log("Error updating event with ID: " + decodeEventId + "\n" + e.toString());
    }
  }
}

解决方案 #

核心是先提取编辑前的原URL，修改日期后将新日期文本重新绑定原URL，以富文本形式写回单元格。修改后的脚本如下：

function DateEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var editedRange = e.range;
  var editedColumn = editedRange.getColumn();
  
  if (editedColumn == 6) {
    // 1. 先获取编辑前的原富文本URL（必须在获取新值前执行，避免覆盖）
    var orgNumberFormats = editedRange.getNumberFormats();
    editedRange.setNumberFormat("@");
    var originalRichText = editedRange.getRichTextValue();
    editedRange.setNumberFormats(orgNumberFormats);
    var eventUrl = originalRichText.getLinkUrl();

    // 2. 获取新输入的日期值
    var newDate = editedRange.getValue();
    if (isNaN(newDate.getTime())) {
      Logger.log("Invalid date format in the edited cell");
      return;
    }

    // 3. 解码事件ID并更新日历事件（保留原逻辑）
    var decodeEventId = null;
    if (eventUrl) {
      var eventIdEncoded = eventUrl.split("/").pop();
      try {
        eventIdEncoded = eventIdEncoded.replace(/-/g, '+').replace(/_/g, '/');
        eventIdEncoded += "==".substring(0, 2 - eventIdEncoded.length % 2);
        var eventIdDecoded = Utilities.base64Decode(eventIdEncoded);
        var eventId = Utilities.newBlob(eventIdDecoded).getDataAsString();
        decodeEventId = eventId.split(" ")[0];
      } catch (e) {
        Logger.log("Error decoding and extracting the event ID: " + e);
      }
    } else {
      Logger.log("Cell contains no URL.");
    }

    // 更新日历事件逻辑保留
    if (decodeEventId) {
      try {
        var calendarId = 'XYZSample@group.calendar.google.com'; // 替换为你的日历ID
        var calendar = CalendarApp.getCalendarById(calendarId);
        var event = calendar.getEventById(decodeEventId);
  
        if (event) {
          var startTime = event.getStartTime();
          var endTime = event.getEndTime();
  
          var newStartTime = new Date(newDate);
          newStartTime.setHours(startTime.getHours(), startTime.getMinutes());
          var newEndTime = new Date(newDate);
          newEndTime.setHours(endTime.getHours(), endTime.getMinutes());
  
          event.setTime(newStartTime, newEndTime);
          Logger.log("Event date updated successfully, and guests have been notified.");
        } else {
          Logger.log("Event not found with ID: " + decodeEventId);
        }
      } catch (e) {
        Logger.log("Error updating event with ID: " + decodeEventId + "\n" + e.toString());
      }
    }

    // 4. 关键：将新日期重新设置为带原URL的富文本，保留链接
    if (eventUrl) {
      // 格式化日期为短日期格式（匹配你的验证格式，这里假设是dd/mm/yyyy）
      var formattedDate = Utilities.formatDate(newDate, Session.getScriptTimeZone(), "dd/MM/yyyy");
      // 创建新的富文本对象
      var newRichText = SpreadsheetApp.newRichTextValue()
        .setText(formattedDate)
        .setLinkUrl(eventUrl)
        .build();
      // 写回单元格
      editedRange.setRichTextValue(newRichText);
      // 恢复原数字格式
      editedRange.setNumberFormats(orgNumberFormats);
    }
  }
}

关键改动说明 #

• 提前获取原URL：在获取新日期值之前提取原单元格的富文本链接，确保拿到的是编辑前的有效URL
• 重新生成富文本：使用SpreadsheetApp.newRichTextValue()构建包含新日期文本和原URL的富文本对象，覆盖手动输入的纯文本
• 恢复格式：确保日期格式与原格式一致，避免显示异常

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