บทความนี้จะอธิบายวิธีการใช้ Power Query เชื่อมกับ Google Form/Google Sheets โดยที่เราสามารถกด Refresh ที่ Excel/Power BI เพื่อดูดข้อมูลจาก Google Form/Google Sheets ณ ตอนนั้นได้เลย
ซึ่งเป็นเรื่องที่ผมคิดว่ามีประโยชน์มากๆ วิธีทำจะเป็นยังไงมาดูกัน (อันนี้ไม่มีในหนังสือนะ แต่ผมทำบทความให้อ่านกันฟรีๆ เลยครับ 555)
ก่อนอื่นก็ไปสร้างฟอร์มใน Google Form ซะก่อน อันนี้แล้วแต่คุณเลย แต่ผมจะลองสร้างฟอร์มใหม่ให้กรอกเล่นๆ ละกัน
สารบัญ
Step หลัก 1 : เตรียม Google Form และหา URL
สร้างฟอร์มที่ Google Form
ผมก็ใส่คำถามไปประมาณนี้
สร้าง Link + ลองกรอกข้อมูล
เราก็จะมาลองกรอกข้อมูลมั่วๆ ลงไปซัก 2 อันละกัน
ก่อนอื่นก็ไปสร้าง Link เพื่อให้คนเข้ามากรอกฟอร์มได้ ดังนี้
แล้ว Copy Link ไปเปิดใน Tab ใหม่ แล้วลองกรอกข้อมูลดู
เสร็จแล้วแล้ว Submit ซะ
สร้าง Google Sheet ไว้บันทึกข้อมูล
จากนั้นกลับไปที่หน้าสร้างฟอร์ม แล้วกดที่ Responses จากนั้นกด icon รูป Sheet เขียวๆ ตามรูป เพื่อให้ Form บันทึกข้อมูลลง Google Sheets
จากนั้นมันจะเปิดหน้าต่าง Google Sheet ขึ้นมาโดยมีคำถามเป็นหัวตารางข้อมูล และมีข้อมูลที่เราลองกรอกลงไป
วิธีที่เอา URL มี 2 วิธี
วิธีที่ 1 (วิธีใหม่ ง่ายกว่า) : ใช้วิธี Publish to Web
*** วิธีนี้แนะนำมาโดย คุณ Bo แห่งเพจ Excel Wizard ครับ***
ให้ไปที่ File -> Publish to Web
จากนั้นเลือกรูปแบบที่ต้องการ ในที่นี้ผมเลือกเป็น csv
จากนั้นจะได้ URL มาให้ Ctrl+C เพื่อ Copy เก็บไว้เพื่อเอาไว้ใส่ใน Power Query ครับ
วิธีที่ 2 (วิธีเก่า) : เอาจาก link download
เปิด Link Sharing ก่อน
ให้กดปุ่ม Share เขียวๆ ที่มุมบนขวา แล้วเปิดให้ anyone with link can view
จากนั้นให้ Download ไฟล์ออกมาเป็น excel หรือ csv ก็ได้ ในที่นี้ผมเป็น csv ละกัน โดยให้ไปที่ File –> Download –> Comma Separate Value
แล้ว Save ไว้ที่ไหนก็ได้ (ตรงนี้ไม่สำคัญ เพราะเราไม่ได้จะ link กับไฟล์ที่ save มาหรอก)
หา URL ของไฟล์จาก Section Download
ให้ไปที่ Section Download (ถ้าเป็น chrome กด Ctrl+J ได้)
แล้วคลิ๊กขวา –> Copy Link Address ของไฟล์ที่เพิ่งโหลดมาซะ สิ่งที่ Copy มานี่แหละ จะเป็น URL ที่จะเอามาใช้ใน Power Query ครับ
Step หลัก 2 : เอาข้อมูลเข้า Power Query
เปิด Power Query ซะ แล้ว Get Data จาก Web จากนั้นใส่ URL ที่ Copy ไว้จาก Step ที่แล้วลงไป (ไม่ว่าจะวิธีไหนก็ตาม) แล้ว ok
จากนั้นมันจะ preview ผลลัพธ์มาให้ ถ้าอ่านไม่ออกให้เปลี่ยน Encoding เป็น UTF8 นะ
จากนั้นกด Transform เพื่อจัดการข้อมูลที่อาจจะผิด เช่นวันที่
อย่างของผมเนี่ย มัน convert วันที่ผิดปี ถ้า control panel ผม set Region เป็น Thai วันที่ที่ถูกต้องตอน Preview จะต้องเป็น พ.ศ. แต่อันนี้มันดันเป็น ค.ศ. แถมเป็น เดือน/วัน/ปี อีก…
ดังนั้นผมต้องกด Convert วันที่แบบ Using Locale ใหม่ แต่ก่อนจะทำ ผมต้องเปลี่ยน Format วันที่กลับเป็น Text ก่อน แล้วกด Replace Current
จากนั้นค่อย กด Convert วันที่แบบ Using Locale ใหม่
จากนั้นเลือก Date/Time รูปแบบเป็น English (United States) เพราะ Format มาเป็น เดือน/วัน/ปี ค.ศ.
พอแก้ Locale เสร็จ ผลลัพธ์เป็นแบบนี้ถึงจะถูกต้องครับ (กรณีใน Control Panel เป็น Thai จะต้องเห็นเป็น พ.ศ.)
ที่นี้ก็กด Home –> Close & Load เพื่อให้ออกไปเป็น Table ดู
จะเห็นว่าผลลัพธ์ใช้ได้เลยล่ะ
กลับไปกรอกฟอร์มเพิ่ม แล้วกลับมา Refresh
ลองไปกรอกเพิ่มดูอีกซักรายการแล้วกด Submit ฟอร์ม
จากนั้นกลับมาที่ตารางผลลัพธ์ใน Excel แล้วคลิ๊กขวา Refresh (โดยไม่ต้องไป Download หรือ Copy Link อะไรอีกแล้ว)
จะเห็นว่าผลลัพธ์ถูกดึงมาใน Excel อย่างง่ายดายเลย!!
นี่แหละครับ ความสุดยอดของ Power Query ซึ่งใช้ได้ทั้งใน Excel และ Power BI เลยนะ ใครอยากลองกรอกฟอร์มของผมเล่นดู ก็ไปกรอกได้ที่ https://forms.gle/2wAbkYeJdP6oHwEHA นะครับ
ใครมีคำถามหรือติดอะไรตรงไหนก็สามารถ Comment ไว้ได้นะครับ ส่วนคนที่อยากเรียนรู้เรื่อง Power Query เพิ่มเติม สามารถอ่านได้ที่ Category Power Query
credit knowledge :
ผมศึกษาวิธีเก่ามาจากเว็บนี้ครับ https://www.excelinppc.com/using-google-sheets-as-data-source-in-power-query/
ส่วนวิธี Publish to Web จาก Google Sheets คุณ Bo แห่งเพจ Excel Wizard แนะนำมาครับ
