- Google spreadsheet with address data
- Google document, used as template containing placeholder for replacement:
- ###LABEL### (address label)
- ###SALUTATION### (personalized start of the letter, e.g "Dear Mr. / Mrs.")
- Result: Google document containing all letters based on the replacements from above (easy printing)
1. Create standalone app in Google Drive
New -> Script (if script is not available you haveto connect the api to your drive by clicking more...)
2. Insert webapp code.
The doGet() method is the entry point, if the script is loaded. It just opens a file picker dialog where the base template document can be selected. Afterwards again the picker is called but to select the spreadsheet containing the series data. At the end the generation is triggered and a link is provided.
Code.gs
/**
* Init
*/
function doGet(){
var app = UiApp.createApplication();
var templateId = app.createHidden("templateId").setId("templateId");
app.add(templateId);
showPicker(app, UiApp.FileType.DOCUMENTS);
return app;
}
/**
* Handler for selected document
*/
function listSelectedDoc_(e){
var app = UiApp.getActiveApplication();
app.getElementById('templateId').setValue(e.parameter.items[0].id);
showPicker(app, UiApp.FileType.SPREADSHEETS);
return app;
}
/**
* Handler for selting spreadsheet
*/
function listSelectedSpreadsheet_(e) {
var app = UiApp.getActiveApplication();
var spreadsheetId = e.parameter.items[0].id;
var templateId = e.parameter.templateId;
var folderId = DocsList.getFileById(templateId).getParents()[0].getId();
var bl = new BulkLetterGenerator(folderId, spreadsheetId, templateId);
var generated = bl.generate();
if(generated) {
var anchor = app.createAnchor("Open bulk letter", generated);
app.add(anchor);
}
return app.close();
}
/**
* Show a picker: folder, docs or tables
*/
function showPicker(app, type) {
switch(type) {
case UiApp.FileType.DOCUMENTS:
var title = "Select a template document";
var handler = app.createServerHandler('listSelectedDoc_');
break;
case UiApp.FileType.SPREADSHEETS:
var title = "Select a spreadsheet with addresses";
var handler = app.createServerHandler('listSelectedSpreadsheet_');
handler.addCallbackElement(app.getElementById("templateId"));
break;
}
var dialog = app.createDocsListDialog();
dialog.addSelectionHandler(handler);
dialog.setDialogTitle(title);
dialog.setInitialView(type);
dialog.setMultiSelectEnabled(false);
dialog.showDocsPicker();
}
Notes: Because it is not possible to use the handler results in callback functions (asynchronous sever call), I used a hidden element an pass it to addCallbackElement. Maybe anyone more experienced can provide a better solution... it just was my first script (try)...3. BulkLetterGenerator Class
As the name implies, it provides the needed method to generate the bulk letter (in the same folder as the template lives in). It has only only one public method: generate() which returns the URL to the generated document. Inside some reflection is used to copy data from template document.
Notes: For other languages than English, the block below the comment "prepare spreadsheet members & replace" should be adopted. Also German address labels standard is used in this case.
BulkLetterGenerator.gs
function BulkLetterGenerator(targetId, spreadsheetId, templateId) {
var that = this;
this.targetId = targetId;
this.spreadsheetId = spreadsheetId;
this.templateId = templateId;
/**
* Generating bulk letter
*/
this.generate = function () {
var membersTable = SpreadsheetApp.openById(that.spreadsheetId);
var members = membersTable.getDataRange().getValues();
var template = DocumentApp.openById(that.templateId);
var templateBody = template.getActiveSection();
// create new document
var bulk = getBulkDocument(template);
// interate over recipients (starting from 1, zero is headline usually)
for (var i = 1; i < members.length; i++) {
// copy elements
for (var j = 0; j < template.getNumChildren(); j++) {
bulk = appendElementToDoc(bulk, template.getChild(j).copy());
}
// prepare spreadsheet members & replace
var label = members[i][0] +"\n"+ members[i][1] +" "+ members[i][2] +"\n"+ members[i][3] + "\n" + members[i][4];
var salutation += " " + members[i][0] +" "+ members[i][2];
bulk = bulk.replaceText('###LABEL###', label).replaceText('###SALUTATION###', salutation);
bulk.appendPageBreak();
}
bulk.saveAndClose();
return bulk.getUrl();
}
/**
* Copy elements to new doc
*/
function appendElementToDoc(bulk, element) {
var tName = underscoreToCamelCase(element.getType() + "");
try {
bulk["append" + tName](element);
}
catch(err) {
Logger.log(err + "");
}
return bulk;
}
/**
* Transform typename to function name
*/
function underscoreToCamelCase(type) {
type = type.toLowerCase();
var tName = type.charAt(0).toUpperCase() + type.slice(1);
var parts = tName.split("_");
if(parts.length == 2) {
tName = parts[0] + parts[1].charAt(0).toUpperCase() + parts[1].slice(1);
}
return tName;
}
/**
* Getting bulk document to insert content
*
* @param Document
* @return Document
*/
function getBulkDocument(template) {
var bulk = DocumentApp.create("Bulk Letter");
bulk.setMarginBottom(template.getMarginBottom());
bulk.setMarginLeft(template.getMarginLeft());
bulk.setMarginRight(template.getMarginRight());
bulk.setMarginTop(template.getMarginTop());
bulk.setPageHeight(template.getPageHeight());
bulk.setPageWidth(template.getPageWidth());
// Header & footer
if(!bulk.getHeader()) {
var header = bulk.addHeader();
} else {
var header = bulk.getHeader();
}
for (var i = 0; i < template.getHeader().getNumChildren(); i++) {
appendElementToDoc(header, template.getHeader().getChild(i).copy());
}
if(!bulk.getFooter()) {
var footer = bulk.addFooter();
} else {
var footer = bulk.getFooter();
}
for (var i = 0; i < template.getFooter().getNumChildren(); i++) {
appendElementToDoc(footer, template.getFooter().getChild(i).copy());
}
// Editors & viewer
var editors = template.getEditors();
for (var i = 0; i < editors.length; i++) {
bulk.addEditor(editors[i])
}
var viewers = template.getViewers();
for (var i = 0; i < editors.length; i++) {
bulk.addViewer(viewers[i])
}
//move to folder
DocsList.getFileById(bulk.getId()).removeFromFolder(DocsList.getRootFolder());
DocsList.getFileById(bulk.getId()).addToFolder(DocsList.getFolderById(that.targetId));
return bulk;
}
}
5. Create template document
Just write a letter containing the string ###LABEL### for the address block and ###SALUTATION### for personalized beginning.
6. Create address spreadsheet
The spreadsheet's first line is ignores (headlines are assumed). The columns have to be in the correct order:
- Salutation
- Surname
- Name
- Street
- Zip / City
7. Run bulk letter generation
Open the script again and set a version (File -> Manage Versions) and start the version as webapp. After setting some permissions, an URL is provided to call the script directly... happy generating...
No comments:
Post a Comment