วันนี้เราจะมาสร้างระบบเช็คอินผู้ใช้ผ่านการสแกน QR Code ซึ่งระบบนี้จะมีการทำงานดังนี้
Google Form -> Send email+with QR Code -> ผู้สมัครนำ QR Codeมาสแกนหน้างาน -> Completed!!!
// Presented by BrilliantPy // Editable let sheetName = 'การตอบแบบฟอร์ม 1'; let col_user_name = 'ชื่อ-สกุล'; let col_email_name = 'อีเมล'; let col_qr_name = 'qr'; let col_status_name = 'send email status'; let qr_col = "D"; let send_email_status_col = "E"; let check_in_status_col = "F"; let index_col = { 'ประทับเวลา': 0, 'ชื่อ-สกุล': 1, 'อีเมล': 2, 'qr': 3, 'send email status': 4, 'check in status': 5,}; let mail_subject = 'แจ้งผลการลงทะเบียน'; let key = "o3MzIzhHJF" // Init let ss,sheet,lastRow,lastCol,range,values; let title,data; let cur_data,cur_data_row; let sent_status = 'SENT'; let checked_status = 'checked'; let special_str = "||:||"; function registerScanQR() { // Editable // Init initSpreadSheet(); for (let i = 0; i < data.length; i++) { cur_data = data[i]; cur_data_row = i+2; cur_user_name = cur_data[index_col[col_user_name]]; if(cur_data[index_col[col_status_name]] == sent_status) { continue; } console.log("cur_data:",cur_data,cur_data_row); //Create QR code formula let user_data = cur_user_name + special_str + cur_data_row; let qr_formula = "=IMAGE(\"https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl=\"&ENCODEURL(\""+user_data+"\"))"; //Set QR code formula to Spread Sheet sheet.getRange(qr_col.concat(cur_data_row)).setFormula(qr_formula); let mail_msg = "ขอบคุณที่ลงทะเบียนกับเรา กรุณานำ QR Code ตามลิงก์ด้านล่างไปสแกนก่อนเข้างาน ขอบคุณสำหรับการเข้าร่วมกิจกรรม"+ "<br><br>" + "<img src='https://chart.googleapis.com/chart?chs=250x250&cht=qr&chl=" + user_data + "'/>"; console.log("mail_msg:",mail_msg); // MailApp.sendEmail(cur_data[index_col[col_email_name]], mail_subject, mail_msg); MailApp.sendEmail({ to: cur_data[index_col[col_email_name]], subject: mail_subject, htmlBody: mail_msg }); sheet.getRange(send_email_status_col.concat(cur_data_row)).setValue(sent_status); // Make sure the cell is updated right away in case the script is interrupted SpreadsheetApp.flush(); } } function initSpreadSheet() { ss = SpreadsheetApp.getActive(); sheet = ss.getSheetByName(sheetName); lastRow = sheet.getLastRow(); lastCol = sheet.getLastColumn(); range = sheet.getDataRange(); values = range.getValues(); title = values.shift(); data = values; console.log('init completed') } function doGet(e) { let action = e.parameter.action; if (action == checked_status) { return checkInStatus(e); } } function checkInStatus(e) { initSpreadSheet(); let parameter = e.parameter || ""; let name_param = parameter.name || ""; let check_index_param = parameter.index || ""; let key_param = parameter.key || ""; console.log(`parameter:${parameter} name_param:${name_param} check_index_param:${check_index_param} key_param:${key_param}`); if (parameter == "" || check_index_param == "" || name_param == "" || key_param == "") { return ContentService.createTextOutput('Invalid Param').setMimeType(ContentService.MimeType.TEXT); } let check_in_col = check_in_status_col.concat(check_index_param); console.log("data:",data); cur_data = data[check_index_param-2]; console.log("cur_data:",cur_data) if (name_param != cur_data[index_col[col_user_name]] || key_param != key) { return ContentService.createTextOutput('Invalid Data').setMimeType(ContentService.MimeType.TEXT); } sheet.getRange(check_in_col).setValue(checked_status); return ContentService.createTextOutput('Success').setMimeType(ContentService.MimeType.TEXT); }
Hello. fantastic job. I did not imagine this. This is a remarkable story. Thanks!
Thank you. You can follow us either way.
youtube : https://www.youtube.com/@brilliantpy/videos
Facebook: https://web.facebook.com/BrilliantPy