กรอกGoogle Formแล้วสรุปเป็นPDFส่งเข้าเมล&แจ้งผ่านLine

เป็นอีกฟีเจอร์ที่คนเรียกร้องเข้ามาเยอะมาก เพราะต้องการสรุปข้อมูลผู้กรอกFormมาทำเป็นPDF ซึ่งถ้าใช้Pluginจะติดข้อจำกัดหลายอย่าง ทั้งการจัดรูปแบบหรือแม้แต่การส่งข้อมูลผ่าน Line Notify วันนี้เราจะมาทำโปรเจกต์นี้โดยการเขียนโค้ดเองทั้งหมด ทำให้ไม่ติดข้อจำกัดใดๆทั้งสิ้นและที่สำคัญคือใช้ฟรีกันแบบ 100% กันไปเล้ยยย

Form >> Copy

Slide >> Copy

main.gs(Version1.3.2)

// Presented by BrilliantPy ✓ v.1.3.3

/*######################### Editable1 Start #########################*/
let templateSlideId = '1s-CnbC13tEk3o4grrpEXWSlit0Rku6GmKNKjwBLP';
let folderResponsePdfId = '1OzsVcKP23BswohNZ7EB0lDSiOYBLP';
let folderResponseSlideId = '1Xf4m38R1GsU_wIxWs9Y51MoIDZBLP';
let sheetName = 'การตอบแบบฟอร์ม 1';
let pdf_file_name = "register_";
let isSendEmail = true;
let isSendLine = true;
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,"send_email_status":13,"send_line_status":14};

let colEmail = index_col["อีเมล"] || "";
let colName = index_col["ชื่อ-สกุล"] || "";
let colEmailStatus = index_col["send_email_status"] || "";
let colLineStatus = index_col["send_line_status"] || "";
let colEmailStatusName = "N";
let colLineStatusName = "O";

//let index_slide_img_arr = [0,1,2];
let index_slide_img_arr = [0];
// 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;

function formToSlidePdfLine() {
  /*######################### Editable2 Start #########################*/
  function formatMsgToLine() {
    return `ฟอร์มการสมัครงานใหม่

${filePath}`;
  }
  /*#########################  Editable2 End  #########################*/
  initSpreadSheet().then(async function() {
    formatTitle();
    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) {}
      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];
          //try{
            //cur_data[index_col["ประทับเวลา"]] = customFormatDate(cur_data[index_col["ประทับเวลา"]],"date","dd/MM/yyyy HH:mm:ss");
            //cur_data[index_col["เวลา QMS"]] = customFormatDate(cur_data[index_col["เวลา QMS"]],"time","HH:mm:ss");
          //}catch(e){}
          await updateSlideData(cur_data).then(async function() {
            presentation.saveAndClose();
            await createPdf().then(async function() {
              removeTempSlide();
              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');
              }
            });
          });
        });
      });
    }
    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();
//     console.log('zzz all_shape',typeof(all_shape),all_shape)
//     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 updateSlideData(cur_data) {
  return new Promise(function(resolve) {
    // Init
    newSlideId = newSlide.getId();
    presentation = SlidesApp.openById(newSlideId);
    let _all_shape = [];
    if(colAllImage.length>0){
      for(let i=0;i<index_slide_img_arr.length;i++){
        let item = index_slide_img_arr[i];
        let slide = presentation.getSlides()[item];
        _all_shape.push(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]) {
            for(let i=0;i<_all_shape.length;i++){
              all_shape = _all_shape[i];
              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);
  }
  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);
}