Last active
May 10, 2021 06:40
-
-
Save mhakrook/35f80fdac6336d1b27dcf6fb73edba2c to your computer and use it in GitHub Desktop.
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 onOpen() { | |
| let ui = SpreadsheetApp.getUi() | |
| ui.createMenu("PDF") | |
| .addItem("สร้าง PDF", "pdfForm") | |
| .addItem("สร้าง PDF ทั้งหมด", "pdfFormAll") | |
| .addToUi() | |
| } | |
| function pdfFormAll(){ | |
| let docId = 'ID ของ Google Docs'; | |
| let sheetId = 'ID ของ Google Spreadsheet'; | |
| let sheetName = 'ชื่อ ของ Sheet' | |
| let ss = SpreadsheetApp.openById(sheetId) | |
| let sheet = ss.getSheetByName(sheetName) | |
| let data = sheet.getDataRange().getDisplayValues() | |
| let pdfFolder | |
| let docFolder | |
| let PDFfolder = file.getParents().next().getFoldersByName('PDF') | |
| let DOCfolder = file.getParents().next().getFoldersByName('DOC') | |
| if(PDFfolder.hasNext()){ | |
| pdfFolder = PDFfolder.next() | |
| }else{ | |
| pdfFolder = file.getParents().next().createFolder('PDF') | |
| } | |
| if(DOCfolder.hasNext()){ | |
| docFolder = DOCfolder.next() | |
| }else{ | |
| docFolder = file.getParents().next().createFolder('DOC') | |
| } | |
| data.forEach(row =>{ | |
| let file = DriveApp.getFileById(docId).makeCopy('application' + Math.random()); | |
| let newDoc = DocumentApp.openById(file.getId()); | |
| replace(newDoc, row); | |
| newDoc.saveAndClose(); | |
| let blob = newDoc.getAs('application/pdf') | |
| let pdfFile = pdfFolder.createFile(blob) | |
| file.makeCopy(docFolder) | |
| file.setTrashed(true) | |
| //DriveApp.removeFile(file); | |
| }) | |
| } | |
| function pdfForm() { | |
| let docId = 'ID ของ Google Docs'; | |
| let sheetId = 'ID ของ Google Spreadsheet'; | |
| let sheetName = 'ชื่อ ของ Sheet' | |
| let ss = SpreadsheetApp.openById(sheetId) | |
| let sheet = ss.getSheetByName(sheetName) | |
| let data = sheet.getRange(sheet.getActiveCell().getRow(), 1,1,sheet.getActiveCell().getColumn()).getDisplayValues() | |
| let file = DriveApp.getFileById(docId).makeCopy('application' + Math.random()); | |
| let pdfFolder | |
| let docFolder | |
| let PDFfolder = file.getParents().next().getFoldersByName('PDF') | |
| let DOCfolder = file.getParents().next().getFoldersByName('DOC') | |
| if(PDFfolder.hasNext()){ | |
| pdfFolder = PDFfolder.next() | |
| }else{ | |
| pdfFolder = file.getParents().next().createFolder('PDF') | |
| } | |
| if(DOCfolder.hasNext()){ | |
| docFolder = DOCfolder.next() | |
| }else{ | |
| docFolder = file.getParents().next().createFolder('DOC') | |
| } | |
| let newDoc = DocumentApp.openById(file.getId()); | |
| replace(newDoc, data); | |
| newDoc.saveAndClose(); | |
| let blob = newDoc.getAs('application/pdf') | |
| let pdfFile = pdfFolder.createFile(blob) | |
| file.makeCopy(docFolder) | |
| file.setTrashed(true) | |
| //DriveApp.removeFile(file); | |
| } | |
| function replace(newDoc, data) { | |
| let body = newDoc.getBody(); | |
| body.replaceText('<ชื่อฟิลด์ในไฟล์เอกสาร>', data[เลขอินเด็กซ์จากชีท]); | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment