TIPS

Google Apps Script: 급여/거래명세서 구글 문서 템플릿에 맞춰 PDF 파일 만들기

Google 스프레드시트
작성자
진수 이
작성일
2021-06-03 13:07
조회
739

설명


구글시트로 데이터를 관리하다보면, 거래명세서나 급여명세서처럼 입력하는 업체나 이름에 따라 데이터를 가공해서 출력해야할 때가 있습니다.


업체/이름/해당월을 입력하고 참조해서 데이터를 구글 시트에서 보여주는 것은 FILTER, QUERY 등을 사용해서 쉽게 가능하지만, 출력을 하려면 하나하나 입력값을 바꿔주는데 수고가 많이 듭니다.


이번 포스팅에서는 구글 문서에 템플릿을 만들어 놓고, 데이터를 반복적으로 바꾸면서 PDF로 저장하는 방법을 소개합니다.




기본적인 프로세스는 다음과 같습니다.


  1. 구글 문서에 템플릿을 작성하고 데이터를 넣을 곳에 치환문자를 입력 (<<성명>>, {성명} 과 같은 문자)
  2. 구글 시트에 데이터베이스의 형태로 자료를 준비 (첫 행은 라벨이고 열 단위로 동일한 데이터를 준비)
  3. 구글 드라이브에 작업 폴더를 생성
  4. 구글 시트에서 스크립트 실행
    1. 템플릿에서 치환문자가 입력된 부분을 실제 데이터로 바꿔주고, PDF로 저장하는 작업을 반복
    2. 구글 드라이브에 저장된 PDF를 ZIP으로 압축해서 다운로드 링크를 표시



이번 포스팅에서는 1,2, 3-1 프로세스를 다룹니다.


1. 구글 문서 템플릿 준비

샘플과 같이 템플릿을 준비합니다.



급여명세서 샘플




급여명세서


데이터가 들어가야 하는 부분에는 치환문자를 넣습니다.


어떤 형태이든 괜찮지만, { }와 같은 기호를 사용하여서 찾아 바꾸기를 할 때에 원하지 않는 문자가 바뀌지 않도록 해야 합니다.




2. 구글 시트 데이터베이스 준비


구글시트 샘플




구글시트 데이터


위의 이미지와 유사하게 데이터베이스를 작성합니다. QUERY 를 사용한다면, 선택한 월에 따라서 데이터를 바꿀 수도 있습니다.




3. 구글 드라이브에 작업 폴더 생성

작업을 진행할 임시 폴더와 PDF가 저장될 폴더를 만들어둡니다.


폴더 생성


각 폴더에 들어가서 URL을 보면 폴더의 고유 ID 값을 찾을 수 있습니다. 다음 과정에서 사용할 예정입니다.






4. 구글 시트 스크립트 작성

데이터베이스가 들어있는 구글 시트에서 스크립트 편집기를 실행해서 스크립트를 작성합니다.


스크립트는 총 2개의 함수로 작성합니다.


  1. PDF를 만드는 함수
  2. PDF를 만드는 과정을 반복하는 함수



4-1. PDF를 만드는 함수

function createPDF(date,name,joindate,prime,lunch,car,nightwork,longterm,etc,overtime,weekend,nightpay,incena,incenb,incenc,incend,salarysum,pension,health,work,longhealth,incometax, citytax,deductsum,total, pdfName, docFile, tempFolder, pdfFolder) {
const tempFile = docFile.makeCopy(tempFolder); // 템플릿을 복사
const tempDocFile = DocumentApp.openById(tempFile.getId()); //복사한 파일을 정의
const body = tempDocFile.getBody(); //복사한 파일의 내용을 body로 정의
// 찾아 바꾸기 진행하고 저장
body.replaceText("<<귀속월>>", date); //row[0]
body.replaceText("<<성명>>", name); //row[1]
body.replaceText("<<입사일>>", joindate); //row[2]
body.replaceText("<<기본급>>", prime); //row[3]
body.replaceText("<<식대>>", lunch); //row[4]
body.replaceText("<<차량유지비>>", car); //row[5]
body.replaceText("<<야간근로수당>>", nightwork); //row[6]
body.replaceText("<<장기근속수당>>", longterm); //[row[7]
body.replaceText("<<기타수당>>", etc); //row[8]
body.replaceText("<<연장근로수당>>", overtime); //row[9]
body.replaceText("<<휴일근로수당>>", weekend);//row[10]
body.replaceText("<<야간수당>>", nightpay); //row[11]
body.replaceText("<<A수당>>", incena); //row[12]
body.replaceText("<<B수당>>", incenb); //row[13]
body.replaceText("<<C수당>>", incenc); //row[14]
body.replaceText("<<D수당>>", incend); //row[15]
body.replaceText("<<지급액계>>", salarysum); //row[16]
body.replaceText("<<국민연금>>", pension); //row[17]
body.replaceText("<<건강보험>>", health); //row[18]
body.replaceText("<<고용보험>>", work); // row[19]
body.replaceText("<<장기요양>>", longhealth); // row[20]
body.replaceText("<<소득세>>", incometax); //row[21]
body.replaceText("<<지방소득세>>", citytax); //row[22]
body.replaceText("<<공제액계>>", deductsum); //row[23]
body.replaceText("<<차인지급액>>", total); //row[24]
tempDocFile.saveAndClose();
//PDF 생성 부분
const pdfContentBlob = tempFile.getAs(MimeType.PDF); //tempfile을 PDF로 가져와서 변수로 정의
pdfFolder.createFile(pdfContentBlob).setName(pdfName); //PDF 를 이름 지정해서 pdfFolder에 저장
tempFolder.removeFile(tempFile); // 임시 파일은 삭제
};

