ดึงข้อมูลจาก google sheet มาแสดง html

บทความนี้จะอธิบายวิธีการใช้ 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/