3

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();
  }
will w
  • 211
  • 1
  • 5
  • 15
  • Document Template: [link](https://docs.google.com/document/d/1BdYkmHFzXcgiTVf4Hu6YsVlt20lpCLh9JR8LHMd9itk/edit) – will w May 10 '12 at 15:31

3 Answers3

3

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.

khr055
  • 28,690
  • 16
  • 36
  • 48
Daniel
  • 46
  • 2
0

I think the problem is with this line:

docText.appendText(entryTemplate);

The variable entryTemplate holds a DocumentBodySection, which is why you are seeing that in the output. If you are trying to append another copy of the original template text you'll need to store that before you enter the loop.

Eric Koleda
  • 12,420
  • 1
  • 33
  • 51
  • I _think_ I understand what you are saying, however, the original template text includes the DocumentBodySection. Maybe this is my problem. Is there a way to copy all of the text (including formatting) in a document without including the DocumentBodySection? Right now, I am copying the text using this line `var entryTemplate = docTemplate.getActiveSection();` of code. – will w May 11 '12 at 14:26
  • It might be better to just use a different approach altogether. Rather than try to have one document that contains all the replacements, what about have one new doc per student? Or perhaps just send the email directly from Apps Script? – Eric Koleda May 11 '12 at 14:41
  • The final goal is to have 1 document that can be printed out. I would love to send instructions via email, but our district will not allow students to have email. Maybe what I want to do just isn't possible. – will w May 11 '12 at 15:01
  • I'd suggest going for Eric's solution (1 doc per student) and, in a second step, merge all these docs into one and eventually delete the originals afterwards. I posted a script to merge documents elsewhere on this forum) – Serge insas Jun 16 '12 at 08:24
0

I agree with Eric that appendText(entryTemplate) isn't going to do what you want it to do.

Since you're trying to create one large document with all the students, using a "template" and replacing the text isn't going to work well. I'd suggest instead, creating the "template" in code using the api calls that produce the formatting you want. Then it makes it simple to keep appending new pages of student instructions. Although I think you may run into slowness when the document gets large... I don't know how many students you have.

mzimmerman
  • 910
  • 6
  • 13
  • I had originally thought about doing it this way, but decided against it because I thought it would be easier for the end user to create a template document. Since this won't work, I'll start playing with the idea of creating the entire document with a script. @mzimmerman I would run this on about 1200 students. Do you think this is too many? – will w May 14 '12 at 13:36
  • That's 1200 pages in a Google Document. Performance in the browser will be slow when opening the document for sure. Maybe separate them into batches of 100 pages at a time or so. – mzimmerman May 15 '12 at 18:32
  • Ok. I am actually going to put 2-3 "students" on each page so it will be quite a bit smaller. I haven't checked yet (I'm crossing my fingers), but I am hoping that apps script will allow me to add a page break. My biggest worry is that my script will timeout. I suppose there is no way to know this without trying it. – will w May 16 '12 at 15:19
  • It almost certainly will. I think 6 minutes is the longest time for a running script, but these limits are in flux. Keep track of the "state" of where you are at and put in the ability to resume where you left off by executing the script again. – mzimmerman May 17 '12 at 20:03
  • @mzimmerman I'm interested in your comment about resuming a script when it times out. Are you saying we could do this seamlessly? Or, would the resuming require an action from the user, like clicking a resume button? – stmcallister Jul 15 '14 at 18:36
  • You can do it seamlessly by adding a trigger for the user that runs every ~4 minutes. https://developers.google.com/apps-script/reference/script/form-trigger-builder#create() After you're done with your bulk processing, make sure to clean up the trigger. – mzimmerman Jul 21 '14 at 02:07
  • Thanks, @mzimmerman! Would these be the installable triggers that "can not be used in add-ons", as per the note on this page: https://developers.google.com/apps-script/managing_triggers_programmatically – stmcallister Jul 21 '14 at 16:50
  • From the documentation, it seems like they can't be used from Add-ons, but I've never tried that. Add-ons are after my time. I haven't been developing in Apps Script as much recently. – mzimmerman Jul 21 '14 at 16:59