问题与创建一个“老式”邮件与Google Apps脚本合并 [英] Issue with creating an "old-fashioned" mail merge with Google Apps Script

问题描述

我的最终脚本的目标是创建一个选举过程，以便我在使用Google表单工作的高中进行学生选举。该脚本有三个部分：1）创建独特的投票ID（随机6位代码）2）合并学生数据（姓名，家庭和投票ID）与模板文件，将创建具体的投票指令每个学生。 （即旧式邮件合并）3）通过检查投票ID并删除重复投票来验证结果。

我遇到的脚本部分是学生数据合并（步骤2）。第一个数据集是唯一可用的数据集。其余显示为DocumentBodySection。我有一种感觉，那就是我如何复制文档模板中的文本，或者我如何将文本添加到新文档中。

Spreadsheet w / Data：< a href =https://docs.google.com/spreadsheet/ccc?key=0AierVcXWELCudFI1LU10RnlIVHNsUm11a0dDWEV6M1E =nofollow> https://docs.google.com/spreadsheet/ccc?key=0AierVcXWELCudFI1LU10RnlIVHNsUm11a0dDWEV6M1E

文档模板：（请参阅url的后续评论）

脚本创建的文档： https://docs.google.com/document/d/12r2D9SpIVmQYVaasMyMWKjHz6q-ZZyIMEBGHTwlQct8/edit p>

  //获取设置& Data 
 ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 source_sheet = ss.getSheetByName（Student Data）; 
 settings_sheet = ss.getSheetByName（SETTINGS）; 
 results_column = settings_sheet.getRange（B19）。getValue（）; 
 source_column = settings_sheet.getRange（B18）。getValue（）; 
 source_lastrow = source_sheet.getLastRow（）; 
 docTemplateID = settings_sheet.getRange（B13）。getValue（）; 
 docCopyName = settings_sheet.getRange（B14）。getValue（）; 
 $ b $ //合并学生数据和文档
函数SendDataMerge（）{
 //打开docTemplate并将内容复制到entryTemplate 
 var docTemplate = DocumentApp.openById（docTemplateID） ; 
 var entryTemplate = docTemplate.getActiveSection（）; 
 docTemplate.saveAndClose（）; 
 //制作docTemplate的新副本
 var docTemplate = DocsList.getFileById（docTemplateID）; 
 var docCopy = DocsList.copy（docTemplate，docCopyName）; 
 var docCopyID = docCopy.getId（）; 
 //创建学生数据数组（首先，最后，分组，VID）
 var data = source_sheet.getRange（A2：D+ source_lastrow）.getValues（）; 
 //打开docCopy进行编辑&清除内容
 var doc = DocumentApp.openById（docCopyID）; 
 var docText = doc.editAsText（）; 
 //运行学生数据
（var i = 0; i <5 /*data.length*/; i ++）{//测试时，限制为5项
 var lastName = data [i] [0]; 
 var firstName = data [i] [1]; 
 var grouping = data [i] [2]; 
 var vid = data [i] [3]; 
 docText.replaceText（'keyLastName'，lastName）; 
 docText.replaceText（'keyFirstName'，firstName）; 
 docText.replaceText（'keyGrouping'，分组）; 
 docText.replaceText（'keyVID'，vid）; 
 docText.appendText（'\\\
 ***附加文本（输入结束）***'）; 
 docText.appendText（entryTemplate）; 
} 
 //保存并关闭
 doc.saveAndClose（）; 
 
  

解决方案

创建模板副本，进行文本替换，然后将原始文档中的模板元素附加到副本中。特别是，我使用了： var copyTables = templateDoc.getTables（）; 来获取并存储表格（因为我的所有模板数据都包含在表格中）和 copyDoc.appendTable（copyTables [0] .copy（））; 将副本（ .copy（）结束似乎工作真正的魔术）。这提供了在友好的Documents接口中更新模板的灵活性，而无需查看程序员。

This is a followup to a question I asked yesterday on the Google Apps Script Office Hours Hangout.

The goal of my final script is to create an election process for student elections at the high school where I work using Google Forms. The script has three parts: 1) Create Unique "Voting IDs" (a random 6-digit code) 2) Merge the student data (Name, Homeroom, & Voting ID) on with a template document that will create specific voting instruction for each student. (i.e. an old-fashioned mail merge) 3) Verify the results by checking Voting ID's and removing duplicate votes.

The part of the script that I am having trouble with is the student data merge (step 2). The first dataset is the only one that works. The rest show up as "DocumentBodySection". I have a feeling it is either how I am copying the text from the Document Template or how I am adding the text to the new document.

Spreadsheet w/ Data: https://docs.google.com/spreadsheet/ccc?key=0AierVcXWELCudFI1LU10RnlIVHNsUm11a0dDWEV6M1E

Document Template: (see followup comment for url)

Document Created by Script: https://docs.google.com/document/d/12r2D9SpIVmQYVaasMyMWKjHz6q-ZZyIMEBGHTwlQct8/edit

//Get Settings & Data
ss = SpreadsheetApp.getActiveSpreadsheet();
source_sheet = ss.getSheetByName("Student Data");
settings_sheet = ss.getSheetByName("SETTINGS");
results_column = settings_sheet.getRange("B19").getValue();
source_column = settings_sheet.getRange("B18").getValue();
source_lastrow = source_sheet.getLastRow();
docTemplateID = settings_sheet.getRange("B13").getValue();
docCopyName = settings_sheet.getRange("B14").getValue();

//Merge Student Data with Document
function SendDataMerge () {
  // Open docTemplate and Copy Contents to entryTemplate
     var docTemplate = DocumentApp.openById(docTemplateID);
     var entryTemplate = docTemplate.getActiveSection();
     docTemplate.saveAndClose();
  // Make a NEW copy of docTemplate
     var docTemplate = DocsList.getFileById(docTemplateID);
     var docCopy = DocsList.copy(docTemplate, docCopyName);
     var docCopyID = docCopy.getId();
  // Create Array of Student Data (First, Last, Grouping, VID)
     var data = source_sheet.getRange("A2:D"+source_lastrow).getValues();
  // Open docCopy for Editing & Clear Contents
     var doc = DocumentApp.openById(docCopyID);
     var docText = doc.editAsText();
  // Run through Student Data
     for(var i=0; i<5 /*data.length*/; i++) { //For testing, limit this to 5 entries 
       var lastName = data[i][0];
       var firstName = data[i][1];
       var grouping = data[i][2];
       var vid = data[i][3];
       docText.replaceText('keyLastName', lastName);
       docText.replaceText('keyFirstName', firstName);
       docText.replaceText('keyGrouping', grouping);
       docText.replaceText('keyVID', vid);
       docText.appendText('\n*** Appended Text (End of entry) ***');
       docText.appendText(entryTemplate);
     }
  // Save and Close 
     doc.saveAndClose();
  }

解决方案

I worked around this issue by creating a copy of the template, doing the text replacement and then appending the template elements from the original document into the copy. In particular, I used: var copyTables = templateDoc.getTables(); to fetch and store the tables (as all of my template data was contained in a table) and copyDoc.appendTable(copyTables[0].copy() ); to append the copy (the .copy() at the end seems to work the real magic). This provides the flexibility of updating the template in the friendly Documents interface without having to see a programmer.

这篇关于问题与创建一个“老式”邮件与Google Apps脚本合并的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！