Skip to content

Instantly share code, notes, and snippets.

@paulgaumer
Created September 13, 2018 07:31
Show Gist options
  • Save paulgaumer/3ea094d731c67b46728d4b6fddbdcb49 to your computer and use it in GitHub Desktop.
Save paulgaumer/3ea094d731c67b46728d4b6fddbdcb49 to your computer and use it in GitHub Desktop.
Automation to generate a Google Doc out of Google Sheet data, then convert it to PDF (apps script)
function createDocument() {
var headers = Sheets.Spreadsheets.Values.get('sheet fileID', 'range for headers');
var tactics = Sheets.Spreadsheets.Values.get('sheet fileID', 'range for data');
var templateId = 'doc fileID';
for(var i = 0; i < tactics.values.length; i++){
var studentName = tactics.values[i][0];
var startDate = tactics.values[i][1];
var endDate = tactics.values[i][2];
var city = tactics.values[i][3];
var creationDate = tactics.values[i][4];
//Make a copy of the template file
var documentId = DriveApp.getFileById(templateId).makeCopy().getId();
//Rename the copied file
DriveApp.getFileById(documentId).setName(studentName + ' - ' + 'Certificate of Completion');
//Get the document body as a variable
var body = DocumentApp.openById(documentId).getBody();
//Insert the supplier name
body.replaceText('{{ STUDENT NAME }}', studentName);
body.replaceText('{{ START DATE }}', startDate);
body.replaceText('{{ END DATE }}', endDate);
body.replaceText('{{ CITY }}', city);
body.replaceText('{{ CREATION DATE }}', creationDate);
DocumentApp.openById(documentId).saveAndClose();
//Make PDF copy at Drive Root
var docFolder = DriveApp.getFileById(documentId).getParents().next().getId();
var docName = DocumentApp.openById(documentId).getName();
docblob = DocumentApp.openById(documentId).getAs('application/pdf');
docblob.setName(docName + ".pdf");
var file = DriveApp.createFile(docblob);
var fileId = file.getId();
moveFileId(fileId, docFolder);
}
}
function moveFileId(fileId, toFolderId) {
var file = DriveApp.getFileById(fileId);
var source_folder = DriveApp.getFileById(fileId).getParents().next();
var folder = DriveApp.getFolderById(toFolderId)
folder.addFile(file);
source_folder.removeFile(file);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment