Google Form, Google Script, Google Sheet, Google slide, Programming
กรอกGoogle Formแล้วสรุปเป็นPDF
เป็นอีกฟีเจอร์ที่คนเรียกร้องเข้ามาเยอะมาก เพราะต้องการสรุปข้อมูลผู้กรอกFormมาทำเป็นPDF ซึ่งถ้าใช้Pluginจะติดข้อจำกัดหลายอย่าง ทั้งการจัดรูปแบบหรือแม้แต่การส่งข้อมูลผ่าน Line Notify วันนี้เราจะมาทำโปรเจกต์นี้โดยการเขียนโค้ดเองทั้งหมด ทำให้ไม่ติดข้อจำกัดใดๆทั้งสิ้นและที่สำคัญคือใช้ฟรีกันแบบ 100% กันไปเล้ยยย
Form >> Copy
Slide >> Copy
// Presented by BrilliantPy ✓ v.1.4.2
// add column >> create_pdf_status
// run function >> generateTitle() -> update variable >> index_col
/*######################### Editable1 Start #########################*/
let templateSlideId = '1s-CnbC13tEk3o4grrpEXWSlit0Rku6GmKNKjwBLP';
let folderResponsePdfId = '1OzsVcKP23BswohNZ7EB0lDSiOYBLP';
let folderResponseSlideId = '1Xf4m38R1GsU_wIxWs9Y51MoIDZBLP';
let sheetName = 'การตอบแบบฟอร์ม 1';
let pdf_file_name = "register_";
let isSendEmail = false;
let isSendLine = false;
let email_send_default = [];
// let email_send_default = ['brilliantpy.live@gmail.com'];
// let email_send_default = ['brilliantpy1.live@gmail.com','brilliantpy2.live@gmail.com'];
var email_subject = 'ขอบคุณสำหรับการสมัครเข้าร่วมโครงการ';
var email_message = 'แบบฟอร์มการสมัครได้จัดส่งให้ท่านแล้ว';
let index_col = {"ประทับเวลา":0,"อีเมล":1,"ชื่อ-สกุล":2,"เพศ":3,"ทักษะ [การพูด]":4,"ทักษะ [การฟัง]":5,"รูปโปรไฟล์":6,"เพศ[ชาย]":7,"เพศ[หญิง]":8,"ทักษะ [การพูด][ไทย]":9,"ทักษะ [การพูด][อังกฤษ]":10,"ทักษะ [การฟัง][ไทย]":11,"ทักษะ [การฟัง][อังกฤษ]":12,"create_pdf_status":13};
let colEmail = index_col["อีเมล"] || "";
let colEmailStatus = index_col["send_email_status"] || "";
let colLineStatus = index_col["send_line_status"] || "";
let colEmailStatusName = "N";
let colLineStatusName = "O";
let colName = index_col["อีเมล"] || ""; // คอลัมน์ที่จะนำมาตั้งเป็นชื่อไฟล์ pdf
let colPdfStatus = index_col["create_pdf_status"] || "";
let colPdfStatusName = "D";
// let colAllImage = [];
let colAllImage = [
{[index_col['รูปโปรไฟล์']] : '{{รูปโปรไฟล์}}'},
];
// let colAllImage = [
// {[index_col['รูปโปรไฟล์1']] : '{{รูปโปรไฟล์1}}'},
// {[index_col['รูปโปรไฟล์2']] : '{{รูปโปรไฟล์2}}'},
// ];
// let index_col_checkbox = [];
let index_col_checkbox = [
{ [index_col['เพศ']] : [{'ชาย':'H'},{'หญิง':'I'}] },
];
// let index_col_multi_checkbox = [];
let index_col_multi_checkbox = [
{ [index_col['ทักษะ [การพูด]']] : [{'ไทย':'J'},{'อังกฤษ':'K'}] },
{ [index_col['ทักษะ [การฟัง]']] : [{'ไทย':'L'},{'อังกฤษ':'M'}] },
];
let tokensV2 = ['c25q5qYbjPeFATLa1LK8vZNBJ61HlqRaseUufBLP']; // BrilliantPy line group(test only)
/*######################### Editable1 End #########################*/
// Init
let newSlideName = 'New_FormToSlidePDF_';
let sent_status = 'SENT';
let ss,sheet,lastRow,lastCol,range,values;
let data_name;
let newSlide,newSlideId,presentation,all_shape;
let titleName;
let exportPdf,pdf_name_full;
let email_send = [];
let filePath;
let isPdfOnly = false;
function formToSlidePdfLine() {
/*######################### Editable2 Start #########################*/
function formatMsgToLine() {
return `ฟอร์มการสมัครงานใหม่
${filePath}`;
}
/*######################### Editable2 End #########################*/
initSpreadSheet().then(async function() {
formatTitle();
if (isSendEmail == false && isSendLine == false) {
isPdfOnly = true;
}
for (let i = 1; i < lastRow; i++) {
let numRow = i+1;
clearVal();
let cur_data = values[i];
data_name = cur_data[colName];
try {
data_name = data_name.replace(/\s/g, '');
} catch(e) {}
if (isPdfOnly) {
let status = cur_data[colPdfStatus];
if (status == sent_status) {
continue;
}
} else {
let emailStatus = cur_data[colEmailStatus];
let lineStatus = cur_data[colLineStatus];
if ((!isSendEmail || (isSendEmail && emailStatus == sent_status)) && (!isSendLine || (isSendLine && lineStatus == sent_status))) {
continue;
}
}
await duplicateSlide().then(async function() {
await updateCheckboxCol(cur_data,numRow).then(async function() {
values = range.getValues();
cur_data = values[i];
await updateSlideData(cur_data).then(async function() {
presentation.saveAndClose();
await createPdf().then(async function() {
removeTempSlide();
if (!isPdfOnly) {
let cur_email = cur_data[colEmail];
let emailStatus = cur_data[colEmailStatus];
let lineStatus = cur_data[colLineStatus];
if (validateEmail(cur_email)) {
email_send.push(cur_email);
}
console.log(email_send);
if (isSendEmail && emailStatus != sent_status) {
for (let j = 0; j < email_send.length; j++) {
if (validateEmail(email_send[j])) {
await sendEmailWithAttachment(email_send[j]).then(function() {
if (j == email_send.length-1) {
updateStatusSent(numRow,'email');
}
});
}
}
}
if (isSendLine && lineStatus != sent_status) {
sendLineNotify(formatMsgToLine());
updateStatusSent(numRow,'line');
}
} else {
updateStatusSent(numRow,'pdf');
}
});
});
});
});
}
console.log('Program completed');
});
}
async function sendLineNotify(all_message_send) {
return new Promise(function(resolve) {
for (let k = 0; k < tokensV2.length; k++) {
let formData = {
'message' : all_message_send,
}
let options = {
"method" : "post",
"payload" : formData,
"headers" : {"Authorization" : "Bearer " + tokensV2[k]}
};
UrlFetchApp.fetch("https://notify-api.line.me/api/notify", options);
}
resolve();
console.log('sendLineNotify completed');
});
}
function clearVal() {
data_name = '';
newSlide = newSlideId = presentation = '';
exportPdf = pdf_name_full = '';
email_send = isSendEmail ? [...email_send_default] : [];
all_shape = '';
console.log('clearVal completed');
}
async function initSpreadSheet() {
return new Promise(function(resolve) {
ss = SpreadsheetApp.getActive();
sheet = ss.getSheetByName(sheetName);
lastRow = sheet.getLastRow();
lastCol = sheet.getLastColumn();
range = sheet.getDataRange();
values = range.getValues();
resolve();
console.log('initSpreadSheet completed');
});
}
function formatTitle() {
titleName = values[0];
titleName.forEach(function (item, index) {
titleName[index] = '{{'+item+'}}';
});
console.log('formatTitle completed');
}
async function duplicateSlide() {
return new Promise(function(resolve) {
let templateSlide = DriveApp.getFileById(templateSlideId);
let templateResponseFolder = DriveApp.getFolderById(folderResponseSlideId);
newSlide = templateSlide.makeCopy(newSlideName.concat(data_name) , templateResponseFolder);
resolve();
console.log('duplicateSlide completed');
});
}
async function updateCheckboxCol(cur_data,numRow) {
return new Promise(function(resolve) {
index_col_checkbox.forEach(function (item) {
Object.keys(item).forEach(function(key) {
var cur_checkbox_val = cur_data[key];
item[key].forEach(function (item_ele) {
Object.keys(item_ele).forEach(function(key_item_ele) {
if (key_item_ele === cur_checkbox_val) {
sheet.getRange(item_ele[key_item_ele].concat(numRow)).setValue('✓');
}
})
})
});
});
index_col_multi_checkbox.forEach(function (item) {
Object.keys(item).forEach(function(key) {
let cur_multi_checkbox_val = cur_data[key];
let cur_multi_checkbox_val_arr = cur_multi_checkbox_val.split(", ");
for(let i = 0; i < cur_multi_checkbox_val_arr.length; i++) {
let cur_val = cur_multi_checkbox_val_arr[i];
item[key].forEach(function (item_ele) {
Object.keys(item_ele).forEach(function(key_item_ele) {
if (key_item_ele === cur_val) {
sheet.getRange(item_ele[key_item_ele].concat(numRow)).setValue('✓');
}
})
})
}
});
});
resolve();
console.log('updateCheckboxCol completed');
});
}
async function updateSlideData(cur_data) {
return new Promise(function(resolve) {
// Init
newSlideId = newSlide.getId();
presentation = SlidesApp.openById(newSlideId);
let slide = presentation.getSlides()[0];
all_shape = slide.getShapes();
titleName.forEach(async function (item, index) {
let isColImg = false;
colAllImage.forEach(async function (img_item) {
Object.keys(img_item).forEach(async function(key) {
if (item === img_item[key]) {
all_shape.forEach(async function(s) {
if (s.getText().asString().includes(img_item[key])) {
let cur_img_url = cur_data[key];
let imageFileId = getIdFromUrl(cur_img_url)
if (imageFileId) {
isColImg = true;
let image = DriveApp.getFileById(imageFileId).getBlob();
await replaceImage(s,image).then(async function() {
console.log('replaceImage completed')
});
}
}
});
}
});
});
if (!isColImg) {
let templateVariable = item;
let replaceValue = cur_data[index];
presentation.replaceAllText(templateVariable, replaceValue);
}
})
resolve();
console.log('updateSlideData completed');
});
}
async function replaceImage(s,image) {
let res;
return new Promise(function(resolve) {
try {
res = s.replaceWithImage(image);
} catch(e) {
console.log("error:",e)
}
if (res) {
console.log('resolve');
resolve();
}
})
}
async function createPdf() {
return new Promise(function(resolve,reject) {
let pdf = DriveApp.getFileById(newSlideId).getBlob().getAs("application/pdf");
pdf_name_full = pdf_file_name+data_name+'.pdf';
pdf.setName(pdf_name_full);
exportPdf = DriveApp.getFolderById(folderResponsePdfId).createFile(pdf);
filePath = exportPdf.getUrl();
if (exportPdf) {
resolve();
console.log('Create PDF completed');
} else {
reject();
console.log('Create PDF error');
}
});
}
async function sendEmailWithAttachment(email) {
return new Promise(function(resolve,reject) {
let file = DriveApp.getFolderById(folderResponsePdfId).getFilesByName(pdf_name_full);
if (!file.hasNext()) {
console.error("Could not open file "+pdf_name_full);
return;
}
try {
MailApp.sendEmail({
to: email,
subject: email_subject,
htmlBody: email_message,
attachments: [file.next().getAs(MimeType.PDF)]
});
resolve();
console.log('sendEmailWithAttachment completed')
} catch(e) {
reject();
console.log("sendEmailWithAttachment error with email (" + email + "). " + e);
}
});
}
function removeTempSlide() {
try {
DriveApp.getFileById(newSlideId).setTrashed(true);
console.log('removeTempSlide completed');
} catch(e) {
console.log('removeTempSlide error')
}
}
function updateStatusSent(numRow,mode) {
if (mode == 'email') {
sheet.getRange(colEmailStatusName.concat(numRow)).setValue(sent_status);
} else if (mode == 'line') {
sheet.getRange(colLineStatusName.concat(numRow)).setValue(sent_status);
} else if (mode == 'both') {
sheet.getRange(colEmailStatusName.concat(numRow)).setValue(sent_status);
sheet.getRange(colLineStatusName.concat(numRow)).setValue(sent_status);
} else if (mode == 'pdf') {
sheet.getRange(colPdfStatusName.concat(numRow)).setValue(sent_status);
}
console.log('updateStatusSent completed');
}
function formatUrlImg(url) {
let new_url = '';
let start_url = 'https://drive.google.com/uc?id=';
new_url = start_url + getIdFromUrl(url);
return new_url;
}
function getIdFromUrl(url) {
return url.match(/[-\w]{25,}/);
}
function validateEmail(email) {
var re = /\S+@\S+\.\S+/;
if (!re.test(email)) {
return false;
} else {
return true;
}
}
function generateTitle() {
let result = "";
initSpreadSheet();
let title = values[0];
console.log("title:",title);
for (let i=0;i<title.length;i++) {
result += `"${title[i]}":${i}`;
if (i != title.length-1) {
result += ','
} else {
result = `let index_col = {${result}};`;
}
}
console.log(result);
}
function customFormatDate(date,mode,format) {
let _timezone = "";
if (mode == "date") {
_timezone = "GMT+7";
}else if (mode == "time") {
_timezone = "GMT+6:43";
}else {
_timezone = timezone;
}
return Utilities.formatDate(date, _timezone, format);
}
