ต่อเนื่องจากบทความก่อนหน้านี้ >> กรอกGoogle Formแล้วสรุปเป็นPDF(Addรูป+ทำCheckboxได้) No plugin แก้ไขได้100% ซึ่งก็มีหลายท่านขอให้เพิ่มโค้ดให้รองรับในส่วนของ ช่องทำเครื่องหมาย(Checkboxes) และตารางกริดช่องทำเครื่องหมาย (Tick box grid) วันนี้เรามาปรับโค้ดเป็น Version2 เพื่อเพิ่มความสามารถให้โค้ดเรากันครับ 🙂 🙂 🙂
// Presented by BrilliantPy // Editable let templateSlideId = '1cSUpE55DdVVqyb0VBxDnb1cCousw_II8YinqNcBLP'; let folderResponsePdfId = '1im9BLLWOVLFtql-HSBEr7BnC40iBLP'; let folderResponseSlideId = '1TRphA4_mQ0zCMv54yAvJIxbf8fyBLP'; let sheetName = 'การตอบแบบฟอร์ม 1'; let pdf_file_name = "register_"; let status_send_col = 'Q'; let data_begin_row = 2; // 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,"ทักษะ [การพูด][อังกฤษ]":13,"ทักษะ [การฟัง][ไทย]":14,"ทักษะ [การฟัง][อังกฤษ]":15,"Status_Send":16,} let colAllImage = [ {[index_col['รูปโปรไฟล์']] : '{{รูปโปรไฟล์}}'}, ]; // let colAllImage = [ // {[index_col['รูปโปรไฟล์1']] : '{{รูปโปรไฟล์1}}'}, // {[index_col['รูปโปรไฟล์2']] : '{{รูปโปรไฟล์2}}'}, // ]; let index_col_checkbox = [ { [index_col['เพศ']] : [{'ชาย':'I'},{'หญิง':'J'}] }, ]; let index_col_multi_checkbox = [ { [index_col['ภาษา']] : [{'ไทย':'K'},{'อังกฤษ':'L'}] }, { [index_col['ทักษะ [การพูด]']] : [{'ไทย':'M'},{'อังกฤษ':'N'}] }, { [index_col['ทักษะ [การฟัง]']] : [{'ไทย':'O'},{'อังกฤษ':'P'}] }, ]; // ############################################################################################################### // // 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 = []; function formToSlidePdfV2() { initSpreadSheet().then(async function() { formatTitle(); for (let i = data_begin_row; i <= lastRow; i++) { clearVal(); let cur_data = values[i-1]; data_name = cur_data[index_col['ชื่อ-สกุล']]; let _status = cur_data[index_col['Status_Send']]; if (_status == sent_status) { continue; } await duplicateSlide().then(async function() { await updateCheckboxCol(cur_data,i).then(async function() { values = range.getValues(); cur_data = values[i-1]; await updateSlideData(cur_data).then(async function() { presentation.saveAndClose(); await createPdf().then(async function() { let cur_email = cur_data[index_col['อีเมล์']]; if (cur_email !== '') { email_send.push(cur_email); } console.log(email_send); 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) { removeTempSlide(); updateStatusSent(i); } }); } } }); }); }); }); } console.log('Program completed'); }); } function clearVal() { data_name = ''; newSlide = newSlideId = presentation = ''; exportPdf = pdf_name_full = ''; email_send = []; email_send = [...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) { 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 new_url = formatUrlImg(cur_img_url); // s.replaceWithImage(new_url); await replaceImage(s,new_url).then(async function() { await console.log('replace image') }); } }); presentation.replaceWithImage(new_url) } }); }) let templateVariable = item; let replaceValue = cur_data[index]; presentation.replaceAllText(templateVariable, replaceValue); }) resolve(); console.log('updateSlideData completed'); }); } async function replaceImage(s,new_url) { console.log('replaceImage()') let res; return new Promise(function(resolve) { res = s.replaceWithImage(new_url); 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); 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) { sheet.getRange(status_send_col.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; } }
It¦s really a nice and helpful piece of info. I am glad that you shared this useful info with us. Please keep us informed like this. Thank you for sharing.
Thank you.
Can I just say what a comfort to find somebody who truly understands what theyre talking about on the net. You certainly understand how to bring a problem to light and make it important. A lot more people really need to look at this and understand this side of the story. I cant believe youre not more popular because you definitely possess the gift.
Thank you.
พี่ครับ ผมได้ทำตามที่พี่ให้ code มาแต่ไม่ส่งอีเมล์ตอบกลับไปที่ผู้กรอกนะครับ ไม่ทราบผมต้องทำอย่างไรบ้างครับ
ผมใช้ code script ตามที่พี่ให้มาเลยนะคับ มันส่งกลับมาให้แต่ผู้สร้างเท่านั้น
รบกวนทักมาทางinbox เพจหน่อยครับ
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy