สร้างPDF(รองรับMulti-checkbox&รูปภาพ)เมื่อกรอกGoogle Form+ส่งเข้าเมล์ผู้กรอก

ต่อเนื่องจากบทความก่อนหน้านี้ >> กรอก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;
  }
}