Day: October 10, 2014

วิธีการใช้ Google Sheets เป็นฐานข้อมูล

kanakorn.h

October 10, 2014
Google Sheets เป็นหนึ่งใน Google Apps ซึ่งเป็น Application Suite ของ Google ประกอบด้วย

ในการใช้งานทั่วไป Google Apps สามารถตอบสนองการใช้งานได้เป็นอย่างดี แต่เมื่อต้องการทำกิจกรรมบางอย่างที่นอกเหนือไปจากการใช้งานพื้นฐาน ผู้ใช้สามารถพัฒนาเพิ่มเติมได้เอง ด้วย Google Apps Script

Google Apps Script เป็น Scripting Language ที่อยู่บนพื้นฐานของภาษา JavaScript สามารถใช้งานได้และพัฒนาต่อยอดได้ทันทีโดยไม่ต้องติดตั้งอะไรเพิ่มเติมอีกแล้ว สามารถเรียกใช้ Google Service ต่างๆได้มากมาย รวมถึง Google Sheets เพื่อสร้าง เมนูพิเศษ หรือ Macro เพื่อให้การทำงานที่ทำหลายๆขั้นตอนลดลงเหลือเพียงแค่คลิกเดียว อีกทั้งยังสามารถตั้งเวลาให้ทำงานอัตโนมัติ หรือ ตั้ง Trigger เพื่อให้ทำงานเมื่อเกิด Action ต่างๆได้อีกด้วย

Google Apps Script มี 3 ชนิด ได้แค่ Standalone, Bound to Google Apps และ Web App ซึ่งจะสามารถใช้งานร่วมกับ Google Sites ได้อีกด้วย (Sites Gadget) รายละเอียดสามารถอ่านเพิ่มเติมได้ที่ Google Apps Script

ในที่นี้ จะแสดงตัวอย่างการประยุกต์ใช้ Google Apps Script แบบ Standalone เพื่อพัฒนาให้ Google Sheets ทำหน้าที่เป็นฐานข้อมูล และจะนำไปสู่การต่อยอดเป็น วิธีการใช้ Google Sheets เป็นระบบเฝ้าระวังเว็บไซต์ (Website Monitoring) จากภายนอกองค์กร เพื่อตรวจสอบระยะเวลาในการตอบสนอง ( Response Time) ของเว็บไซต์ ได้อีกด้วย

วิธีการใช้งาน Google Apps Script แบบ Standalone
1. ใน Google Drive คลิก New > More > Connect more apps
1. ในชื่อ search ใส่คำว่า script แล้วกด Enter
  จะพบ Google Apps Script แล้วกดปุ่ม Connect
2. จากนั้น ใน Google Drive ให้คลิกที่ New > More > Google Apps Script
3. จากนั้นให้คลิก Close ได้เลย
4. จะได้พื้นที่โปรเจค (Project) ในการพัฒนา Google Apps Script โดยในแต่ละโปรเจคจะประกอบไปด้วยหลายๆไฟล์ Google Apps Script ได้
ในการพัฒนา Google Apps Script นั้น จะต้องเขียนในรูปแบบของฟังก์ชั่น (Function) เพื่อให้สะดวกในการใช้งานต่างๆ

ตัวอย่างเช่น มี Google Sheets อยู่ใน Google Drive ดังภาพ

มีรายละเอียดดังนี้
1. ชื่อของ SpreadSheet คือ “ฐานข้อมูลของฉัน”
2. ประกอบไปด้วย Sheet ชื่อ “Sheet1” และ “Log”
3. มี URL คือ
  https://docs.google.com/a/psu.ac.th/spreadsheets/d/1HJmyqiBYC_AEATmdUWakLgHFyYGqSqeqSA8xEw-8o-c/edit
ต่อไปเป็นขั้นตอนการเขียน Google Apps Script เพื่อติดต่อกับ Google Sheet ข้างต้น เพื่อเขียนข้อมูลลงไป โดยตั้งชื่อโปรเจคนี้ว่า ProjectMyDB ตั้งชื่อไฟล์ว่า SheetDB.gs และตั้งชื่อฟังก์ชั่น “editSheet” ดังภาพ

ขั้นตอนการทำงานของฟังก์ชั่น editSheet
1. สร้างตัวแปร ss รับค่าจากการเปิด SpreadSheet จาก URL ข้างต้นด้วยคำสั่ง
```
var ss = SpreadsheetApp.openByUrl('https://docs.google.com/a/psu.ac.th/spreadsheets/d/1HJmyqiBYC_AEATmdUWakLgHFyYGqSqeqSA8xEw-8o-c/edit');
```
1. สั่งให้ SpreadSheet ดังกล่าว Active ด้วยคำสั่ง
```
SpreadsheetApp.setActiveSpreadsheet(ss);
```
1. เนื่องจากในแต่ละ SpreadSheet ประกอบด้วยหลาย Sheet จึงต้องระบุว่า จะทำงานกับ Active Sheet ชื่อ “Sheet1” ด้วยคำสั่ง
```
SpreadsheetApp.setActiveSheet(ss.getSheetByName("Sheet1"));
```
1. สร้างตัวแปร activeSheet เพื่อกำหนดว่ากำลังทำงาน Active Sheet ด้วยคำสั่ง
```
var activeSheet=ss.getActiveSheet();
```
1. เมื่อต้องการเขียนค่า “Hello World” ลงใน Active Sheet ที่ Cell “C3” ใช้คำสั่ง
```
activeSheet.getRange("C3").setValue("Hello World");
```
1. หากต้องการเขียนค่าทีละหลายๆ Cell หรือเป็น Range ต้องสร้างข้อมูลชนิด Array 2 มิติขึ้นมา แล้วจึงเขียนค่าลงไป กรณีต้องการใส่ค่าในช่วง “A1:C1” ใช้คำสั่ง
```
var values =[  ["คณกรณ์","หอศิริธรรม","'3720024"]  ];
activeSheet.getRange("A1:C1").setValues(values);
```
1. หากต้องการเขียนค่าในช่วง “A2:A4” ใช้คำสั่ง
```
values = [ ["เกรียงไกร"],["หนูทองคำ"],["'4220020"] ];
activeSheet.getRange("A2:A4").setValues(values);
```
1. เมื่อจะเก็บข้อมูลจริงๆ วิธีการข้างต้นจะไม่สะดวก เพราะจะต้องทราบว่าแถวสุดท้ายแล้วเพิ่มค่าแถวไปทีละหนึ่ง ซึ่งสามารถใช้วิธีการ Append Row กล่าวคือเขียนค่าลงไปในแถวถัดจากแถวล่าสุดที่มีข้อมูลได้ ในตัวอย่างนี้ จะสลับไปใช้ Sheet ชื่อ “Log” แล้วใส่ค่าลงไปด้วยคำสั่ง
```
SpreadsheetApp.setActiveSheet(ss.getSheetByName("Log"));
activeSheet=ss.getActiveSheet();
var timestamp = new Date();
activeSheet.appendRow([timestamp, 200 , 300]);
timestamp = new Date();
activeSheet.appendRow([timestamp, 200 , 456]);
```
จากนั้น Save ข้อมูล แล้วสั่ง Run โดยเลือกฟังก์ชั่นชื่อ editSheet ดังภาพ

ในการใช้งานครั้งแรก จะปรากฏหน้าต่าง Consent ขึ้นมาเพื่อขอสิทธิ์ในการเข้าใช้ไฟล์

ผลที่ได้จากการทำงานคือ

และ

จะเห็นได้ว่าสามารถใช้ Google Apps Script เพื่อเขียนค่าใน Google Sheets เพื่อเป็นฐานข้อมูลได้ และสามารถประยุกต์ใช้งานอื่นๆได้อีกมากมาย
October 10, 2014
สร้างแบบฟอร์มลงทะเบียนออนไลน์ด้วย Google Form ให้ปิดการลงทะเบียนแบบอัตโนมัติเมื่อครบตามจำนวนที่ต้องการ

sarawut.le

October 10, 2014
อีกหนึ่งปัญหาของการใช้งาน Google Form เพื่อสร้างแบบฟอร์มลงทะเบียนออนไลน์ ที่ผมได้เจอมากับตัวเองคือ ไม่สามารถจำกัดจำนวนผู้ที่มาลงทะเบียนแบบออนไลน์ได้ ทั้ง ๆ ที่การจัดอบรม/สัมมนาต่าง ๆ เราประกาศว่า “รับจำนวนจำกัด” จึงเป็นที่มาของการหาคำตอบในครั้งนี้ครับ ว่า…

“ทำอย่างไร ให้แบบฟอร์มลงทะเบียนออนไลน์ (Google Form)
สามารถปิดการลงทะเบียนได้
เมื่อมีผู้สมัครครบตามจำนวนที่ต้องการ ?”

** หมายเหตุ **

