Excel Forum

Code: Select all

// Presented by BrilliantPy ✓ v.1.3.2

/*######################### Editable1 Start #########################*/
let templateSlideId = '1MYT2wuumiQ8vR1T9NokGYMGkKCcTW4LY0gbV-K8mNOk';   //id slide
let folderResponsePdfId = '1hwEd7oHHrYiINBT1oFkNrRGUVDzlaBhs';  //id slide Pdf
let folderResponseSlideId = '1K0u47AXWhmY6qPTNGtFEPIk-qd0FE4ma';   //id slide_temp
let sheetName = 'การตอบแบบฟอร์ม 1';  //ชื่อหน้า ใน Google Sheets
let pdf_file_name = "ฟอร์มคลังจ่ายออก_";   //ชื่อไฟล์ pdf ในส่วนที่หนึ่ง นำหน้า
let isSendEmail = true;  //ส่งเมล์   false true
let isSendLine = true;   //ส่งLine  false true
// let email_send_default = [];  //ไม่ต้องส่ง email หาคนทำ Form
let email_send_default = ['yodpao.b@gmail.com'];  // ส่งกลับ 1 mail
// let email_send_default = ['brilliantpy1.live@gmail.com','brilliantpy2.live@gmail.com'];   // ส่งกลับ 2 mail
var email_subject = 'แจ้งเอกสารคลังจ่ายออก';   //ใช้สำหรับส่งเมล์ หัวเรื่อง
var email_message = 'ยินดีด้วย คุณได้ทำเอกสารคลังจ่ายออกหรือใบส่งมอบ ผ่านระบบออนไลน์เรียบร้อยแล้ว สามารถ Download ไฟล์ที่แนบมานี้ เพื่อนำไปใช้เป็นหลักฐานในการเบิกจ่ายได้เลยครับ';    //ใช้สำหรับส่งเมล์ เนื้อเรื่อง


let index_col = {"auto_id":0,"ประทับเวลา":1,"อีเมล":2,"วันดำเนินการ":3,"จ่ายหน่วยงานหรือผู้รับมอบผลิตภัณฑ์":4,"ถุงยังชีพเปล่า":5,"วัตถุประสงค์":6,"หน่วยงาน":7,"น้ำดื่มเล็ก":8,"น้ำดื่มกลาง":9,"อาหารแห้ง":10,"ผ้าห่ม EGAT":11,"ผ้าห่ม  ENGY":12,"ถุงเปล่า":13,"จ่ายหน่วยงาน":14,"กิจกรรม":15,"รูปโปรไฟล์1":16,"รูปโปรไฟล์2":17,"รูปโปรไฟล์3":18,"รูปโปรไฟล์4":19,"ทักษะ [การพูด]":20,"ทักษะ [การฟัง]":21,"วัตถุประสงค์[ถุงเปล่าสำรองไว้ที่ รส.กฟผ.]":22,"วัตถุประสงค์[จ่ายถุงเปล่าให้ รส.กฟผ.ใช้เป็นครั้ง]":23,"ทักษะ [การพูด][ไทย]":24,"ทักษะ [การพูด][อังกฤษ]":25,"ทักษะ [การฟัง][ไทย]":26,"ทักษะ [การฟัง][อังกฤษ]":27,"text_date":28,"text_id":29,"แทนที่1":30,"แทนที่2":31,"send_email_status":32,"send_line_status":33};
//หัวคอลัม ใน Google Sheets คัดลอกมาได้


let colEmail = index_col["อีเมล"] || "";    // เกี่ยวกับ การส่ง pdf กลับไป Email ผู้ตอบกลับ
let colName = index_col["text_id"] || "";  // คอลัมน์ที่จะนำมาตั้งเป็นชื่อไฟล์ pdf ในส่วนที่สอง
let colEmailStatus = index_col["send_email_status"] || "";   // เกี่ยวกับ Status คอลัม Email
let colLineStatus = index_col["send_line_status"] || "";  // เกี่ยวกับ Status คอลัม Line
let colEmailStatusName = "AG";   // เกี่ยวกับ Status คอลัม Email
let colLineStatusName = "AH";    // เกี่ยวกับ Status คอลัม Line


// let colAllImage = [];  // ไม่มีรูปภาพ
let colAllImage = [
  {[index_col['รูปโปรไฟล์1']] : '{{รูปโปรไฟล์1}}'},
  {[index_col['รูปโปรไฟล์2']] : '{{รูปโปรไฟล์2}}'},
  {[index_col['รูปโปรไฟล์3']] : '{{รูปโปรไฟล์3}}'},
  {[index_col['รูปโปรไฟล์4']] : '{{รูปโปรไฟล์4}}'},
  ];
// let colAllImage = [
//   {[index_col['รูปโปรไฟล์1']] : '{{รูปโปรไฟล์1}}'},
// ];


// let index_col_checkbox = [];   //ไม่มี checkbox
let index_col_checkbox = [
  { [index_col['วัตถุประสงค์']] : [{'ถุงเปล่าสำรองไว้ที่ รส.กฟผ.':'W'},{'จ่ายถุงเปล่าให้ รส.กฟผ.ใช้เป็นครั้ง':'X'}] },    //ใช้ในกรณี ที่ใน google slide มี checkbox ให้คลิกถูก ถ้ามีหลายอัน ก็ให้ คัดลอกทั้งบรรทัดลงด้านล่าง
  ];


// let index_col_multi_checkbox = [];    //ไม่มี checkbox
let index_col_multi_checkbox = [
  { [index_col['ทักษะ [การพูด]']] : [{'ไทย':'Y'},{'อังกฤษ':'Z'}] },  //ใช้ในกรณี ที่ใน google slide มี checkbox ให้คลิกถูก ถ้ามีหลายอัน ก็ให้ คัดลอกทั้งบรรทัดลงด้านล่าง
  { [index_col['ทักษะ [การฟัง]']] : [{'ไทย':'AA'},{'อังกฤษ':'AB'}] },  //ใช้ในกรณี ที่ใน google slide มี checkbox ให้คลิกถูก ถ้ามีหลายอัน ก็ให้ คัดลอกทั้งบรรทัดลงด้านล่าง
  ];  
let tokensV2 = ['xVp96xhDZvSBqV5WeWFksTwIxDGH1lKYZoL5UuS6ve4']; // BrilliantPy line group(test only)  
//เกี่ยวกับสร้างกลุ่ม Line

/*#########################  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 `ส่งเอกสาร คลังจ่ายออกหรือใบส่งมอบ งานบรรเทาสาธารณภัย และ ส่งแจ้งไปทาง email@gmail.com ของท่านแล้ว


${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();
    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);
  }
  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);
}