如何自动复制表格并按单元格值重命名，实现行复制及自动触发

我需要实现以下功能：

• 自动复制名为Contract的电子表格
• 根据列值（客户名称）为新表格重命名
• 将对应客户详情行复制到新表格的details工作表中

目前已找到可复制文件并重命名的脚本，但缺少行复制的实现，同时希望修改现有脚本，使其无需通过菜单点击运行，而是在新增行时自动触发执行。现有脚本如下：

function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Options')
.addItem('Generate Proposal','copyfilefromsource')
.addToUi();
}
function copyfilefromsource() {
var ui = SpreadsheetApp.getUi();

var sheet_merge = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Customers");
var last_row = sheet_merge.getLastRow();

var name = null;
var phone = null;
var date = null;
var dest_folder = null;
var sheets_created = 0;
var new_file = null;
var source_file = null;
var lead = null;
var range = sheet_merge.getRange(1, 1, last_row, 30);
var temp = null;

source_file = DriveApp.getFileById('1XotVWkmjyi8OBRGy6N6mosPf16YwvozfKYxums_oMJ'); //gets the source file to copy
dest_folder = DriveApp.getFolderById('17aawdMFOvF4clNcjA_JeK2v2Q9_VJnE'); //gets the ID of the folder to place the copied files into

if (last_row <= 1) {
SpreadsheetApp.getUi().alert("No rows to process!");
return
}
for (var i = 2; i <= last_row; i++) { // loop to go down every row from 2 until the end
if (range.getCell(i, 30).getValue() == '') { //makes sure there is nothing in the column, i.e. can run the script with some students already been processed if it fails half way through.

name = range.getCell(i, 3).getValue(); //gets first name from the sheet
event_date = range.getCell(i, 6).getValue(); //gets event date from the sheet
phone = range.getCell(i, 5).getValue(); //gets phone from the sheet
lead = range.getCell(i,1).getValue(); //gets lead link
new_file = source_file.makeCopy(name + " - " + phone + " - " + event_date, dest_folder);

//Add the details that the process has worked
SpreadsheetApp.getActiveSheet().getRange(i,1).setValue('=HYPERLINK("' + new_file.getUrl() +'/","Lead")');
SpreadsheetApp.getActiveSheet().getRange(i,30).setValue(new_file.getId());
sheets_created++; // add to files created
}
}
ui.alert(sheets_created + " files were created!")
}

function testFolder(folderName){
var exist = true;
try{var testFolder = DocsList.getFolder(folderName)}
catch(err){exist=false}
return exist;
}

修改后的完整脚本 #

function onEdit(e) {
  var sheet = e.source.getSheetByName("Customers");
  // 仅处理Customers表的新增行，且第30列为空（未处理过）
  if (e.range.getSheet().getName() !== "Customers" || e.range.getRow() !== sheet.getLastRow() || sheet.getRange(e.range.getRow(), 30).getValue() !== '') {
    return;
  }

  var row = e.range.getRow();
  var sourceFileId = '1XotVWkmjyi8OBRGy6N6mosPf16YwvozfKYxums_oMJ'; // Contract模板文件ID
  var destFolderId = '17aawdMFOvF4clNcjA_JeK2v2Q9_VJnE'; // 目标文件夹ID
  
  // 获取当前行的客户数据
  var name = sheet.getRange(row, 3).getValue();
  var eventDate = sheet.getRange(row, 6).getValue();
  var phone = sheet.getRange(row, 5).getValue();
  
  // 复制模板文件并重命名
  var sourceFile = DriveApp.getFileById(sourceFileId);
  var destFolder = DriveApp.getFolderById(destFolderId);
  var newFile = sourceFile.makeCopy(`${name} - ${phone} - ${eventDate}`, destFolder);
  
  // 将当前行数据复制到新表格的details工作表
  var newSpreadsheet = SpreadsheetApp.openById(newFile.getId());
  var detailsSheet = newSpreadsheet.getSheetByName("details");
  if (detailsSheet) {
    // 清空details表原有数据（保留表头）
    detailsSheet.getRange(2, 1, detailsSheet.getLastRow()-1, detailsSheet.getLastColumn()).clearContent();
    // 复制当前行数据到details表第2行
    var sourceRowRange = sheet.getRange(row, 1, 1, 30);
    sourceRowRange.copyTo(detailsSheet.getRange(2, 1), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  }
  
  // 更新原表标记：添加新文件链接，标记已处理
  sheet.getRange(row, 1).setValue(`=HYPERLINK("${newFile.getUrl()}","Lead")`);
  sheet.getRange(row, 30).setValue(newFile.getId());
}

修改说明 #

• 自动触发逻辑：使用onEdit简单触发器，仅当Customers表新增行且该行未被处理（第30列为空）时执行，避免重复操作
• 行复制功能：打开新生成的表格，清空details工作表的原有数据（保留表头），将当前客户行的数值复制到details表的第2行
• 效率优化：仅处理新增的单行数据，无需遍历所有行，减少资源消耗
• 冗余代码清理：移除原有的菜单创建、批量处理及未使用的testFolder函数

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