การใช้ Script : formLimiter v.1.0.2 นี้ จะสามารถใช้ได้กับ Google Spreadsheets “เวอร์ชั่นเก่า” เท่านั้นครับ เวอร์ชั่นปัจจุบัน ไม่สามารถใช้งานได้ เนื่องจาก Google ได้มีการเปลี่ยนแปลงการใช้งานของ Script ใหม่จาก Script Gallery ไปเป็น Add-ons ครับ
```
Script Gallery ==> Add-ons
```
ซึ่ง formLimitter นั้น ผู้พัฒนายังไม่ได้พัฒนาเข้าไปอยู่ใน Add-ons จึงทำให้หลาย ๆ ท่าน ที่สร้าง Google Spreadsheets ด้วยเวอร์ชั่นปัจจุบัน ไม่สามารถใช้งาน Script อันนี้ได้ครับ

ดังนั้น หากผู้ใช้งาน ต้องการใช้ Script ดังกล่าว แนะนำว่าให้สร้าง Google Spreadsheets ด้วยเวอร์ชั่นเก่า นะครับ สามารถเข้าไปสร้างผ่านลิงค์ด้านล่างนี้ได้เลยครับผม
```
goo.gl/pprjA5
```
1. ไปที่มุมมองของ View responses

จากนั้น คลิกเมนู Tools > เลือก Script gallery…

แล้วค้นหา Script ที่ชื่อว่า “formLimiter” ในช่อง Search แล้วคลิกปุ่ม Search
เมื่อปรากฎ “formLimiter v.1.0.2” ให้ดำเนินการติดตั้ง โดยคลิกปุ่ม Install

คลิกปุ่ม Continue > ปุ่ม Accept > และปุ่ม Close เป็นอันเสร็จเรียบร้อย

2. จะเห็นว่ามีเมนูที่ชื่อว่า formLimiter ปรากฎเพิ่มขึ้นมาใหม่
จากนั้นให้ คลิกเมนู formLimiter > เลือก Run initial configuration เพื่อเปิดการใช้งาน Script

เมื่อเปิดการใช้งานเรียบร้อย เมนูย่อยจะเปลี่ยนไปดังภาพด้านล่าง ให้ คลิกเลือก Settings

3. ในส่วนนี้จะเป็นการกำหนดเงื่อนไขของการ “ปิดแบบฟอร์มการลงทะเบียนออนไลน์”
ซึ่งจะมีด้วยกัน 3 เงื่อนไขให้เลือกใช้ ดังนี้

3.1 date and time – ใช้ในกรณีที่กำหนดให้ปิดการลงทะเบียนเป็นช่วงของเวลา โดยไม่จำกัดจำนวนผู้สมัคร

3.2 max number of form response – ใช้ในกรณีที่ต้องการรับผู้สมัครแบบจำกัดจำนวน

3.3 spreadsheet cell value – ใช้ตรวจสอบเงื่อนไขตามค่าของข้อมูลใน cell
จะปิดการลงทะเบียนเมื่อข้อมูลใน cell ตรงกับค่าที่ได้กำหนดไว้

ในส่วนนี้ผมขอให้เลือกเป็น spreadsheet cell value นะครับ
โดยให้กำหนดเงื่อนไขในลักษณะดังนี้

ช่องแรก ให้ระบุ Cell ที่ใช้เป็น “Sheet2!A1” และ
ช่องสอง ให้ระบุจำนวนที่ต้องการรับลงทะเบียน (ในที่นี้ผมกำหนดรับที่ 50 คน)

แนะนำว่า ให้นำเครื่องหมายถูกออก เนื่องจากระบบจะทำการส่งเมลทุกครั้งที่มีการลงทะเบียน
อาจทำให้เกิดอีเมลขยะจากการแจ้งเตือนเป็นจำนวนมากได้ (กรณีที่รับผู้สมัครจำนวนมาก)

จากนั้น คลิกปุ่ม Save settings เป็นอันเสร็จสิ้น

4. ขณะนี้เรายังอยู่ในหน้าของ View response
ให้ทำการสร้าง “Sheet2” เพิ่มขึ้นอีกหนึ่ง เพื่อใช้ในการนับจำนวนผู้สมัคร (ดังภาพ)
โดยให้กำหนดเงื่อนไขใน Cell A1 ดังนี้ =COUNTUNIQUE('Form Responses'!B2:B1000)

