Google Sheets脚本优化：行填写触发执行+插入结果表链接

需求1：仅在整行填写完成时执行脚本，避免重复创建文件 #

原脚本遍历所有行导致重复创建文件，优化思路如下：

• 新增标记列（建议用F列，标题设为「已创建结果表」），记录该行是否已生成结果表，避免重复执行
• 定义「整行完成」判断逻辑：样本ID（A列）、样本类型（D列）、样本名称（E列）均不为空，且标记列未标记为「已创建」
• 仅处理符合条件的行，而非遍历所有行

需求2：将新表格链接插入对应样本ID单元格 #

使用setFormula方法，将样本ID单元格设置为带链接的公式，格式为=HYPERLINK("表格URL", "样本ID文本")

修改后的完整脚本 #

function onEdit(e) {
  const targetSheetName = "Analysis Log";
  const activeSheet = e.source.getActiveSheet();
  // 仅当编辑的是Analysis Log表的D列（样本类型）时触发
  if (activeSheet.getName() !== targetSheetName || e.range.getColumn() !== 4) return;
  
  const now = new Date();
  // 设置日期列（B列）
  e.range.offset(0, -2).setValue(now).setNumberFormat("dd/MM/yyyy");
  // 设置时间列（C列）
  e.range.offset(0, -1).setValue(now).setNumberFormat("HH:mm");
  
  // 编辑完成后自动检查当前行是否满足创建结果表的条件
  checkAndCreateResultSheet(e.range.getRow());
}

// 检查指定行是否符合条件，符合则创建结果表
function checkAndCreateResultSheet(row) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Analysis Log');
  
  // 获取当前行关键数据
  const sampleId = sheet.getRange(row, 1).getValue();
  const sampleType = sheet.getRange(row, 4).getValue();
  const sampleName = sheet.getRange(row, 5).getValue();
  const isCreated = sheet.getRange(row, 6).getValue(); // F列作为标记列
  
  // 判断整行是否完成：关键字段非空，且未创建过结果表
  if (!sampleId || !sampleType || !sampleName || isCreated === "已创建") return;
  
  // 创建结果表
  createSampleResultSheet(row, sampleId, sampleName, sampleType, sheet.getRange(row, 2).getValue(), sheet.getRange(row, 3).getValue());
}

// 批量处理所有未创建的行（可手动运行）
function Createnewsampleresultsheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Analysis Log');
  const lastRow = sheet.getLastRow();
  const dataRange = sheet.getRange(8, 1, lastRow - 7, 6); // 从第8行开始，取A-F列数据
  const data = dataRange.getValues();
  
  data.forEach((row, index) => {
    const rowNum = 8 + index;
    const sampleId = row[0];
    const sampleType = row[3];
    const sampleName = row[4];
    const isCreated = row[5];
    
    if (!sampleId || !sampleType || !sampleName || isCreated === "已创建") return;
    
    createSampleResultSheet(rowNum, sampleId, sampleName, sampleType, row[1], row[2]);
  });
}

// 核心创建结果表的函数
function createSampleResultSheet(rowNum, sampleId, sampleName, sampleType, date, time) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Analysis Log');
  
  // 设置结果表名称
  const resultSheetName = `${sampleName}-${sampleId}`;
  
  // 复制模板到目标文件夹
  const destinationFolderId = '1mErM-8b9n-H1BXrJXrRX19F6GPVMbSce';
  const templateFileId = '1ZDy5K4l1qhQXbYsRKNBQyscU_qX_YDEMa216TaBp6iM';
  const newFile = DriveApp.getFileById(templateFileId).makeCopy(resultSheetName, DriveApp.getFolderById(destinationFolderId));
  const newFileUrl = newFile.getUrl();
  
  // 更新新结果表的信息
  const newSampleSheet = SpreadsheetApp.openById(newFile.getId());
  const resultSheet = newSampleSheet.getSheetByName("Sample results template");
  resultSheet.getRange("C2").setValue(sampleId);
  resultSheet.getRange("C3").setValue(sampleName);
  resultSheet.getRange("C4").setValue(sampleType);
  resultSheet.getRange("C5").setValue(date);
  resultSheet.getRange("C6").setValue(time);
  
  // 将样本ID单元格设置为可点击链接
  sheet.getRange(rowNum, 1).setFormula(`=HYPERLINK("${newFileUrl}", "${sampleId}")`);
  
  // 标记该行已创建结果表
  sheet.getRange(rowNum, 6).setValue("已创建");
}

关键优化点说明 #

• 避免重复创建：通过F列的「已创建」标记，确保每行仅执行一次创建逻辑；同时判断关键字段是否为空，只有整行填写完成才触发
• 提升效率：批量处理时使用getValues()一次性获取所有数据，减少多次调用getRange()的性能开销
• 自动触发：修改onEdit函数，用户填写样本类型（D列）后自动检查当前行是否符合条件，符合则自动创建结果表，无需手动运行脚本
• 链接插入：使用HYPERLINK公式将样本ID转为可点击链接，直接关联到新创建的结果表

内容的提问来源于stack exchange，提问作者Google script novice