Employee of the week award script modification not working

Question

I have modified the "employee of the week award" and cannot get it to pull accurate data to my document upon from submission.

My goal is to:

1) Fill out the form with the required details

2) Have the script gather those details and insert them into my docs template in the proper key holders

3) Create a 'new' copy of the doc in a pdf with title change

4) Email would be great but not necessary as long as it saves the new document

I can get it to create a new document and run the script but it does not seem to be pulling the correct (or any) data from the form or spreadsheet. It keeps showing 'undefined' where it should be showing the values I inputed into the form. Please help me see my errors. I have literally spent hours trying to figure it out and re-write code. Thanks in advance!

Here are links to my document and spreadsheet, form, and script I am using.

https://docs.google.com/document/d/1A34uyNyMzp3o8XBzmIGqqfvCH6ocpQWN4HaZ5f4AiDk/edit

https://docs.google.com/spreadsheet/ccc?key=0AkKN7xCpxU54dENoZ0RoSnF1QXhQNnAyX3ZiNmVwRGc#gid=0

Here is my current script:

function sendDocument() {
 var sheet = SpreadsheetApp.getActiveSheet();  
 var startRow = sheet.getLastRow();  // First row of data to process  
 var numRows = 1;   // Number of rows to process  // Fetch the range of cells  
 var dataRange = sheet.getRange(startRow, 1,2)  // Fetch values for each row in the Range.  
 var data = dataRange.getValues();  
for (i in data) {    
 var row = data[i];    
 var ID = row[1];  // First column    
 var facility_name = row[2];       // Second column  
// Get document template, copy it as a new temp doc, and save the Doc’s id
 var copyId   = DocsList.getFileById("1A34uyNyMzp3o8XBzmIGqqfvCH6ocpQWN4HaZ5f4AiDk")
            .makeCopy("Copy of Mail Merge Doc template"+' for '+facility_name)
            .getId();
// Open the temporary document
 var copyDoc  = DocumentApp.openById(copyId);
// Get the document’s body section
 var copyBody = copyDoc.getActiveSection();
// Replace place holder keys/tags,  
 copyBody.replaceText('keyFacilityName', facility_name);
 copyBody.replaceText('keyID', ID);
 var todaysDate =  Utilities.formatDate(new Date(), "GMT", "MM/dd/yyyy");
 copyBody.replaceText('keyTodaysDate', todaysDate);
// Save and close the temporary document
 copyDoc.saveAndClose();
// Convert temporary document to PDF by using the getAs blob conversion
 var pdf = DocsList.getFileById(copyId).getAs("application/pdf");
// Delete temp file
 DocsList.getFileById(copyId).setTrashed(false);  
}}

I have also tried the following two options at the beginning of my script to 'pull' the data from the form to create the 'new' doc with no success...

// Global variables 
docTemplate = “1A34uyNyMzp3o8XBzmIGqqfvCH6ocpQWN4HaZ5f4AiDk”;
docName = “Copy of Mail Merge Doc template”;

