Google Sheets：标记Yes跨表复制后评论错位等问题

针对你遇到的两个核心问题，推荐使用Google Apps Script替代QUERY+IMPORTRANGE方案，实现更可控的数据同步，同时解决超链接和评论错位问题：

核心思路 #

1. 避免评论错位：仅在potential_presentations的「Finalized?」列从No改为Yes时，主动复制对应行到finalized_presentations；当状态改回No时，不删除目标行，仅添加标记（避免手动输入的评论丢失或移位）。
2. 保留超链接：通过脚本的单元格复制功能，完整保留原单元格的内容、格式及超链接，替代无法保留格式的IMPORTRANGE。

具体实现步骤 #

1. 打开你的Google表格，点击「扩展程序」→「Apps Script」进入脚本编辑器。
2. 替换默认代码为以下脚本：

function onEdit(e) {
  // 配置工作表名称和关键列索引
  const sourceSheetName = "potential_presentations";
  const targetSheetName = "finalized_presentations";
  const finalizedColIndex = 1; // 「Finalized?」列的索引（A列=1，B列=2，以此类推）
  const uniqueIdColIndex = 2; // 用于识别唯一行的列索引（比如标题ID列，避免重复复制）

  const sourceSheet = e.source.getSheetByName(sourceSheetName);
  const targetSheet = e.source.getSheetByName(targetSheetName);
  
  // 仅处理「potential_presentations」中「Finalized?」列的编辑操作
  if (!sourceSheet || e.range.getColumn() !== finalizedColIndex) return;

  const row = e.range.getRow();
  const newValue = e.value;
  const oldValue = e.oldValue;
  const uniqueId = sourceSheet.getRange(row, uniqueIdColIndex).getValue();

  // 当状态从No改为Yes时，复制整行到目标表（避免重复）
  if (newValue === "Yes" && oldValue === "No") {
    const existingRows = targetSheet.getDataRange().getValues();
    let isDuplicate = false;
    for (let i = 1; i < existingRows.length; i++) {
      if (existingRows[i][uniqueIdColIndex - 1] === uniqueId) {
        isDuplicate = true;
        break;
      }
    }
    if (!isDuplicate) {
      const sourceRow = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
      // 复制所有内容（包括超链接、格式）到目标表末尾
      sourceRow.copyTo(targetSheet.getRange(targetSheet.getLastRow() + 1, 1), SpreadsheetApp.CopyPasteType.PASTE_ALL, false);
    }
  }

  // 当状态从Yes改为No时，在目标行添加标记（不删除，保留评论）
  if (newValue === "No" && oldValue === "Yes") {
    const targetData = targetSheet.getDataRange().getValues();
    for (let i = 1; i < targetData.length; i++) {
      if (targetData[i][uniqueIdColIndex - 1] === uniqueId) {
        targetSheet.getRange(i + 1, targetSheet.getLastColumn() + 1).setValue("已取消确认");
        break;
      }
    }
  }
}

脚本说明 #

• 触发逻辑：每次编辑potential_presentations的「Finalized?」列时自动运行。
• 超链接保留：使用PASTE_ALL参数复制单元格，完整保留原单元格的超链接、格式和内容。
• 评论安全：状态回退时仅添加标记，不删除目标行，手动输入的comments列内容不会错位或丢失。
• 去重机制：通过唯一ID列避免同一行被重复复制到目标表。

使用注意事项 #

1. 首次运行脚本时，需要按照提示完成权限授权（Google会提示脚本需要访问表格数据，点击允许即可）。
2. 根据你的表格结构，调整finalizedColIndex（「Finalized?」列的索引）和uniqueIdColIndex（唯一标识列的索引）。
3. 如果需要自动移除「已取消确认」的行，可以修改脚本逻辑，但不建议（会丢失对应评论）。

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