วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 1

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets

บทความนี้จะอธิบายวิธีการใช้ Power Query เชื่อมกับ Google Form/Google Sheets โดยที่เราสามารถกด Refresh ที่ Excel/Power BI เพื่อดูดข้อมูลจาก Google Form/Google Sheets ณ ตอนนั้นได้เลย

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

ก่อนอื่นก็ไปสร้างฟอร์มใน Google Form ซะก่อน อันนี้แล้วแต่คุณเลย แต่ผมจะลองสร้างฟอร์มใหม่ให้กรอกเล่นๆ ละกัน

Step หลัก 1 : เตรียม Google Form และหา URL

สร้างฟอร์มที่ Google Form

ผมก็ใส่คำถามไปประมาณนี้

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 2

สร้าง Link + ลองกรอกข้อมูล

เราก็จะมาลองกรอกข้อมูลมั่วๆ ลงไปซัก 2 อันละกัน
ก่อนอื่นก็ไปสร้าง Link เพื่อให้คนเข้ามากรอกฟอร์มได้ ดังนี้

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 3

แล้ว Copy Link ไปเปิดใน Tab ใหม่ แล้วลองกรอกข้อมูลดู

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 4

เสร็จแล้วแล้ว Submit ซะ

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 5

สร้าง Google Sheet ไว้บันทึกข้อมูล

จากนั้นกลับไปที่หน้าสร้างฟอร์ม แล้วกดที่ Responses จากนั้นกด icon รูป Sheet เขียวๆ ตามรูป เพื่อให้ Form บันทึกข้อมูลลง Google Sheets

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 6

จากนั้นมันจะเปิดหน้าต่าง Google Sheet ขึ้นมาโดยมีคำถามเป็นหัวตารางข้อมูล และมีข้อมูลที่เราลองกรอกลงไป

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 7

วิธีที่เอา URL มี 2 วิธี

วิธีที่ 1 (วิธีใหม่ ง่ายกว่า) : ใช้วิธี Publish to Web

*** วิธีนี้แนะนำมาโดย คุณ Bo แห่งเพจ Excel Wizard ครับ***

ให้ไปที่ File -> Publish to Web

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 8

จากนั้นเลือกรูปแบบที่ต้องการ ในที่นี้ผมเลือกเป็น csv

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 9

จากนั้นจะได้ URL มาให้ Ctrl+C เพื่อ Copy เก็บไว้เพื่อเอาไว้ใส่ใน Power Query ครับ

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 10

วิธีที่ 2 (วิธีเก่า) : เอาจาก link download

เปิด Link Sharing ก่อน

ให้กดปุ่ม Share เขียวๆ ที่มุมบนขวา แล้วเปิดให้ anyone with link can view

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 11

จากนั้นให้ Download ไฟล์ออกมาเป็น excel หรือ csv ก็ได้ ในที่นี้ผมเป็น csv ละกัน โดยให้ไปที่ File –> Download –> Comma Separate Value

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 12

แล้ว Save ไว้ที่ไหนก็ได้ (ตรงนี้ไม่สำคัญ เพราะเราไม่ได้จะ link กับไฟล์ที่ save มาหรอก)

หา URL ของไฟล์จาก Section Download

ให้ไปที่ Section Download (ถ้าเป็น chrome กด Ctrl+J ได้)

แล้วคลิ๊กขวา –> Copy Link Address ของไฟล์ที่เพิ่งโหลดมาซะ สิ่งที่ Copy มานี่แหละ จะเป็น URL ที่จะเอามาใช้ใน Power Query ครับ

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 13

Step หลัก 2 : เอาข้อมูลเข้า Power Query

เปิด Power Query ซะ แล้ว Get Data จาก Web จากนั้นใส่ URL ที่ Copy ไว้จาก Step ที่แล้วลงไป (ไม่ว่าจะวิธีไหนก็ตาม) แล้ว ok

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 14

จากนั้นมันจะ preview ผลลัพธ์มาให้ ถ้าอ่านไม่ออกให้เปลี่ยน Encoding เป็น UTF8 นะ

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 15

จากนั้นกด Transform เพื่อจัดการข้อมูลที่อาจจะผิด เช่นวันที่

อย่างของผมเนี่ย มัน convert วันที่ผิดปี ถ้า control panel ผม set Region เป็น Thai วันที่ที่ถูกต้องตอน Preview จะต้องเป็น พ.ศ. แต่อันนี้มันดันเป็น ค.ศ. แถมเป็น เดือน/วัน/ปี อีก…

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 16

ดังนั้นผมต้องกด Convert วันที่แบบ Using Locale ใหม่ แต่ก่อนจะทำ ผมต้องเปลี่ยน Format วันที่กลับเป็น Text ก่อน แล้วกด Replace Current

จากนั้นค่อย กด Convert วันที่แบบ Using Locale ใหม่

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 17

จากนั้นเลือก Date/Time รูปแบบเป็น English (United States) เพราะ Format มาเป็น เดือน/วัน/ปี ค.ศ.

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 18

พอแก้ Locale เสร็จ ผลลัพธ์เป็นแบบนี้ถึงจะถูกต้องครับ (กรณีใน Control Panel เป็น Thai จะต้องเห็นเป็น พ.ศ.)

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 19

ที่นี้ก็กด Home –> Close & Load เพื่อให้ออกไปเป็น Table ดู

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 20

จะเห็นว่าผลลัพธ์ใช้ได้เลยล่ะ

กลับไปกรอกฟอร์มเพิ่ม แล้วกลับมา Refresh

ลองไปกรอกเพิ่มดูอีกซักรายการแล้วกด Submit ฟอร์ม

จากนั้นกลับมาที่ตารางผลลัพธ์ใน Excel แล้วคลิ๊กขวา Refresh (โดยไม่ต้องไป Download หรือ Copy Link อะไรอีกแล้ว)

วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 21
วิธีใช้ Power Query ดึงข้อมูลจาก Google Form/Google Sheets 22

จะเห็นว่าผลลัพธ์ถูกดึงมาใน 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 แนะนำมาครับ

Power BI Workshop 2024 ตุลาคมนี้
อบรม In-House Training

Feedback การใช้งาน AI Chatbot