Google Sheets与Docs邮件合并：文件未生成在指定文件夹

你的代码核心问题在于复制模板文档时未指定目标文件夹，默认会生成在Drive根目录，且后续没有执行将文件移动到目标文件夹的操作。以下是具体修改方案：

关键修改点 #

1. 给createMergedDocument函数添加目标文件夹参数，复制模板时直接指定文件夹，避免后续移动操作
2. 移除无效的DriveApp.getFileById(newDocId);代码行
3. 优化复制逻辑，复制时直接设置新文档名称，减少重复操作

修改后的完整代码 #

/***
  <function>
    <name>mergeLetters</name>
    <purpose>Loop through spreadsheet data and merge with a template document.</purpose>
    <example>TODO</example>
  </function>
***/
function mergeLetters () {
  // Set the debug flag
  var debug = false;

  // Declare variables.
  var myDataSheet, myVariablesSheet;
  var rowId, maxRows, maxRowsOverride;
  var templateDocId;
  var timeZone, timestamp, dateline;
  var newFolder, newFolderId, collectionDate, collectionName, appendTimestamp;
  var newDocNameBase, newDocNameSuffixCol, newDocName, newDoc, newDocId;
  var fieldColRow, fieldArr, colArr;

  if(debug) Browser.msgBox("Starting merge...");


  // Declare and initialize the data and variable sheets
  myDataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  myVariablesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Variables");
  

  // Get the row from which to start merging data.
  rowId = myVariablesSheet.getRange("B6").getValue();
  if(debug) Browser.msgBox("rowId = " + rowId);


  // Get the row at which to end merging data.
  maxRowsOverride = myVariablesSheet.getRange("B8").getValue();
  if(debug) Browser.msgBox("maxRowsOverride = " + maxRowsOverride);

  if(typeof maxRowsOverride == 'undefined' || maxRowsOverride == '') {
    maxRows = myDataSheet.getLastRow();
  } else {
    maxRows = maxRowsOverride;
  }
  if(debug) Browser.msgBox("maxRows = " + maxRows);


  // Get the ID of the template doc that will be merged with the spreadsheet data.
  templateDocId = myVariablesSheet.getRange("B5").getValue();
  if(debug) Browser.msgBox("templateDocId = " + templateDocId);


  // Setup the current timestamp and timezone.
  timeZone = myVariablesSheet.getRange("B13").getValue();
  dateline = myVariablesSheet.getRange("B7").getValue();

  if(typeof timeZone == 'undefined' || timeZone == '') {
    timeZone = 'GMT';
  }
  if(typeof dateline == 'undefined' || dateline == '') {
    dateline = Utilities.formatDate(new Date(), timeZone, "MMMMM' 'd', 'yyyy");
  } else {
    dateline = Utilities.formatDate(dateline, timeZone, "MMMMM' 'd', 'yyyy");
  }
  if(debug) Browser.msgBox("dateline = " + dateline + "\nntimeZone = " + timeZone);


  // Create new collection.
  collectionDate = Utilities.formatDate(new Date(), timeZone, "yyyy-MM-dd' 'HH:mm");
  collectionName = myVariablesSheet.getRange("B11").getValue();

   if(typeof collectionName == 'undefined' || collectionName == '') {
    collectionName = "Merged Collection";
  }


  appendTimeStamp = myVariablesSheet.getRange("B12").getValue();
  if(appendTimeStamp == 'NO') 
  
  {
    newFolder = DriveApp.createFolder(collectionName); 
  } else {
    newFolder = DriveApp.createFolder(collectionName + ": " + collectionDate);
  }

  newFolderId = newFolder.getId();
  if(debug) Browser.msgBox("newFolder = " + newFolder.getName() + "\nnewFolderId =" + newFolderId);


  // Setup the mail merge fields array.
  fieldColRow = 18;
  fieldArr = new Array(
    myVariablesSheet.getRange("C" + fieldColRow).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 1)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 2)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 3)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 4)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 5)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 6)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 7)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 8)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 9)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 10)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 11)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 12)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 13)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 14)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 15)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 16)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 17)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 18)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 19)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 20)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 21)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 22)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 23)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 24)).getValue(),
    myVariablesSheet.getRange("C" + (fieldColRow + 25)).getValue()
  );


  // Merge the data with the template.
  // Begin letter creation.
  while (rowId <= maxRows) {
    // Get the data for rowId.
    colArr = new Array(
      myDataSheet.getRange("A" + rowId).getValue(),
      myDataSheet.getRange("B" + rowId).getValue(),
      myDataSheet.getRange("C" + rowId).getValue(),
      myDataSheet.getRange("D" + rowId).getValue(),
      myDataSheet.getRange("E" + rowId).getValue(),
      myDataSheet.getRange("F" + rowId).getValue(),
      myDataSheet.getRange("G" + rowId).getValue(),
      myDataSheet.getRange("H" + rowId).getValue(),
      myDataSheet.getRange("I" + rowId).getValue(),
      myDataSheet.getRange("J" + rowId).getValue(),
      myDataSheet.getRange("K" + rowId).getValue(),
      myDataSheet.getRange("L" + rowId).getValue(),
      myDataSheet.getRange("M" + rowId).getValue(),
      myDataSheet.getRange("N" + rowId).getValue(),
      myDataSheet.getRange("O" + rowId).getValue(),
      myDataSheet.getRange("P" + rowId).getValue(),
      myDataSheet.getRange("Q" + rowId).getValue(),
      myDataSheet.getRange("R" + rowId).getValue(),
      myDataSheet.getRange("S" + rowId).getValue(),
      myDataSheet.getRange("T" + rowId).getValue(),
      myDataSheet.getRange("U" + rowId).getValue(),
      myDataSheet.getRange("V" + rowId).getValue(),
      myDataSheet.getRange("W" + rowId).getValue(),
      myDataSheet.getRange("X" + rowId).getValue(),
      myDataSheet.getRange("Y" + rowId).getValue(),
      myDataSheet.getRange("Z" + rowId).getValue()
    );

    
    newDocNameBase = myVariablesSheet.getRange("B9").getValue();
    newDocNameSuffixCol = myVariablesSheet.getRange("B10").getValue();
    
    // Put together newDocName.
    if (typeof newDocNameBase == 'undefined' || newDocNameBase == '') {
      newDocNameBase = "Letter of Rep";
    }
    if (typeof newDocNameSuffixCol == 'undefined' || newDocNameSuffixCol == '') {
      newDocNameSuffixCol = 'A';
    }
    newDocName = newDocNameBase + " " + myDataSheet.getRange(newDocNameSuffixCol + rowId).getValue();

    // 传入newFolder参数
    newDoc = createMergedDocument(templateDocId, newDocName, dateline, fieldArr, colArr, debug, newFolder);
    newDocId = newDoc.getId();
    // 移除无效的getFileById调用
    SpreadsheetApp.flush();
    
    rowId++;
  };

  Browser.msgBox("Merge complete.");
}


