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

IMPORTRANGE ดึงข้อมูลข้าม Spreadsheet ใน Google Sheets

ในการที่เราจะนำข้อมูลจากไฟล์การตอบกลับหรือไฟล์ต้นฉบับที่ไม่ต้องการให้คนอื่นมองเห็นไฟล์ที่สำรวจมาทั้งหมดหรือมายุ่งวุ่นวายกับไฟล์ต้นฉบับบของเรา ใน sheet ของ Google Sheets ทำได้โดยใช้คำสั่ง

=IMPORTRANGE()

ซึ่งคำสั่งนี้จะช่วยทำสำเนา นำข้อมูลทั้งหมดของอีก sheet มาใช้ใน Spreadsheet ที่เราต้องการทั้งหมด แต่เมื่อเราจะแก้ไขข้อมูลให้ไปแก้ไขใน sheet ต้นฉบับของเรา

การใช้คำสั่งนี้จะต้องมีไฟล์ Spreadsheet 2 ไฟล์ เพื่อทำการสำเนาข้อมูลจากไฟล์หนึ่งไปยังอีกไฟล์หนึ่ง ซึ่งตามภาพจะสำเนาไฟล์จากไฟล์ต้นฉบับไปยังไฟล์สรุปข้อมูล

เปิดที่ไฟล์ สรุปข้อมูล แล้วเลิกที่ช่อง A1 หรือช่องที่ต้องการเริ่มต้น โดยพิมพ์คำสั่ง =IMPORTRANGE ลงไป แล้ว Google Sheets จะทำการแนะนำการใช้งานคำสั่งแบบอัตโนมัติมาให้ดู ทำให้ง่ายและสะดวกต่อการใช้งานมากขึ้น

เมื่อพิมพ์ชุดคำสั่งทั้งหมดแล้ว ดังตัวอย่างข้างล่าง

=IMPORTRANGE(spreadsheet_url หรือ รหัสของ Sheet, สตริงช่วง)

=IMPORTRANGE("1m3J5cLZsGfs2AI1Y90_AFaYA15DtnlQtzULw1TH0s0E","data!A:E")

ให้ไปนำรหัสของไฟล์งานต้นฉบับที่จะดึงข้อมูลมาใช้งาน โดยไปคัดลอกรหัสที่ URL ของเว็บไซต์

นำมาใส่ใน หมายเลข 1

หมายเลข 2 คือ ชื่อของ Spreadsheet ที่ตั้งค่าไว้

หมายเลข 3 คือ ช่วงของข้อมูลตาม Column ที่เราต้องการ

เมื่อพิมพ์คำสั่งทั้งหมดแล้ว Google Sheets จะให้เราเปิดการอนุญาตการเข้าถึงข้อมูล เพื่อที่จะนำข้อมูลที่ต้องการมาแสดงผลได้

หลังจากที่ได้รับการอนุญาตให้เข้าถึงข้อมูลแล้ว ข้อมูลที่เลือกไว้ก็จะแสดงผลตามที่เราต้องการ

Google Sheets Importrange Spreadsheet ดึงข้อมูล

ในบทความนี้จะแนะนำการเรียกข้อมูลจาก Google Sheet มาแสดงบนเว็บของเราอย่างง่ายๆ ด้วยภาษา PHP.

1. สร้างข้อมูลตัวอย่างใน Google Sheet ก่อนครับ.

• อย่าลืมตั้งชื่อไฟล์ด้วยนะครับ.

• จากนั้นให้ทำการกดแชร์เพื่อรับลิงก์การแชร์ไฟล์.

• จากนั้นให้นำลิงก์ที่คัดลอกมาไปที่เว็บ //sheet.best/ เพื่อแปลงเป็น API

ขั้นตอนที่ 1.ขั้นตอนที่ 2.

