구글 설문지로 '견적서 자동 발송' 시스템 구축하기 | 완성 템플릿 포함

/* ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ */
/* ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ 이 영역을 수정하세요. ↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓ */
 
const sourceSpreadsheetId = '구글시트ID';
const destinationFolderId = '폴더ID';
const sh_name_inv = '견적서'; // 업데이트 할 서식의 시트명
const sh_name_form = '설문지'; // 설문지와 연동된 시트명
const printArea = 'A1:I34'; // PDF로 출력할 범위
 
const logo_url = '로고URL' // 빙 이미지 생성기에서 만든 이미지 URL을 작성합니다.;
 
const mailtitle = '[오빠두엑셀] {client_name} 님의 강의요청서입니다.';
const htmlbody = `{client_name}님 감사합니다.`;
 
const fileNameFormat = "OPD{rowID}-{client_name}-{datetime}";
const sendAsPDF = 1;
 
// 견적서 서식과 동일하게 {필드}로 메일 본문 내용을 수정할 수 있습니다.
// {rowID}는 설문지 시트에서 고유 ID로 사용할 수 있는 행 번호를 5자리 숫자로 반환합니다.
// {datetime}은 현재 시간을 yymmddhhmmss 형식으로 반환합니다.
// PDF를 첨부하지 않고, 구글 시트 링크를 보낼 경우 sendAsPDF를 0으로 사용합니다.
 
/* ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ 이 영역을 수정하세요. ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ */
/* ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑ */
 
// 설문지 데이터를 기반으로 문서를 생성하는 함수입니다.
function sendEmailfromForm() {
 
// 소스 스프레드시트를 엽니다.
var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
 
// '설문지' 시트에서 새롭게 추가된 항목을 배열로 가져옵니다.
var sh = sourceSpreadsheet.getSheetByName(sh_name_form);
var last_row = sh.getLastRow();
var rng = sh.getDataRange();
var values = rng.getValues()[last_row - 1];
var header = rng.getValues()[0];
 
// 파일이름에 사용할 현재 시간을 파싱합니다.
var formattedTime = Utilities.formatDate(new Date(), 'Asia/Seoul', 'yyMMddHHmmss');
 
// 가져온 설문지 데이터를 파싱합니다.
var formdata = parseFormData(values, header);
 
// 파싱된 데이터를 콘솔에 출력합니다.
console.log(formdata)
 
// '견적서' 템플릿을 복사합니다.
var sh_inv = sourceSpreadsheet.getSheetByName(sh_name_inv);
var sh_copy = sh_inv.copyTo(sourceSpreadsheet);
 
// 복사된 시트의 데이터 범위를 참조합니다.
var new_rng = sh_copy.getDataRange();
var new_values = new_rng.getValues();
 
// 복사된 시트의 모든 셀을 돌아가벼 반복문을 실행합니다.
for (var i = 0; i < new_values.length; i++) {
for (var j = 0; j < new_values[i].length; j++) {
var cell = new_values[i][j];
// 셀이 문자열이고 '{'와 '}'로 시작하고 끝나는지 확인합니다.
if (typeof cell === 'string' && cell.startsWith('{') && cell.endsWith('}')) {
var key = cell.substring(1, cell.length - 1);
// 셀에 'rowID'라는 키가 있으면 마지막 행 번호로 값을 설정합니다.
if (key == 'rowID') {
sh_copy.getRange(i + 1,j + 1).setValue(last_row);
} else {
// 그렇지 않으면 해당 키에 해당하는 데이터로 값을 설정합니다.
sh_copy.getRange(i + 1,j + 1).setValue(formdata[key]);
}
}
}
}
 
// 스프레드시트에 대기 중인 변경사항을 적용합니다.
SpreadsheetApp.flush();
 
// 완성된 견적서 시트의 함수를 값으로 변경합니다.
rng = sh_copy.getDataRange();
values =rng.getValues();
rng.setValues(values);
 
// 새로운 스프레드시트를 생성합니다.
var newSpreadsheet = SpreadsheetApp.create('Sheet');
var newSpreadsheetId = newSpreadsheet.getId();
var sh_copied = sh_copy.copyTo(newSpreadsheet);
 
// 완성된 견적서를 새 스프레드시트로 옮깁니다.
sh_copied.setName(sh_name_inv);
sourceSpreadsheet.deleteSheet(sh_copy);
 
// 새 스프레드시트의 기본시트를 제거하고, 견적서 시트만 남깁니다.
var defaultSheet = newSpreadsheet.getSheets()[0];
newSpreadsheet.deleteSheet(defaultSheet);
 
var emailbody = htmlbody;
var emailtitle = mailtitle;
var fileName = fileNameFormat;
// 이메일 본문 중 "{}"로 작성된 값을 고객이 제출한 설문지 데이터로 변경합니다.
for (var key in formdata) {
if (formdata.hasOwnProperty(key)) {
 
// 정규표현식을 작성합니다.
var regex = new RegExp('{' + key + '}', 'g');
 
// 필드명에 date, time이 있을 경우 표시형식을 날짜, 시간 형식으로 변경합니다.
if (key.toLowerCase().includes("date")) {
var replacevalue = Utilities.formatDate(formdata[key], 'Asia/Seoul', 'yyyy-MM-dd');
} else if (key.toLowerCase().includes("time")) {
var replacevalue = Utilities.formatDate(formdata[key], 'Asia/Seoul', 'a hh:mm:ss');
} else {
var replacevalue = formdata[key];
}
 
// "{키}" 를 설문지 값으로 변경합니다.
emailbody = emailbody.replace(regex, replacevalue);
emailtitle = emailtitle.replace(regex, replacevalue);
fileName = fileName.replace(regex,replacevalue);
}
}
// {logo_url}이 있을 경우, 실제 로고 이미지 주소로 변경합니다.
emailbody = emailbody.replace("{logo_url}",logo_url);
fileName = fileName.replace("{datetime}",formattedTime);
fileName = fileName.replace("{rowID}",last_row.toString().padStart(5,'0'));
 
// 새 스프레드시트를 지정한 폴더에 저장합니다.
var file = DriveApp.getFileById(newSpreadsheetId);
file.setName(fileName);
var folder = DriveApp.getFolderById(destinationFolderId);
file.moveTo(folder);
 
if (sendAsPDF == 1) {
// 고객에게 PDF 첨부파일과 함께 이메일을 발송합니다.
var fileId = SaveAsPDF(newSpreadsheetId, sh_name_inv, printArea, destinationFolderId, fileName+".pdf");
sendEmailWithAttachment(formdata['client_email'], emailtitle, emailbody, fileId);
} else {
var driveShFile = DriveApp.getFileById(newSpreadsheetId);
driveShFile.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
emailbody = '<div style="text-align: center; background: #3f37bb; color: #fff; max-width: 600px; border-radius: 5px; margin: auto; margin-top: 10px; margin-bottom: 10px; line-height: 1.5rem; padding: 10px;">발행된 문서는 아래 링크를 클릭해서 확인하세요.<div style="color: #fff34e; font-weight: bold; font-size: 0.8rem;">'+newSpreadsheet.getUrl()+'</div></div>' + emailbody;
sendEmailWithAttachment(formdata['client_email'], emailtitle, emailbody);
}
}
 