/***
  <function>
    <name>createMergedDocument</name>
    <purpose>This function takes a document template ID, a new document name, a date, an array of field names
      and array of data, and a debug flag. It will merge the data into a copy of the template document, which
      is renamed to the new document name, and it returns the new document.</purpose>
    <example>TODO</example>
  </function>
***/
// 添加newFolder参数
function createMergedDocument (templateDocId, newDocName, dateline, fieldArr, colArr, debug, newFolder) {
  if (typeof debug == 'undefined') debug = false;

  // 复制模板时直接指定目标文件夹和新名称
  var newFile = DriveApp.getFileById(templateDocId).makeCopy(newDocName, newFolder);
  var newDocId = newFile.getId();
  
  var newDoc = DocumentApp.openById(newDocId);
 
  var newBody = newDoc.getActiveSection();

  // Replace variables with spreadsheet data.
  var i;
  //Browser.msgBox("fieldArr.length = " + fieldArr.length);
  for(i = 0; i < fieldArr.length; i++) {
    //Browser.msgBox("fieldArr[" + i + "] = " + fieldArr[i] + "\ncolArr[" + i + "] = " + colArr[i]);
    newBody.replaceText(fieldArr[i], colArr[i]);
  }
  newBody.replaceText("%dateline%", dateline);

  if(debug) Browser.msgBox("Document Created");

  SpreadsheetApp.flush();

  return newDoc;
}

修改说明 #

1. 传递文件夹对象：在mergeLetters中调用createMergedDocument时，把创建好的newFolder作为参数传入
2. 直接在目标文件夹复制：使用makeCopy(newDocName, newFolder)方法，复制模板时直接将新文档生成在指定文件夹内，同时设置好文档名称，省去后续改名步骤
3. 移除无效代码：删除原代码中仅获取文件但无实际操作的DriveApp.getFileById(newDocId);行

这样修改后，生成的所有合并文档都会直接存放在你创建的目标文件夹中，不会出现在Drive根目录。

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