Duplicating a page of a document and replacing text on each page

Question

I am trying to create a script that will take a Google Doc template document, make a copy, replace certain text with information from a row on my spreadsheet, append another page, replace the text with information from the next row on the spreadsheet, append another page, etc.

Here is what I have so far:

// Global variables 
var templateDocID = ScriptProperties.getProperty("backRxRequestDocID");
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var activeSheetName = sheet.getName();
var user = Session.getUser().getEmail();

function requestGen3() {
  var physName = ["doc john", "doc evan", "doc jane"];
  var physAddr1 = ["fake st.", "faker st.", "fakest st."];
  var physAddr2 = ["ste 100", "", "ste 209"];
          var physCity = ["SLC", "Provo", "Orem"];
  var physState = ["UT", "AZ", "NV"];
  var physZip = ["84049", "84044", "84601"];
          var physPhone = ["8015555555", "7206666666", "4803333333"];
  var ptName = ["ed", "sue", "izzy"];
  var ptDOB = ["12/10/1979", "1/1/2001", "45/94/4561"];
  // Get document template, copy it as a new temp doc, and save the Doc’s id
  var docID = DocsList.getFileById(templateDocID).makeCopy().getId();
  var doc = DocumentApp.openById(docID);
  var body = doc.getActiveSection();
  var pars = doc.getParagraphs();
  var bodyCopy = body;
  for (var i = 0; i < physName.length; ++i) {
    // Replace place holder keys,  
    body.replaceText('%PHYS_NAME%', physName[i]);
    body.replaceText('%PHYS_ADDR1%', physAddr1[i]);
    body.replaceText('%PHYS_ADDR2%', physAddr2[i]);
    body.replaceText('%PHYS_CITY%', physCity[i]);
    body.replaceText('%PHYS_STATE%', physState[i]);
    body.replaceText('%PHYS_ZIP%', physZip[i]);
    body.replaceText('%PHYS_PHONE%', physPhone[i]);
    body.replaceText('%PT_NAME%', ptName[i]);
    body.replaceText('%PT_DOB%', ptDOB[i]);    
    doc.appendPageBreak();
    for (var j = 0; j < pars.length; ++j) {
      doc.appendParagraph(pars[j].copy());
    }
  }

  // Save and close the document
  doc.saveAndClose();
}

I went through the tutorial on reading from the spreadsheet but I couldn't seem to make getRowsData() and getObjects() to work properly. My script above is creating the document properly but is not inserting the second set of info into the second page and third set into the third page, etc.

score 3 · Accepted Answer · answered May 31 '12 at 00:37

You have to replace the text only after you copied the paragraphs, because if you do it after, the placeholders will be already replaced and will not be present for the next copies. You can do it like this:

//...
var pars = doc.getParagraphs();
for( var i in pars ) //loop to keep a copy of the original paragraphs
  pars[i] = pars[i].copy();

for( var i = 0; i < physName.length; ++i ) {
  body.replaceText('%PHYS_NAME%', physName[i]);
  //do all your replaces...
  if( i != physName.length-1 ) { //has next?
    doc.appendPageBreak();
    for( var j in pars )
      doc.appendParagraph(pars[j].copy());
  }
}
doc.saveAndClose();

thank you. that worked perfectly for adding pages, quite simple but i'm new to this. any tips on getting the getRowsData() function to read the lines of my spreadsheet to use in place of the arrays i manually typed out to replace the text of the document. again, any help is very much appreciated. — Evan Caldwell, Jun 01 '12 at 23:17

score 0 · Answer 2 · answered Mar 05 '14 at 00:23

0

The following code will read your spreadsheet and create a matrix called data.

var sh = SpreadsheetApp.getActive().getActiveSheet();
var lastRow = sh.getLastRow();
var lastCol = sh.getLastColumn();
var data = sh.getRange(1, 1, lastRow, lastCol).getValues();

answered Mar 05 '14 at 00:23

Sam

1

Actually you don't need to get the last row and column. Just do a: `var data = SpreadsheetApp.getActiveSheet().getDataRange().getValues()` – Henrique G. Abreu Mar 17 '14 at 11:15

Duplicating a page of a document and replacing text on each page

2 Answers2

Linked