Tag: google script

  • ใช้ google script ทำ mail merge เนื้อหาเมลที่มี pre-filled form URL

    จากตอนที่แล้ว “ใช้ google script ทำ mail merge เนื้อหาเมลที่มี link” เรารู้วิธีใส่ link ไปในเนื้อหาเมล ในตอนนี้เราจะมาเปลี่ยน static link เป็น dynamic link (คือ link ที่มีการอ้างชื่อ cell จึงเป็น link ที่เปลี่ยนแปลงไปตามค่าที่อยู่ใน cell) จุดประสงค์ก็เพื่อส่ง link ให้ผู้ที่รับอีเมลกรอกข้อมูลลง google form ที่มีข้อมูลให้บางส่วนแล้ว (pre-filled form) ซึ่งเป็นข้อมูลของผู้รับอีเมลแต่ละคนที่แตกต่างกัน เช่น ให้มีฟิลด์ ชื่อ และ หน่วยงาน ไว้แล้ว เป็นต้น เมื่อเปิดฟอร์มจึงไม่ต้องกรอกชื่อ และ หน่วยงาน แต่ยังสามารถแก้ไขได้หากต้องการ แล้วให้กรอกข้อมูลเพิ่มในส่วนอื่น ๆ

    สมมติ
    1. เรามี google sheet ชื่อ members มี 4 คอลัมน์ อยู่แล้ว ดังนี้ รายการที่, ชื่อ, หน่วยงาน, อีเมล

    mmwp01

    2. เราต้องการสร้าง google form เพื่อสอบถาม ตอบรับร่วมงาน และ ประเภทอาหาร

    วิธีทำ ดังนี้
    1. สร้าง google form ขึ้นมา 1 อัน ตั้งชื่อว่า member-meeting1

    mmwp02

    2. สร้าง form field ดังนี้ ชื่อ, หน่วยงาน, ตอบรับร่วมงาน, ประเภทอาหาร

    3. เลือกเมนู Responses > เลือก Get pre-filled URL

    mmwp03

    4. กรอกข้อมูลชื่อตัวแปรลงในช่องข้อมูล เช่น ชื่อ ให้ใส่ f1, หน่วยงาน ให้ใส่ f2

    mmwp04

    5. กรอกเสร็จให้คลิก submit จะได้หน้าต่างแสดงเป็น URL และมีข้อความบอกให้เราคัดลอก (ctrl+c)

    mmwp05

    6. URL ที่คัดลอกนี้จะไปใส่ใน google sheet ชื่อ member ในคอลัมน์ใหม่ รวมเป็น 5 คอลัมน์ ดังนี้
    รายการที่, ชื่อ, หน่วยงาน, อีเมล, URL

    7. ที่ cell URL นี้ ให้ใส่ฟังก์ชั่น =HYPERLINK(“URL”,”URL”) และนำ URL ที่คัดลอกมาวางตรงคำว่า URL

    mmwp06

    8. ให้แทนที่ข้อความ ตรงที่มีคำว่า f1 ด้วย “&B2&”, แทนที่ f2 ด้วย “&C2&”

    mmwp07

    9. จากนั้นก็นำความรู้ในเรื่อง “ใช้ google script ทำ mail merge เนื้อหาเมลที่มี link” มาใช้ในการส่งอีเมลได้แล้ว
    9.1 เตรียมเนื้อหาจดหมายไว้ใน sheet ชื่อ Template
    mmwp08

    9.2 เตรียม range ของข้อมูล คีย์เองนะ

    mmwp09

    9.3 คัดลอก google script จากตอนที่แล้วมาใช้

    mmwp10

    9.4 สั่งให้ google script ชื่อ MailMergeWithLinkv2 ทำการส่งอีเมล

    mmwp11

    9.5 ตรวจสอบดูอีเมลที่ได้รับ

    mmwp12

    9.6 เมื่อคลิก link จากในเมล จะไปเปิด google form ขึ้นมา พร้อมข้อมูล 2 ฟิลด์แรก คือ ชื่อ และ หน่วยงาน ที่ดึงข้อมูลที่มีอยู่มาไว้ให้เลย จะได้ไม่ต้องคีย์บ่อย ๆ

    mmwp13

    9.7 ตรวจสอบข้อมูลที่กรอกผ่าน google form โดยที่เมนู Responses ใน form ให้เลือก Choose response destination และเลือก สร้างไฟล์ google spreadsheet ใหม่

    mmwp14

    จากนั้น จะได้ไฟล์

    mmwp15

    เปิดดู เห็นข้อมูลที่กรอกผ่าน google form มาเก็บไว้แล้ว

    mmwp16

     

    ความรู้นำมาจากเรื่องนี้:
    Pre-populate form answers
    https://support.google.com/docs/answer/160000?hl=en

  • ใช้ google script ทำ mail merge เนื้อหาเมลที่มี link

    ผมใช้ Yet another mail merge ซึ่งเป็น add-on เพื่อส่งอีเมลเรื่องเดียวกันไปยังเพื่อนๆจำนวนหนึ่ง ก็ใช้งานได้ดีมา 5 ครั้งแล้ว แต่พอครั้งหลังนี้ ผมต้องการส่งอีเมลที่ในเนื้อหามี link ให้ผู้ที่รับอีเมลสามารถคลิกเพื่อเปิดอ่านได้ แต่ลองหลายวิธีที่จะส่งด้วยวิธีที่เคยใช้ add-on ตัวนี้ก็จะแสดงเป็นข้อความ ไม่แสดงเป็น link ในอีเมล (อันนี้ต้องบอกก่อนว่า คาดว่าผู้ใช้ส่วนใหญ่ใช้โปรแกรมอีเมลที่มีการแสดงผลมากกว่าข้อความธรรมดา)

    ค้นหาดูก็พบว่า ต้องเปลี่ยนวิธีทำมาเป็นวิธีเขียน google script เพื่อส่ง mail merge แทน

    วิธีทำ ดังนี้
    1. สร้าง google sheet ตั้งชื่อ myusers
    1.1 sheet แรก ตั้งชื่อ Data ที่มีคอลัมน์ ดังนี้
    รายการที่, ชื่อ, คณะ, อีเมล, link1, link2

    mmwl01

    1.2 sheet สอง ตั้งชื่อ Template ที่มี 1 คอลัมน์ เพื่อเขียน subject และ ข้อความจดหมายที่มี link

    mmwl02

    1.3 sheet สาม ตั้งชื่อ Range ที่มี 3 คอลัมน์ ดังนี้
    header, data, template

    mmwl03

    2. ใส่ข้อมูลตามตัวอย่างรูปภาพ

    3. ที่ google sheet ให้คลิกที่ Tool > Script editor > เลือก Blank Project > ตั้งชื่อว่า myusers project

    mmwl04

    mmwl05

    4. แปะ google script ตามซอร์สโค้ดด้านล่างบทความนี้ และคลิก Save
    หมายเหตุ ซอร์สโค้ดนี้จะวางตำแหน่งอีเมลไว้ที่คอลัมน์ที่ 4

    mmwl06

    5. ปิดแท็บ google script (myusers project)

    6. ปิดแท็บ google sheet (myusers) แล้วเปิดใหม่

    7. เมื่อเปิดใหม่จะเห็นว่ามีเมนู Email และมี submenu ชื่อ MailMergeWithLinkv2

    mmwl07

    8. คลิกเมนู Email > MailMergeWithLinkv2
    จะมีการขอสิทธิในการรัน script ดังรูป

    mmwl08   mmwl09

    9. ไปตรวจสอบว่าอีเมลถูกส่งไปและมีเนื้อหาที่มี link ตามต้องการ

    mmwl10

    10. หากต้องการส่งอีกครั้งให้กับอีเมลที่ได้ส่งแล้ว ให้ดูที่คอลัมน์ อีเมล จะเห็นว่ามี Note เนื่องจากว่า google script นี้ได้เขียน Note ไว้ใน cell ใช้เป็นการป้องกันการส่งอีเมลซ้ำ

    mmwl11

    ให้คลิกขวาและเลือก Clear Note

    mmwl12

    ซอร์สโค้ดที่ใช้:

    function onOpen() {
      var submenu = [{name:"MailMergeWithLinkv2", functionName:"MailMergeWithLinkv2"}];
      SpreadsheetApp.getActiveSpreadsheet().addMenu('Email', submenu);  
    }
    
    function MailMergeWithLinkv2() {
      var ss = SpreadsheetApp.getActiveSpreadsheet(),
      DataSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Data")),
      TemplateSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Template")),
      RangeSheet = SpreadsheetApp.setActiveSheet(ss.getSheetByName("Range")),
    
      header = RangeSheet.getRange(2, 1).getValue(),
      data = RangeSheet.getRange(2, 2).getValue(),
      template = RangeSheet.getRange(2, 3).getValue(),
    
      dataRows = DataSheet.getRange(data).getValues(),
      headerRow = DataSheet.getRange(header).getValues(),
      numColumns = DataSheet.getRange(header).getNumColumns(),
      templateRows= TemplateSheet.getRange(template).getValues();
    
      for (var i=0 ; i < dataRows.length ; i++) {
        var mailmessage = "";
        for (var k=0 ; k < templateRows.length ; k++) {
          if ( k == 0 ) {
            var subject = templateRows[0][0];
          } else {
            mailmessage += templateRows[k][0] + "<br>\n"; 
          }
        }
        for (var j=0 ; j < numColumns ; j++) { 
          mailmessage = mailmessage.replace('{' + headerRow[0][j] + '}', dataRows[i][j]); 
        }
        //Test output with logger. Uncomment the line below.
        //Logger.log(mailmessage);
        //
        //Send Email start here, starting row is 2.
        var sender = "Sawadee Krub";   //email sender name
        var emailaddress = DataSheet.getRange(2+i, 4).getValue(); //email in column 4
        var options = {}
        options.htmlBody = mailmessage;
        options.name = sender;
        //Not send email again if it was sent. Clear note then can send email again.
        if (DataSheet.getRange(2+i, 4).getNote()) {
        } else {
          MailApp.sendEmail(emailaddress, subject, '', options);
          DataSheet.getRange(2+i, 4).setNote("Email sent on " + new Date); 
        }
        //Email sent
      } 
    }

     

    ความรู้นำมาจากเรื่องนี้:
    1) Embedding a link in Mail Merge
    https://productforums.google.com/forum/#!topic/docs/WvW-lAr3pg8
    และ
    2) Google Apps Scripts Workshop – Level 1
    http://sysadmin.psu.ac.th/2015/05/06/gasws-1/

    ในตอนต่อไป จะนำเสนอวิธีสร้าง link ที่เป็น pre-filled form URL ของ google form