Created
September 13, 2018 07:31
-
-
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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