// 헤더와 값을 딕셔너리로 변환하는 함수입니다.
function parseFormData(values, header) {
 
// 비어있는 배열을 생성합니다.
var result = {};
 
// 항목을 하나씩 돌아가며 헤더와 값을 하나의 배열로 합칩니다.
for (var i = 0; i < values.length; i++) {
var key = header[i];
var value = values[i];
 
// 키에 count 라는 단어가 포함된 경우, 값에서 숫자만 남기고 '명'을 지웁니다.
if (key.toLowerCase().includes("count")) {
value = value.toString().replace("명", "");
}
result[key] = value;
}
 
return result;
}
 
// 시트를 PDF로 저장하는 함수입니다.
function SaveAsPDF(sheetId, sheetName, printRange, folderId, pdfFileName) {
// PDF로 저장할 시트를 불러옵니다.
var folder = DriveApp.getFolderById(folderId);
var spreadsheet = SpreadsheetApp.openById(sheetId);
var sheet = spreadsheet.getSheetByName(sheetName);
 
// 시트를 PDF로 저장하기 위한 URL을 생성합니다.
var url = spreadsheet.getUrl().replace(/edit$/, '')
+ 'export?exportFormat=pdf&format=pdf'
// Specify the range and sheet name
+ '&gid=' + sheet.getSheetId()
+ '&range=' + printRange
// Add other parameters for PDF formatting as needed
+ '&size=A4'
+ '&portrait=true'
+ '&scale=4'
+ '&sheetnames=false&printtitle=false&pagenumbers=false'
+ '&gridlines=false'
+ '&fzr=false';
 
// 보안 토큰을 헤더에 추가합니다.
var token = ScriptApp.getOAuthToken();
var response = UrlFetchApp.fetch(url, {
headers: {
'Authorization': 'Bearer ' + token
}
});
 
// 완성된 PDF를 구글 드라이브 폴더에 저장합니다.
var pdfBlob = response.getBlob().setName(pdfFileName);
var pdfFile = folder.createFile(pdfBlob);
 
return pdfFile.getId();
}
 
// PDF 첨부파일과 함께 이메일을 발송하는 함수입니다.
function sendEmailWithAttachment(emailaddress, title, body, fileId) {
var recipient = emailaddress;
var subject = title;
var htmlBody = body;
 
if (fileId == null) {
GmailApp.sendEmail(recipient, subject, '', {
htmlBody: htmlBody
});
} else {
var file = DriveApp.getFileById(fileId);
GmailApp.sendEmail(recipient, subject, '', {
htmlBody: htmlBody,
attachments: [file.getAs(MimeType.PDF)]
});
}
 
}