function sendDocument() {
// Full name and email address values come from the spreadsheet form
   var ID = from-spreadsheet-form
   var facility_name = from-spreadsheet-form

...continue script as above...

AND

// Global variables 
docTemplate = “1A34uyNyMzp3o8XBzmIGqqfvCH6ocpQWN4HaZ5f4AiDk”;
docName = “Copy of Mail Merge Doc template”;

function onFormSubmit(e) {
// Full name and email address values come from the spreadsheet form
   var ID = e.values[1];
   var facility_name = e.values[2];

...continue script as above...

Please help if possible.

score 0 · Accepted Answer · answered Feb 05 '13 at 19:39

your script is quite .... how should I say... approximative ?

I didn't test it and couldn't see your spreadsheet since you forgot to make them viewable publicly but I can suggest a first part of the script with some corrections and explanations in the comments.

Hoping it will help you to make it work.

function sendDocument() {
 var sheet = SpreadsheetApp.getActiveSheet();  
 var startRow = sheet.getLastRow();  // First row of data to process  
 var numRows = 1;   // Number of rows to process  // Fetch the range of cells  
 var dataRange = sheet.getRange(startRow, 1,numRows,sheet.getLastColumn())  // Fetch values for each row in the Range. Needs 4 parameters :start row, start column, number of rows, number of columns 
 var data = dataRange.getValues();  //returns a 2D array with 0 indexed values : data[0] is row nr 1 and data[0][0] is first cell in this first row
for (i in data) {    
 var row = data[i];    
 var ID = row[0];  // First column is index 0   
 var facility_name = row[1];       // Second column is index 1

Since you said the doc creation was working there is a chance these changes can make it ;-)

Thank you for the response. I have shared the links now. My apologies for not doing that earlier. I will try your suggestion and let you know how I fair. If you would like to look at the links now that would be appreciated. — user2043853, Feb 06 '13 at 02:28
Just saw your message (European time) , glad it helped ! - pls consider accepting the answer — Serge insas, Feb 06 '13 at 06:06

score 0 · Answer 2 · answered Feb 06 '13 at 03:28

Here is my new script that seems to be working great. Thanks for the help.

function sendDocument() {
 var sheet = SpreadsheetApp.getActiveSheet();  
 var startRow = sheet.getLastRow();  // First row of data to process  
 var numRows = 1;   // Number of rows to process  // Fetch the range of cells  
 var dataRange = sheet.getRange(startRow, 1,numRows,sheet.getLastColumn())  // Fetch values for each row in the Range. Needs 4 parameters :start row, start column, number of rows, number of columns 
 var data = dataRange.getValues();  //returns a 2D array with 0 indexed values : data[0] is row nr 1 and data[0][0] is first cell in this first row
for (i in data) {    
 var row = data[i];    
 var ID_ = row[1];  // First column is index 0   
 var facility_name = row[2];       // Second column is index 1  
 var facility_type = row[3]; 
 var Length_ = row[4];
 var Acres_ = row[5]; 
 var Submission_Date = row[6];
 var email_address1 = row[7];
 var email_address2 = row[8];
// Get document template, copy it as a new temp doc, and save the Doc’s id
 var copyId   = DocsList.getFileById("1PsSwL9-w3R51C3RcWCuVwHWKYSxw9btJDVRuGgipDAI")
            .makeCopy("POD BBC Prickly Pear Contraction POD template"+' for '+ID_)
            .getId();
// Open the temporary document
 var copyDoc  = DocumentApp.openById(copyId);
// Get the document’s body section
 var copyBody = copyDoc.getActiveSection();
// Replace place holder keys/tags,  
 copyBody.replaceText('keyID', ID_);
 copyBody.replaceText('keyFacilityName', facility_name);
 copyBody.replaceText('keyFacilityType', facility_type);
 copyBody.replaceText('keyLength', Length_);
 copyBody.replaceText('keyAcres', Acres_);
 copyBody.replaceText('keySubmissionDate', Submission_Date);
// Save and close the temporary document
 copyDoc.saveAndClose();
// Convert temporary document to PDF by using the getAs blob conversion
 var pdf = DocsList.getFileById(copyId).getAs("application/pdf");
  // Attach PDF and send the email
   var subject = "POD BBC Prickly Pear Contraction"+' for '+ID_;
   var body    = "Document for POD BBC Prickly Pear Contraction"+' for '+ID_+" has been created.  Please see attached PDF";
   MailApp.sendEmail(email_address1, subject, body, {htmlBody: body, attachments: pdf});
   MailApp.sendEmail(email_address2, subject, body, {htmlBody: body, attachments: pdf});
// Delete temp file
 DocsList.getFileById(copyId).setTrashed(false);  
}}

If you want to delete the doc you should use setTrashed(true) in the last part of code... — Serge insas, Feb 06 '13 at 07:14
I want to keep the doc for now. On another note, I would like to open multiple files and merge / combine, in a specific order, them into one PDF document. Any suggestions on how I would add this to my script? — user2043853, Feb 06 '13 at 10:05
have a look at this post [merging docs without blanks](http://stackoverflow.com/questions/10939031/how-to-delete-blank-pages-in-a-multipage-document) — Serge insas, Feb 06 '13 at 10:57

Employee of the week award script modification not working

2 Answers2