• ให้นำลิงก์ที่คัดลอกมา นำมาใส่ในช่อง Connection URL.
• อย่าลืมตั้งชื่อ Connection Name ด้วยนะครับ จากนั้นกด Connect.

ขั้นตอนที่ 3.ขั้นตอนที่ 4.

• จากนั้นให้นำลิงก์ในช่อง Connection URL ไปใช้ในการดึงข้อมูลได้เลยครับ.

ขั้นตอนที่ 5.

2. เรามาลองดึงข้อมูลผ่าน API ด้วยภาษา PHP กันครับ.

• ผมจะสร้างไฟล์ api.php ขึ้นมาครับ.

ภาพผลลัพธ์

• ฝากไลค์ฝากแชร์ด้วยนะครับ ขอบคุณครับผม.

Posted 28 September 2019. 2 min read.

หาก Google Sheets ของคุณมีข้อมูลแยกกันอยู่หลายไฟล์ เราสามารถเรียกข้อมูลจาก Spreadsheet ไฟล์นึงไปยังอีกไฟล์นึงได้ด้วย =IMPORTRANGE()

=IMPORTRANGE()

คือคำสั่งเดียวที่จะทำให้คุณสามารถเรียกเอาไฟล์จาก Google Sheets ของใครก็ได้(ถ้าเขาอนุญาต) มาใช้งานบน Spreadsheet ของคุณ โดยหน้าตาของคำสั่งเต็มๆก็จะเป็นอย่างนี้ครับ

=IMPORTRANGE("SPREADSHEET URL", "Range ของข้อมูลที่อยากได้ เช่น Sheet1!A1:C10")

จบแล้วครับ

ตัวอย่าง(กลัวไม่เห็นภาพ)

สมมติว่าผมมี Spreadsheet ชื่อ "ลูกค้า" เอาไว้เก็บลูกค้าแยกแบบนี้นะครับ

แล้วผมก็มีอีก Spreadsheet นึงชื่อ "รายการขาย" เป็นคนละไฟล์กัน แต่ผมอยากเอารายชื่อลูกค้ามาแปะไว้ที่นี่ด้วย แต่ผมไม่อยาก copy & paste รายชื่อลูกค้าใหม่ทุกครั้งที่มีการแก้ไข ผมจึงเลือกใช้ =IMPORTRANGE() เพื่อความสะดวก ใช้แค่คำสั่งเดียวจะแก้กี่ครั้งข้อมูลก็ update ตามเอง ว่าแล้วผมก็พิมพ์สูตร

=IMPORTRANGE( "//docs.google.com/spreadsheets/d/1A0J9ofsjn2ThsAENGH4HgR_CB_xTBkSradYhlUOTEp4", "Sheet1!A1:B" )

• URL ที่ผมใส่ลงไปนั้นก็คือ URL ของ Spreadsheet ที่ชื่อว่า "ลูกค้า" นั่นเองครับ
• ส่วน Sheet1!A1:B ก็คือ range ที่ผมต้องการเอามาแสดงครับ Sheet1 เอา column A ถึง B มาทุก row เลย

Google Sheets จะมีหน้าต่างเล็กๆมาบอกเราว่าต้องอนุญาตการเข้าถึงก่อนนะ ก็ไม่มีอะไรครับถ้าเราสร้างเองทั้งหมดอยู่แล้วก็ Allow access ไปเลย ถ้าเป็นของคนอื่นก็ต้องรอเจ้าของเขาอนุญาตก่อน พอเรียบร้อยแล้วก็จะได้ข้อมูลมาปรากฏตรงหน้าแบบนี้เลยครับ ง่ายๆแค่นี้แหละ (ที่เห็นมาแค่ 2 column ไม่ได้ผิดนะครับ อย่าลืมนะว่าผมขอมาแค่ A กับ B)

ไหนลองกลับมาสร้าง Named range ที่ spreadsheet "ลูกค้า" ดูบ้างซิ ไม่อยากพิมพ์ Sheet1!A1:D มันยาว ก็ไปที่เมนู Data > Named ranges