ในส่วนนี้จะเป็นการแก้ปัญหา กรณีที่ผู้สมัครลงทะเบียนซ้ำหลาย ๆ ครั้ง (ซึ่งมีอยู่บ่อยครั้ง)
เงื่อนไขนี้จะทำการนับจำนวนจากชื่อ-นามสกุลของผู้สมัคร หากซ้ำกันจะนับเป็น 1 คน
(*ยกเว้นในกรณีที่ผู้ลงทะเบียน พิมพ์ชื่อ-นามสกุลในแต่ละครั้ง ไม่เหมือนกัน)
เมื่อครบตามจำนวนที่กำหนดไว้ ระบบจะทำการปิดแบบฟอร์มลงทะเบียนโดยอัตโนมัติ

5. จากนั้นให้กลับมาที่มุมมองของ Edit questions ซึ่งเป็นหน้าเริ่มต้นของการสร้างแบบฟอร์ม

คลิกที่เมนู Response > เลือก Accepting responses

จากนั้นจะปรากฎ กล่อง This form has been turned off. ขึ้นมาให้
ซึ่งกล่องนี้จะปรากฎขึ้น เมื่อมีผู้สมัครลงทะเบียนครบตามจำนวนที่ได้กำหนดไว้
ดังนั้น ให้กำหนดข้อความแสดงการ “ปิดรับการลงทะเบียน” เพื่อแจ้งให้ผู้สนใจได้รับทราบ
ดังตัวอย่างข้อความในภาพ

เมื่อกำหนดข้อความดังกล่าวเสร็จเรียบร้อยแล้ว
ให้กลับไปคลิกที่เมนู Response > เลือก Accepting responses อีกครั้ง
เพื่อปิดกล่องดังกล่าว (หากไม่ปิด หน้าแบบฟอร์มลงทะเบียนจะมีลักษณะดังภาพด้านล่าง)
และทำให้ผู้สมัคร ไม่สามารถลงทะเบียนออนไลน์ได้

ลักษณะของหน้าแบบฟอร์มลงทะเบียน เมื่อมีผู้สมัครลงทะเบียนครบตามจำนวนที่กำหนดไว้
October 10, 2014
สร้างแบบฟอร์มลงทะเบียนออนไลน์ด้วย Google Form ให้ตอบรับผ่านทางอีเมลโดยอัตโนมัติ

sarawut.le

October 10, 2014
เนื่องจากว่ามีผู้สอบถามถึงการใช้งานแบบฟอร์มลงทะเบียนออนไลน์ แบบให้สามารถตอบรับผ่านทางอีเมลโดยอัตโนมัติไปยังผู้สมัครเมื่อเข้ามาลงทะเบียน ผมเห็นว่าน่าจะมีประโยชน์สำหรับ Admin หรือผู้ใช้งาน Google Form หลาย ๆ ท่าน จึงได้สรุปเป็นขั้นตอน ตามที่ผมได้ทำมา หวังว่าจะเป็นประโยชน์ให้กับทุก ๆ ท่านนะครับ

** หมายเหตุ **

เนื่องจาก Google ได้มีการเปลี่ยนแปลงการใช้งานของ Script ใหม่
จาก Script Gallery ไปเป็น Add-ons ครับ
```
Script Gallery ==> Add-ons
```
ซึ่งบทความที่ผมเขียนขึ้นนี้ ขออ้างอิงเมนูและการใช้งาน Google Spreadsheets เวอร์ชั่นเก่านะครับ
ดังนั้น หากผู้ใช้งาน ต้องการใช้งาน Google Spreadsheets เวอร์ชั่นเก่า สามารถเข้าไปสร้างผ่านลิงค์ด้านล่างนี้ได้เลยครับผม
```
goo.gl/pprjA5
```
1. คลิก Form เพื่อสร้างแบบฟอร์มลงทะเบียนออนไลน์

2. กำหนดรายละเอียดข้อมูลของแบบลงทะเบียนตามที่ต้องการ
(ในส่วนนี้จะไม่ขออธิบายรายละเอียดในการสร้างแบบฟอร์ม นะครับ)

3. คลิก View response เข้าสู่มุมมองของ View responses เพื่อกำหนดต่าง ๆ

4. คลิกเมนู Tools > เลือก Script manager… (เป็นส่วนจัดการ Script ต่าง ๆ)
จากนั้น คลิกปุ่ม New เพื่อสร้าง Script สำหรับการส่งอีเมลแบบอัตโนมัติ

ตั้งชื่อไฟล์ Script ใหม่ โดยใช้ชื่อว่า “sendEmail”