최종적으로는 한번에 모든 PDF를 만드는 함수를 실행하기 때문에, 단일 PDF 를 만드는 스크립트에 들어가는 변수는 모든 PDF를 만드는 함수에서 가져오게 정의합니다. 4-2 함수에보면 const 로 변수가 정의되어 있습니다.


function createPDF(date,name,joindate,prime,lunch,car,nightwork,longterm,etc,overtime,weekend,nightpay,incena,incenb,incenc,incend,salarysum,pension,health,work,longhealth,incometax, citytax,deductsum,total, pdfName, docFile, tempFolder, pdfFolder)

템플릿을 복사해서 tempFile을 만들고, 해당 파일에 치환부분으로 정의된 곳을 찾아서 데이터로 변경해줍니다. 그리고 저장하고 문서를 닫습니다.


const tempFile = docFile.makeCopy(tempFolder); // 템플릿을 복사
const tempDocFile = DocumentApp.openById(tempFile.getId()); //복사한 파일을 정의
const body = tempDocFile.getBody(); //복사한 파일의 내용을 body로 정의
// 찾아 바꾸기 진행하고 저장
body.replaceText("<<귀속월>>", date); //row[0]
tempDocFile.saveAndClose();

문서를 PDF로 변환해줍니다. 그리고 임시로 만들었던 tempFile 은 삭제합니다.


 const pdfContentBlob = tempFile.getAs(MimeType.PDF); //tempfile을 PDF로 가져와서 변수로 정의
pdfFolder.createFile(pdfContentBlob).setName(pdfName); //PDF 를 이름 지정해서 pdfFolder에 저장
tempFolder.removeFile(tempFile); // 임시 파일은 삭제

여기까지가 단일 PDF 생성 사이클입니다. 이어지는 벌크 함수에서 이 과정을 데이터에 맞게 반복합니다.




4-2. PDF를 만드는 과정을 반복하는 함수

function createBulkPDFs() {
const docFile = DriveApp.getFileById("1f3DpK4j3hNqZwRSWFisPrQ4FuKvHgUBZjAWFWrYmEfQ"); //구글 문서 템플릿 ID
const tempFolder = DriveApp.getFolderById("1Hy2bDVyX8o4WbuI8gOejf2jhnHDXBEY7"); // 구글 문서에 데이터를 바꿔 넣은 뒤에 PDF로 변환하기 위해 저장하는 폴더
const pdfFolder = DriveApp.getFolderById("1LEoaZiwiBiioJJ3pjd_u2mxFtCwVkmHE"); // PDF 가 저장되는 폴더 주소 ID
const thisSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("급여명세서"); // 현재 파일에서 사용할 데이터가 들어 있는 시트이름

const data = thisSheet.getRange(3, 1, thisSheet.getLastRow() - 2, 25).getDisplayValues(); //시트에서 데이터의 범위. 행은 동적으로 설정함
//행마다 PDF 만드는 함수를 반복 실행
let errors =[];
data.forEach(row => {
try{
createPDF(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],row[20],row[21],row[22],row[23],row[24], row[0]+"_"+row[1], docFile, tempFolder, pdfFolder); //생성되는 pdf 이름은 "귀속월_성명" row[0]+"_"+row[1]
errors.push([""]);
} catch(err){
errors.push(["에러"]);
}
}); //forEach 닫는 중괄호
thisSheet.getRange(3, 26, thisSheet.getLastRow() - 2, 1).setValues(errors); // 에러가 나면 마지막 열에 에러라고 표시함
};

먼저 템플릿 문서의 ID, 임시폴더의 ID, PDF 저장폴더의 ID를 정의해줍니다.


const docFile = DriveApp.getFileById("1f3DpK4j3hNqZwRSWFisPrQ4FuKvHgUBZjAWFWrYmEfQ"); //구글 문서 템플릿 ID
const tempFolder = DriveApp.getFolderById("1Hy2bDVyX8o4WbuI8gOejf2jhnHDXBEY7"); // 구글 문서에 데이터를 바꿔 넣은 뒤에 PDF로 변환하기 위해 저장하는 폴더
const pdfFolder = DriveApp.getFolderById("1LEoaZiwiBiioJJ3pjd_u2mxFtCwVkmHE"); // PDF 가 저장되는 폴더 주소 ID
const thisSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("급여명세서"); // 현재 파일에서 사용할 데이터가 들어 있는 시트이름

