Duplicate a document page and replace text on each page

Question

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

Here is what I still have:

// 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 looked at a reading tutorial from a spreadsheet, but I could not get getRowsData() and getObjects() to work correctly. My script above creates the document correctly, but does not insert the second set of information on the second page and the third set on the third page, etc.

Answer 1

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

 //... 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(); 

Answer 2

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();