จากนั้น Copy Script ด้านล่างนี้ วางไว้ดังภาพข้างต้น
** ข้อมูลนี้เป็นข้อมูลจำลอง เพื่อให้สามารถเข้าใจในรายละเอียดของ Script ได้
เมื่อผู้ใช้งานนำไปใช้จริง สามารถแก้ไขรายละเอียดได้ตามความเหมาะสม **
```
function sendEmail(e) {
 var userEmail = e.values[4];
 var firstName = e.values[1];
 var trainingTitle = "อบรมการใช้โปรแกรม Smart Notebook sympodium & Smart board สำหรับการจัดการเรียนการสอนทางไกล";
 var infoLink = "http://clpd.psu.ac.th/clpd_2012";
 var emailAdd = "clpdpsu@psu.ac.th";
 var subject = "ศูนย์ส่งเสริมและพัฒนาการเรียนรู้ ม.อ. : ตอบรับเข้าร่วมอบรมโครงการเชิงปฏิบัติการ"
 var emailBody = "เรียนคุณ: "+firstName+"\n\n ท่านได้ทำการลงทะเบียนเข้าร่วมโครงการ"+
 "หัวข้อ \"" + trainingTitle + "\""+
 " ในวันพุธที่ 16 กรกฎาคม 2557 เวลา 09.00-16.00 น."+
 " ณ ห้องอบรม ชั้น 8 อาคารศูนย์ทรัพยากรการเรียนรู้ อาคาร 1 มหาวิทยาลัยสงขลานครินทร์ วิทยาเขตหาดใหญ่"+
 "\n ทางศูนย์ส่งเสริมและพัฒนาการเรียนรู้ มหาวิทยาลัยสงขลานครินทร์ ตอบรับการเข้าร่วมโครงการดังกล่าวแล้วค่ะ"+
 "\n"+
 "\n ติดตามการประกาศรายชื่อผู้มีสิทธิ์เข้าร่วมอบรมโครงการอีกครั้งได้ที่: " + infoLink + 
 "\n"+
 "\n"+
 "\nขอบคุณสำหรับการลงทะเบียนเข้าร่วมโครงการค่ะ"+
 "\n"+
 "\nศูนย์ส่งเสริมและพัฒนาการเรียนรู้ มหาวิทยาลัยสงขลานครินทร์"+
 "\nโทรศัพท์ : 074-289203-4"+
 "\nE-mail : " + emailAdd;
 MailApp.sendEmail(userEmail , subject, emailBody); 
}
```
หมายเหตุ
อักษรสีแดง คือ ข้อความที่สามารถแก้ไขได้
อักษรสีฟ้า คือ ชื่อตัวแปร ที่ใช้สำหรับเก็บค่าต่าง ๆ ที่กำหนดไว้
* สามารถปรับแก้ไข Script ได้ตามข้อมูลของแบบฟอร์มที่ได้สร้างขึ้น
ซึ่ง Script นี้ อ้างอิงตามแบบฟอร์มลงทะเบียนในข้อที่ 2

5. เมื่อกำหนดรายละเอียดข้อมูลการตอบรับ (Script) เสร็จเรียบร้อยแล้ว
จากนั้น คลิกเมนู Resources > เลือก Current Project’s triggers

และคลิก No triggers setup. Click here to add one now.

เลือก On form submit แล้วคลิกปุ่ม Save / ปุ่ม Continue และ ปุ่ม Accept
เป็นอันเสร็จสิ้นการสร้าง Script สำหรับการตอบรับอีเมลแบบอัตโนมัติ

คลิกปุ่ม Close (หากต้องการแก้ไข Script ให้คลิกปุ่ม Edit… ด้านบน)

6. ทดสอบการตอบรับการลงทะเบียน
ว่า Script ที่สร้างไว้ มีข้อมูลที่ถูกต้องและสามารถตอบรับทางอีเมลแบบอัตโนมัติได้

ไปที่มุมมอง View live form เพื่อทดสอบการลงทะเบียนจริง

จากนั้นให้ตรวจสอบดูในมุมมอง View responses ว่ามีข้อมูลปรากฎให้เห็นหรือไม่
และตรวจสอบอีเมลที่ได้ระบุไว้ ว่ามีการตอบรับจากระบบหรือไม่

หมายเหตุ หากพบว่าไม่มีการส่งอีเมลโดยอัตโนมัติจากระบบ อาจต้องทำการตรวจสอบ Script ใหม่อีกครั้ง เพื่อตรวจหาข้อผิดพลาด
October 10, 2014