가져올 데이터의 범위를 설정합니다. 아래의 코드는 thisSheet의 3행 1열에서 시작해서 데이터가 있는 마지막행, 25열까지의 데이터를 사용하는 것입니다. thisSheet.getLastRow() - 2 는 시트의 맨 위에 2열은 데이터에 포함되지 않기 때문에 보정해주는 함수입니다.


  const data = thisSheet.getRange(3, 1, thisSheet.getLastRow() - 2, 25).getDisplayValues(); //시트에서 데이터의 범위. 행은 동적으로 설정함

이제 한 행씩 PDF를 만드는 작업을 반복합니다. 에러가 나서 생성되지 않은 경우에는 마지막 열에 "에러" 라고 표시하게 해두었습니다. createPDF()에 넘길 변수들을 순서에 맞게 row[] 형식으로 넣어주는데, 0이 첫번째 열을 가리킵니다.


  let errors =[];
data.forEach(row => {
try{
createPDF(row[0],row[1],row[2],row[3],row[4],row[5],row[6],row[7],row[8],row[9],row[10],row[11],row[12],row[13],row[14],row[15],row[16],row[17],row[18],row[19],row[20],row[21],row[22],row[23],row[24], row[0]+"_"+row[1], docFile, tempFolder, pdfFolder); //생성되는 pdf 이름은 "귀속월_성명" row[0]+"_"+row[1]
errors.push([""]);
} catch(err){
errors.push(["에러"]);
}
}); //forEach 닫는 중괄호
thisSheet.getRange(3, 26, thisSheet.getLastRow() - 2, 1).setValues(errors); // 에러가 나면 마지막 열에 에러라고 표시함



이제 createBulkPDFs 함수를 실행하면 다음과 같이 작업결과를 얻게 됩니다.






본 포스팅이 참고한 " target="_blank" rel="noreferrer noopener" class="ek-link">동영상(영문)

전체 32
번호 썸네일 제목 작성자 작성일 추천 조회
32 업무 자동화가 적용된 소규모 ERP
업무 자동화가 적용된 소규모 ERP
업무 자동화가 적용된 소규모 ERP
진수 이 | 2021.06.03 | 추천 0 | 조회 592
진수 이 2021.06.03 0 592
31 회계재무 대시보드
회계재무 대시보드
회계재무 대시보드
진수 이 | 2021.06.03 | 추천 0 | 조회 677
진수 이 2021.06.03 0 677
30 쇼핑몰 자동 주간 보고서
쇼핑몰 자동 주간 보고서
쇼핑몰 자동 주간 보고서
진수 이 | 2021.06.03 | 추천 0 | 조회 497
진수 이 2021.06.03 0 497
29 병원 ERP 기반 경영 대시보드
병원 ERP 기반 경영 대시보드
병원 ERP 기반 경영 대시보드
진수 이 | 2021.06.03 | 추천 0 | 조회 890
진수 이 2021.06.03 0 890
28 쇼핑몰/도소매 판매 대시보드
쇼핑몰/도소매 판매 대시보드
쇼핑몰/도소매 판매 대시보드
진수 이 | 2021.06.03 | 추천 0 | 조회 768
진수 이 2021.06.03 0 768
27 엑셀: 다른 파일 참조해서 VLOOKUP/INDEX 하는 방법
엑셀: 다른 파일 참조해서 VLOOKUP/INDEX 하는 방법
엑셀: 다른 파일 참조해서 VLOOKUP/INDEX 하는 방법
진수 이 | 2021.06.03 | 추천 0 | 조회 590
진수 이 2021.06.03 0 590
26 Google Apps Script: 급여/거래명세서 구글 문서 템플릿에 맞춰 PDF 파일 만들기
Google Apps Script: 급여/거래명세서 구글 문서 템플릿에 맞춰 PDF 파일 만들기
Google Apps Script: 급여/거래명세서 구글 문서 템플릿에 맞춰 PDF 파일 만들기
진수 이 | 2021.06.03 | 추천 0 | 조회 739
진수 이 2021.06.03 0 739
25 Google Apps Script: 전체 시트 PDF로 PC에 다운로드
Google Apps Script: 전체 시트 PDF로 PC에 다운로드
Google Apps Script: 전체 시트 PDF로 PC에 다운로드
진수 이 | 2021.06.03 | 추천 0 | 조회 615
진수 이 2021.06.03 0 615
24 Google Apps Script: 메뉴 생성
Google Apps Script: 메뉴 생성
Google Apps Script: 메뉴 생성
진수 이 | 2021.06.03 | 추천 0 | 조회 521
진수 이 2021.06.03 0 521
23 주식 포트폴리오 구성 및 샤프 비율 (Sharpe Ratio) 계산 방법
주식 포트폴리오 구성 및 샤프 비율 (Sharpe Ratio) 계산 방법
주식 포트폴리오 구성 및 샤프 비율 (Sharpe Ratio) 계산 방법
진수 이 | 2021.06.03 | 추천 0 | 조회 450
진수 이 2021.06.03 0 450

© 2019-2022 autooffice. All rights reserved.