목적
구글 시트의 인보이스 템플릿을 활용해 대량의 인보이스 PDF를 만들고, 구글 드라이브에 저장하고, 이메일로 보내는 앱스 스크립트를 이해합니다.
샘플 준비 하기
구글 앱스 스크립트 샘플 사이트에서는 훌륭한 샘플들을 제공하고 있습니다.
Automations 카테고리 하위에 File Management >Generate and send PDFs from Google Sheets 에서 PDF 생성 및 발송 샘플을 자신의 계정으로 복사할 수 있습니다.
샘플 분석
샘플은 구글 시트와 시트에 연결된 앱스 스크립트로 구성됩니다.
구글 시트는 5개의 탭으로 구성되어 있습니다.
1.
인보이스 템플릿을 작성한 Invoice Template탭
2.
생성한 인보이스 정보가 담기는 Invoices탭
3.
거래 정보가 담긴 Transactions 탭
4.
고객 정보가 담긴 Customers 탭
5.
제품 정보가 담긴 Products 탭
앱스 스크립트는 3개의 파일로 구성되어 있습니다.
1.
메인 코드가 작성된 Code.gs
2.
메뉴 코드가 작성된 Menu.gs
3.
구글 드라이브와 관련된 코드가 작성된 Utilities.gs
그럼 이제 앱스 스크립트를 중심으로 샘플을 분석하겠습니다.
많은 경우 코드의 주석을 참고해주세요.
샘플을 복사한 뒤에 스프레드시트 설정에서 시간대를 바꿔주는 것을 잊지마세요. 바꿔주지 않으면 원하는 시간대로 기록되지 않습니다.
메뉴
다음 코드는 메뉴를 실행을 구성하는 코드입니다.
onOpen(e)는 심플 트리거 중 하나로 구글 시트가 열릴 때 자동으로 실행되는 예약 함수입니다.
상수 APP_TITLE 로 정의된 대로 메뉴명을 지정해서 메뉴를 만들고 하위에 메뉴 아이템들을 추가합니다.
// 구글 시트가 열릴 때 메뉴를 생성합니다.
function onOpen(e) {
const menu = SpreadsheetApp.getUi().createMenu(APP_TITLE)
menu
.addItem('Process invoices', 'processDocuments')
.addItem('Send emails', 'sendEmails')
.addSeparator()
.addItem('Reset template', 'clearTemplateSheet')
.addToUi();
}
JavaScript
복사
기본 변수 정의
// 테스트하기 위해서는 EMAIL_OVERRIDE 를 TRUE 로 바꾸고, EMAIL_ADDRESS_OVERRIDE 에 자신의 이메일을 넣을 수 있습니다.
const EMAIL_OVERRIDE = false;
const EMAIL_ADDRESS_OVERRIDE = 'test@example.com';
// 메뉴명, 폴더명, 인보이스 유효일을 지정합니다.
const APP_TITLE = 'Generate and send PDFs';
const OUTPUT_FOLDER_NAME = "Customer PDFs";
const DUE_DATE_NUM_DAYS = 15
// 시트명을 정의합니다. 시트명을 바꾸게 되면 그에 맞춰 변경합니다.
const CUSTOMERS_SHEET_NAME = 'Customers';
const PRODUCTS_SHEET_NAME = 'Products';
const TRANSACTIONS_SHEET_NAME = 'Transactions';
const INVOICES_SHEET_NAME = 'Invoices';
const INVOICE_TEMPLATE_SHEET_NAME = 'Invoice Template';
// 이메일 제목, 내용을 정의합니다.
const EMAIL_SUBJECT = 'Invoice Notification';
const EMAIL_BODY = 'Hello!\rPlease see the attached PDF document.';
JavaScript
복사
인보이스 만들기 (Process invoices)
processDocuments 함수가 실행되면 다음의 과정이 반복적으로 실행됩니다.
•
Customers, Products, Transactions 탭의 데이터를 객체로 만들어 준비합니다.
•
고객별로 인보이스를 만드는 createInvoiceForCustomer() 함수를 반복해서 실행하여 인보이스를 만듭니다.
◦
clearTemplateSheet() 함수를 실행해서 Invoice Template 탭을 리셋합니다.
◦
Invoice Template 에 넣을 데이터를 가공합니다.
◦
Invoice Template 에 데이터를 넣습니다.
◦
createPDF() 함수를 실행합니다.
▪
PDF 파일을 만들기 위한 blob 을 준비합니다.
▪
blob을 사용해 지정된 폴더에 PDF 파일을 만듭니다.
반복된 과정이 끝나면, 인보이스 생성결과를 Invoices 탭에 기록합니다.
이제 단계별로 코드를 분석하겠습니다.
processDocuments
function processDocuments() {
// const 로 상수를 선언합니다.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const customersSheet = ss.getSheetByName(CUSTOMERS_SHEET_NAME);
const productsSheet = ss.getSheetByName(PRODUCTS_SHEET_NAME);
const transactionsSheet = ss.getSheetByName(TRANSACTIONS_SHEET_NAME);
const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
const invoiceTemplateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);
// Customers, Products, Transactions 탭에서 데이터를 가져옵니다.
// dataRangeToObject 라는 헬퍼 함수로 탭에 들어있는 데이터를 객체로 만듭니다.
// 다음 코드 블럭에서 설명합니다.
const customers = dataRangeToObject(customersSheet);
const products = dataRangeToObject(productsSheet);
const transactions = dataRangeToObject(transactionsSheet);
//1초간 토스트 메시지를 표시합니다.
ss.toast('Creating Invoices', APP_TITLE, 1);
//invoices 를 빈 배열로 생성합니다.
const invoices = [];
// customers 배열의 각 요소별로 반복적으로 함수를 실행합니다.
customers.forEach(function (customer) {
// 1초간 토스트 메시지를 표시합니다.
ss.toast(`Creating Invoice for ${customer.customer_name}`, APP_TITLE, 1);
// invoice 는 createInvoiceForCustomer 함수의 결과물로 정의합니다.
let invoice = createInvoiceForCustomer(
customer, products, transactions, invoiceTemplateSheet, ss.getId());
// invoices 배열에 invoice 를 푸시합니다.
invoices.push(invoice);
});
// Invoices 탭에 invoices 배열을 기록합니다.
invoicesSheet.getRange(2, 1, invoices.length, invoices[0].length).setValues(invoices);
}
JavaScript
복사
헬퍼 함수 (dataRangeToObject(sheet))
processDocuments() 가 실행되면서 탭의 데이터를 객체로 변환시켜주는 헬퍼 함수입니다.
헬퍼함수가 실행되고나면, key와 value 가 페어링된 배열을 리턴합니다.
function dataRangeToObject(sheet) {
// 탭을 파라미터로 받아, dataRange를 정의합니다.
// getLastRow(), getLastColumn()을 사용해 마지막 행과 열의 범위를 계산하여 전체 범위를 지정하고 값을 가져옵니다.
const dataRange = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();
// 열의 제목을 keys 로 저장합니다.
// dataRange에 splice(0,1)을 적용하면 dataRange의 가장 처음 값만 가져오는데, 배열로 가져옵니다.
// 배열의 가장 첫번째 인자를 가져오기 위해 [0] 을 붙여줍니다.
const keys = dataRange.splice(0, 1)[0];
//getObjects 함수와 createObjectKeys 함수를 사용해서 결과값 객체를 얻어 리턴합니다.
return getObjects(dataRange, createObjectKeys(keys));
}
// 탭에서 추출한 열이름들로 keys를 재정의 합니다.
function createObjectKeys(keys) {
return keys.map(function (key) {
// 열 하나하나를 map 으로 다시 할당하는데,
// 정규식에서 \W 는 영문자, 숫자, 밑줄을 제외한 문자를 의미합니다.
// [^A-Za-z0-9_]와 동일합니다.
// 열이름 전체에서 \W 인 것은 찾아서 밑줄로 치환하고, 전체를 소문자로 맞추어 key로 정의합니다.
return key.replace(/\W+/g, '_').toLowerCase();
});
}
// data와 keys 를 파라미터로 받아 객체를 만듭니다.
function getObjects(data, keys) {
// objects 를 빈 배열로 생성합니다.
let objects = [];
// data의 길이만큼, 즉 지정한 탭의 데이터 행만큼 반복문을 실행합니다.
for (let i = 0; i < data.length; ++i) {
// 반복문 안에서 object 를 빈 객체로 생성합니다.
let object = {};
let hasData = false;
// data[i]의 길이만큼, 즉 열의 길이만큼 반복문을 실행합니다.
for (let j = 0; j < data[i].length; ++j) {
let cellData = data[i][j];
// cellData 가 비어있으면, j 반복문의 끝으로 이동해 다음 반복을 실행합니다.
if (isCellEmpty(cellData)) {
continue;
}
//cellData가 비어있지 않으면, object 에 key 을 열이름으로 지정하고, 값을 cellData로 지정합니다.
//hasData 를 true 로 변경합니다.
object[keys[j]] = cellData;
hasData = true;
}
// 1개의 행을 object 객체로 만들고 난뒤에 hasData 가 true이면, object 배열에 푸시합니다.
// 만약 빈 행이었다면 hasData는 초기값 false 가 유지되고 푸시하지 않습니다.
if (hasData) {
objects.push(object);
}
}
// 완성된 배열을 리턴합니다.
return objects;
}
// 파라미터로 받는 데이터가 비어있는지를 리턴하는 함수입니다.
function isCellEmpty(cellData) {
// cellData가 비어있으면서, type이 string 이면 true를 리턴합니다.
return typeof (cellData) == "string" && cellData == "";
}
JavaScript
복사
createInvoiceForCustomer
인보이스를 생성하는 함수입니다.
function createInvoiceForCustomer(customer, products, transactions, templateSheet, ssId) {
// transaction 탭의 데이터 중 고객명이 같은 데이터만 필터링해서 customerTransactions 로 정의합니다.
let customerTransactions = transactions.filter(function (transaction) {
return transaction.customer_name == customer.customer_name;
});
// Invoice Template 탭을 리셋합니다.
clearTemplateSheet();
let lineItems = [];
let totalAmount = 0;
// customerTransactions 의 항목 마다 함수를 실행합니다.
customerTransactions.forEach(function (lineItem) {
// customerTransactions 의 한 아이템(lineItem) 의 sku 와 동일한 이름을 가진 sku_name을 products 데이터에서 필터링해서 지정합니다.
// 필터링 결과는 배열이기 때문에 [0] 으로 첫 번째 요소를 선택합니다.
let lineItemProduct = products.filter(function (product) {
return product.sku_name == lineItem.sku;
})[0];
// lineTime 의 데이터로 변수를 정의합니다.
// toFixed(2)로 소수점 2번째 자리로 표시합니다. (반올림 적용)
const qty = parseInt(lineItem.licenses);
const price = parseFloat(lineItemProduct.price).toFixed(2);
const amount = parseFloat(qty * price).toFixed(2);
// lineItems 배열에 lineItem 의 데이터로 준비된 값을 푸시합니다.
lineItems.push([lineItemProduct.sku_name, lineItemProduct.sku_description, '', qty, price, amount]);
// totalAmount 는 amount 를 누산해줍니다.
totalAmount += parseFloat(amount);
});
// invoiceNumber를 랜덤하게 생성합니다.
const invoiceNumber = Math.floor(100000 + Math.random() * 900000);
// 오늘 날짜와 유효 날짜를 계산합니다.
const todaysDate = new Date().toDateString()
const dueDate = new Date(Date.now() + 1000 * 60 * 60 * 24 * DUE_DATE_NUM_DAYS).toDateString()
// 템플릿에 데이터를 입력합니다.
templateSheet.getRange('B10').setValue(customer.customer_name)
templateSheet.getRange('B11').setValue(customer.address)
templateSheet.getRange('F10').setValue(invoiceNumber)
templateSheet.getRange('F12').setValue(todaysDate)
templateSheet.getRange('F14').setValue(dueDate)
templateSheet.getRange(18, 2, lineItems.length, 6).setValues(lineItems);
// flush()를 통해 현재까지 누적된 변경사항을 모두 적용하고 그 다음 단계를 진행합니다.
SpreadsheetApp.flush();
// 0.5초간 대기하여 딜레이로 인한 오류를 방지합니다.
Utilities.sleep(500);
// createPDF 함수로 pdf를 생성합니다.
const pdf = createPDF(ssId, templateSheet, `Invoice#${invoiceNumber}-${customer.customer_name}`);
// Invoices 탭에 기록할 내용을 리턴합니다.
// pdf 가 저장된 주소를 getUrl 함수로 확인하여 저장합니다.
return [invoiceNumber, todaysDate, customer.customer_name, customer.email, '', totalAmount, dueDate, pdf.getUrl(), 'No'];
}
JavaScript
복사
clearTemplateSheet
인보이스 템플릿을 리셋하는 함수입니다.
function clearTemplateSheet() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const templateSheet = ss.getSheetByName(INVOICE_TEMPLATE_SHEET_NAME);
// 리셋할 범위를 지정합니다.
const rngClear = templateSheet.getRangeList(['B10:B11', 'F10', 'F12', 'F14']).getRanges()
// 각 범위별로 clearContents()로 서식은 그대로 두고 콘텐츠만 삭제합니다.
rngClear.forEach(function (cell) {
cell.clearContent();
});
// 아래의 ITEMS ~ AMOUNT 부분을 삭제합니다. 이 샘플에서는 아이템이 6행으로 제한되어 있습니다.
templateSheet.getRange(18, 2, 7, 6).clearContent();
}
JavaScript
복사
createPDF
PDF 의 생성은 정형화되어 있습니다.
자세한 사항은 Spencer-Easton 의 Github 를 참고할 수 있습니다. 필요한 파라미터만 가져다 사용할 수 있으며, PDF를 생성할 구글 시트의 ssID, 탭의 ID 가 필수적입니다.
function createPDF(ssId, sheet, pdfName) {
// fr은 시작하는 행으로 1행이 0입니다.
// fc는 시작하는 열로 1열이 0입니다.
// lc는 종료하는 열입니다.
// lr은 종료하는 행입니다.
const fr = 0, fc = 0, lc = 9, lr = 27;
const url = "https://docs.google.com/spreadsheets/d/" + ssId + "/export" +
"?format=pdf&" +
"size=7&" +
"fzr=true&" +
"portrait=true&" +
"fitw=true&" +
"gridlines=false&" +
"printtitle=false&" +
"top_margin=0.5&" +
"bottom_margin=0.25&" +
"left_margin=0.5&" +
"right_margin=0.5&" +
"sheetnames=false&" +
"pagenum=UNDEFINED&" +
"attachment=true&" +
"gid=" + sheet.getSheetId() + '&' +
"r1=" + fr + "&c1=" + fc + "&r2=" + lr + "&c2=" + lc;
// params 부분은 정형화되어 있는 부분입니다. 현재 계정의 권한으로 실행한다는 뜻입니다.
const params = { method: "GET", headers: { "authorization": "Bearer " + ScriptApp.getOAuthToken() } };
// blob 은 UrlFetchApp 으로 가져오게됩니다. pdfName 으로 파일명을 정의할 수 있습니다.
const blob = UrlFetchApp.fetch(url, params).getBlob().setName(pdfName + '.pdf');
// PDF 를 저장할 폴더를 지정합니다. 폴더가 생성되어 있지 않으면 새로 생성합니다.
const folder = getFolderByName_(OUTPUT_FOLDER_NAME);
// blob 을 folder 에 PDF 로 생성합니다.
const pdfFile = folder.createFile(blob);
// pdfFile 을 리턴합니다.
return pdfFile;
}
JavaScript
복사
getFolderByName_
폴더를 생성하거나 기존 폴더를 가져오는 함수입니다.
// 폴더명을 파라미터로 받습니다.
function getFolderByName_(folderName) {
// 현재 구글 시트가 저장된 폴더를 parentFolder 로 지정합니다.
const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
const parentFolder = DriveApp.getFileById(ssId).getParents().next();
// parentFolder 안에 파라미터로 받은 폴더명이 존재하는지 체크합니다.
const subFolders = parentFolder.getFolders();
while (subFolders.hasNext()) {
let folder = subFolders.next();
// 같은 폴더명이 존재하면 folder 를 리턴합니다.
if (folder.getName() === folderName) {
return folder;
}
}
// 모든 폴더를 확인했는데, 같은 폴더명이 없다면 파라미터로 받은 폴더명으로 새로운 폴더를 생성합니다.
return parentFolder.createFolder(folderName)
.setDescription(`Created by ${APP_TITLE} application to store PDF output files`);
}
JavaScript
복사
반복문이 완료되고 나면, Invoices 탭에 생성한 Invoice 들의 링크를 포함한 결과가 기록됩니다.
이메일 보내기 (sendEmails)
인보이스가 생성되어 구글 드라이브에 저장되고 난 뒤에 이메일을 발송하는 기능입니다.
function sendEmails() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const invoicesSheet = ss.getSheetByName(INVOICES_SHEET_NAME);
// Invoices 탭의 데이터를 정의합니다. 앞서 Customers 탭의 데이터를 정의한 것과 동일한 방식입니다.
const invoicesData = invoicesSheet.getRange(1, 1, invoicesSheet.getLastRow(), invoicesSheet.getLastColumn()).getValues();
const keysI = invoicesData.splice(0, 1)[0];
const invoices = getObjects(invoicesData, createObjectKeys(keysI));
// 토스트 메시지를 1초간 표시합니다.
ss.toast('Emailing Invoices', APP_TITLE, 1);
// invoices 행 별로 반복문을 실행합니다.
invoices.forEach(function (invoice, index) {
// Email Sent 열이 Yes 가 아니면 진행합니다.
if (invoice.email_sent != 'Yes') {
ss.toast(`Emailing Invoice for ${invoice.customer}`, APP_TITLE, 1);
// 구글 드라이브 링크에서 fileId 를 추출합니다.
// [-\w] 는 영문자, 숫자, 밑줄, 하이픈에 대응합니다.
// [-\w]{25,} 는 최소한 25자를 의미합니다.
// [-\w]{25,}(?!.*[-\w]{25,}) 에서 ?! 는 앞의 결과가 뒤에 뒤의 결과가 따라붙지 않는 경우를 의미합니다. 오류 방지 차원에서 추가된 것으로 보입니다.
const fileId = invoice.invoice_link.match(/[-\w]{25,}(?!.*[-\w]{25,})/)
const attachment = DriveApp.getFileById(fileId);
// 수신자의 이메일을 정의합니다.
let recipient = invoice.email;
// 만약 EMAIL_OVERRIDE 가 TRUE 이면, 이메일을 테스트 이메일로 변경합니다.
if (EMAIL_OVERRIDE) {
recipient = EMAIL_ADDRESS_OVERRIDE
}
// PDF를 첨부파일로해서 GmailApp 으로 메일을 발송합니다.
GmailApp.sendEmail(recipient, EMAIL_SUBJECT, EMAIL_BODY, {
attachments: [attachment.getAs(MimeType.PDF)],
name: APP_TITLE
});
// 발송된 건은 Email Sent 의 값을 Yes 로 변경합니다.
invoicesSheet.getRange(index + 2, 9).setValue('Yes');
}
});
}
JavaScript
복사
구글의 샘플은 샘플이기 때문에 연속성이 고려되어 있지는 않습니다.
인보이스를 만들기를 다시 실행하는 경우 Invoices 탭에는 새로 기록이 됩니다.
이런 부분은 시트의 마지막행을 구하고 그 뒤에 데이터를 입력하는 방법으로 해결할 수 있겠습니다.