ตั้งชื่อเป็น Customer แทนแล้วกันนะครับ

กลับมาที่ spreadsheet "รายการขาย" บ้าง คราวนี้ผมจะเปลี่ยน range ท้ายสูตรจาก Sheet1!A1:B เป็น Customer แทนบ้าง ผลที่ออกมาก็ตรงตามคาดครับ มาครบตั้งแต่ column A ถึง D เลย แปลว่าเราสามารถใช้ Named range กับสูตรนี้ได้ด้วยนะ

แล้วถ้าเกิดเอามาใช้กับ =QUERY() จะเป็นยังไง ผมก็สร้าง sheet ใหม่แล้วพิมพ์สูตรลงไปเลย

=QUERY( IMPORTRANGE("//docs.google.com/spreadsheets/d/1A0J9ofsjn2ThsAENGH4HgR_CB_xTBkSradYhlUOTEp4","Customer"), "where Col1 = 'C0001'" )

สังเกตนะครับว่า เงื่อนไขใน =QUERY() ของผมวันนี้ดูแปลกไป ไม่เหมือนเมื่อก่อนเวลาเราเรียก column เราจะใช้ตัวอักษร A, B, C, ... ใช่ไหมครับ แต่เมื่อไหร่ก็ตามที่เราเรียกข้อมูลมาตาม =IMPORTRANGE() เราต้องเปลี่ยนเป็น Col1, Col2, Col3, ... แทนนะครับ ในคำสั่งนี้ผมบอกว่า "จงดึงเอาข้อมูลทุก column มาถ้า Col1 มีค่าเท่ากับ C0001" ถ้าย้อนกลับไปดู A หรือ Col1 นั่นก็คือ "หมายเลข" ของรายชื่อลูกค้านั่นเองครับ

ถึงผลลัพธ์จะสวยงามแต่สูตรมันช่างยืดยาวน่าเกลียดน่ากลัวเหลือเกิน เพราะ URL กินที่ไปหมด เพื่อความสวยงามเราสามารถสร้าง sheet ใหม่ขึ้นมาเก็บ URL ของแต่ละ spreadsheet ที่เราจะดึงข้อมูลมาก็ได้นะครับ

เพื่อให้สั้นลงไปอีกผมก็สร้าง Named range ของ URL ด้วยเลย ในภาพจะเห็นว่าผมสร้าง CustomerUrl กับ PartnerUrl

ลองกลับมาแก้สูตรที่ยาวน่าเกลียดน่ากลัวให้สั้นลงด้วย Named range ดูสิครับ ดูสะอาดตาคือเยอะเลย

ของเก่า

=QUERY( IMPORTRANGE("//docs.google.com/spreadsheets/d/1A0J9ofsjn2ThsAENGH4HgR_CB_xTBkSradYhlUOTEp4","Customer"), "where Col1 = 'C001'" )

แก้ใหม่ให้สั้นลง

=QUERY( IMPORTRANGE(CustomerUrl,"Customer"), "where Col1 = 'C001'" )

นอกจาก =QUERY() แล้วเรายังสามารถใช้ได้กับทุกสูตรที่ต้องการรับ range นะครับ เช่น =VLOOKUP(), =HLOOKUP(), =FILTER(), =MATCH() และอีกหลายๆตัวเลย ต่อไปนี้เราไม่จำเป็นต้องรวมทุกอย่างใน Spreadsheet เดียวให้รู้สึกเทอะทะแล้วนะครับ การแยกข้อมูลเป็นหลายๆส่วนอาจจะช่วยให้การจัดการง่ายขึ้นได้นะครับ ลองนึกภาพ Spreadsheet ที่มีอยู่ 50-60 sheets คงต้องเลื่อนหากันตาลายแน่นอนครับ

My thoughts about People, Programming, Gadget, Photography and Everything that can be conceptualized