Google Sheets

การอ่านหรือเขียนข้อมูลในสเปรดชีต Google Sheets

การเข้าถึงสเปรดชีต

ก่อนที่คุณจะใช้ Node นี้ คุณต้องให้สิทธิ์ Automation ในการเข้าถึงสเปรดชีตก่อน โดยสิทธิ์ในการเข้าถึงของชีตจะแบ่งออกเป็น 2 ประเภทด้วยกัน

การเข้าถึงชีตรูปแบบ Public

เป็นการเข้าถึงสเปรดชีตแบบสาธารณะ สามารถเลือกได้โดยการตั้งค่าชีต และเปลี่ยนสิทธิ์การเข้าถึงให้เป็น “ทุกคนที่มีลิงก์”

การเข้าถึงชีตรูปแบบ Private

เป็นการเข้าถึงสเปรดชีตในรูปแบบส่วนตัว โดยคุณสามารถตั้งค่าการเชื่อมต่อ Google Sheets API ด้วย Service Account ดังนี้

การเชื่อมต่อ Google Sheets API ด้วย Service Account

ก่อนที่จะสามารถเข้าถึง สเปรดชีตส่วนตัวได้ คุณต้องทำการเชื่อมต่อกับ Google Sheets API ด้วย Service Account ก่อน

สิ่งที่ต้องมี:

1. Google Cloud Account: สามารถสมัครหรือล็อกอินเข้าใช้งานได้ที่ Google Cloud Console

2. Google Sheets Document: เข้าไปยังชีตของท่าน หรือสร้างชีตใหม่ ที่ Google Sheets

3. Service Account: เราจะสอนคุณสร้างบัญชีนี้ในขั้นตอนด้านล่าง เพื่อใช้สำหรับการยืนยันตัวตน

ขั้นตอนการเชื่อมต่อ:

การให้สิทธิ์เข้าถึงกับ Service Account

1. เมื่ออยู่หน้า Grant this service account to project แล้ว ให้เลือก Role เป็น Editor (หรือ Owner สำหรับการเข้าถึงเต็มรูปแบบ)

2. กดคลิก Continue

3. คลิก Done เพื่อเสร็จสิ้นการตั้งค่า

   

รายละเอียดการตั้งค่าในบล็อค

Spreadsheet ID

สามารถระบุสเปรดชีต ID ของเอกสาร เพื่อเชื่อมต่อบล็อคเข้ากับเอกสารที่ต้องการใช้งาน

การระบุ ID ของสเปรดชีต

ID ของสเปรดชีตประกอบด้วยตัวอักษร ตัวเลข เครื่องหมายขีดกลาง หรือเครื่องหมายขีดล่าง และคุณสามารถพบได้ใน URL ของ Google Sheets:

https://docs.google.com/spreadsheets/d/1ZOVnxQDdNm2rOCMIn_a9y2-ZxUqwVAR0fcYzc6X43nY/edit#gid=0

ค่าของสเปรตชีต ID จะอยู่ระหว่าง /d/สเปรตชีต ID/edit

ซึ่ง ID ของสเปรดชีต คือ:

1ZOVnxQDdNm2rOCMIn_a9y2-ZxUqwVAR0fcYzc6X43nY

Range (ช่วง)

ช่วงของค่าที่คุณต้องการดึง อัปเดต หรือ ลบ เช่น หากใช้งานฟังค์ชั่นดึงข้อมูล สามารถระบุเป็น Sheet1!A1:A3 เพื่อรับข้อมูล 3 เซลล์

Reference key (การกำหนดคีย์อ้างอิง)

เมื่อกำหนดคีย์อ้างอิง ผลลัพธ์ที่ได้จากการดึงข้อมูล จะอยู่ในคีย์ที่กำหนด เช่น

• ตัวอย่าง การกำหนดคีย์อ้างอิง ชื่อ “names”

จะเห็นได้ว่า ผลลัพธ์ทั้งหมดจะถูกเก็บไว้ในลิสต์ของคีย์อ้างอิง

การใช้แถวแรกเป็นคีย์

เมื่อเลือกใช้แถวแรกของสเปรดชีตเป็นคีย์ ค่าที่อยู่ในคอลัมน์ที่สอดคล้องกันจะถูกระบุตามคีย์นั้น และคุณสามารถดึงค่าต่างๆ ได้โดยใช้คีย์นี้

ตัวอย่าง: เมื่อคุณมีสเปรดชีตดังนี้

// ไม่เลือกแถวแรกเป็นคีย์
[["name", "age"], ["foo", 22], ["bar", 23]]
//เลือกแถวแรกเป็นคีย์
[{ "name": "foo", "age": 22 }, { "name": "bar", "age": 23 }]

ชื่อคอลัมน์ที่ใช้เป็นคีย์หลัก

การใช้คอลัมน์เป็นคีย์หลักช่วยให้คุณสามารถดึงข้อมูลจากสเปรดชีตตามค่าที่กำหนดในคอลัมน์นั้น เช่น ในกรณีที่ข้อมูลถูกจัดกลุ่มตามโปรไฟล์ ID หรือรหัสเฉพาะ คุณสามารถใช้คอลัมน์นี้เป็นตัวระบุ (Key) เพื่อเข้าถึงข้อมูลได้ง่ายขึ้น

ตัวอย่าง: เมื่อคุณมีสเปรดชีตดังนี้

หากคุณต้องการให้เมื่อรันโปรไฟล์ที่มี ID เท่ากับ 2 2 และ 3 สามารถใช้คีย์ profileId ในการเข้าถึงข้อมูล เช่น

• สำหรับโปรไฟล์ ID 2: {{googleSheets.referenceKey.[2].name}} จะได้ค่าเป็น foo

• สำหรับโปรไฟล์ ID 3: {{googleSheets.referenceKey.[3].name}} จะได้ค่าเป็น bar

การดึงช่วงของสเปรดชีต

ดึงค่าช่วงของสเปรดชีต จากนั้นกำหนดค่านั้นให้กับตัวแปรหรือคอลัมน์ที่ต้องการ

ช่วงของสเปรดชีต

• กำหนดให้ตัวแปร: กำหนดช่วงของข้อมูลให้กับตัวแปร

• แทรกลงในตาราง: กำหนดช่วงของข้อมูลให้กับคอลัมน์

การอัปเดตค่าของเซลล์ในสเปรดชีต

เมื่ออัปเดตข้อมูลในสเปรดชีต คุณสามารถกำหนดวิธีที่ข้อมูลจะถูกแปลความหมายก่อนจัดเก็บลงในเซลล์ โดยมีตัวเลือกหลัก ๆ ดังนี้:

ตัวเลือกการป้อนค่า (Input Modes)

• RAW (ค่าดิบ):

  • ข้อมูลจะถูกเก็บตามที่ป้อนโดยไม่มีการแปลงใด ๆ

  • เหมาะสำหรับข้อมูลที่ต้องการเก็บในรูปแบบข้อความ (String)

    ตัวอย่าง: ป้อน "123" จะถูกเก็บเป็น "123" (ข้อความ)

• USER_ENTERED (ค่าที่ป้อนโดยผู้ใช้):

  • ข้อมูลจะถูกแปลความหมายเหมือนกับการป้อนใน Google Sheets โดยตรง

  • ตัวเลขและวันที่อาจถูกแปลงโดยอัตโนมัติตามรูปแบบใน Google Sheets

  • ตัวอย่าง: ป้อน "123" จะถูกเก็บเป็น 123 (ตัวเลข)

ตัวเลือกแหล่งข้อมูล (Data From)

ข้อมูลจากแหล่งข้อมูลเพื่ออัปเดตสเปรดชีต ค่าเริ่มต้นคือ ตาราง

• ตาราง (Table):

  • ดึงข้อมูลจากตารางที่มีอยู่ในสเปรดชีต

  • เหมาะสำหรับข้อมูลที่ถูกจัดเรียงเป็นแถวและคอลัมน์

• ใช้คีย์เป็นแถวแรก (Use First Row as Key):

  • ใช้คอลัมน์แรกเป็นคีย์หลักในการดึงข้อมูล

  • ข้อมูลจะถูกอ้างอิงตามคีย์ในแถวแรก

• กำหนดเอง (Custom):

  • ป้อนข้อมูลในรูปแบบ JSON array

  • ตัวอย่าง: ["1","2","3"]

ตัวเลือกการแทรกข้อมูลลงในสเปรดชีต

เมื่อเพิ่มข้อมูลลงในสเปรดชีต คุณสามารถกำหนดวิธีการแทรกข้อมูลเพื่อให้สอดคล้องกับรูปแบบที่ต้องการ มีตัวเลือกดังนี้:

วิธีการแทรกข้อมูล (Insert Modes)

• OVERWRITE (เขียนทับ):

  • เขียนทับข้อมูลเดิมในช่วงเซลล์ที่กำหนด

  • เหมาะสำหรับข้อมูลที่ต้องการอัปเดตแบบคงที่

  • ตัวอย่าง: อัปเดตข้อมูลในช่วง A2:B4 โดยเขียนทับข้อมูลที่มีอยู่

• INSERT_ROWS (แทรกแถวใหม่):

  • แทรกแถวใหม่สำหรับข้อมูลใหม่ โดยไม่เขียนทับข้อมูลเดิม

  • เหมาะสำหรับการเพิ่มข้อมูลใหม่ เช่น รายการขายหรือรายชื่อลูกค้า

แหล่งข้อมูลสำหรับการแทรก (Data From)

• ตาราง (Table):

  • ดึงข้อมูลจากตารางที่มีอยู่

  • เหมาะสำหรับข้อมูลที่มีโครงสร้างชัดเจน เช่น ข้อมูลการขายหรือข้อมูลลูกค้า

• ใช้คีย์เป็นแถวแรก (Use First Row as Key):

  • ใช้คอลัมน์แรกเป็นคีย์หลักในการดึงข้อมูล

  • ข้อมูลจะถูกอ้างอิงตามคีย์ในแถวแรก

• กำหนดเอง (Custom):

  • ป้อนข้อมูลในรูปแบบ JSON array

  • ตัวอย่าง: ["1", "2", "3"]

การเข้าถึงข้อมูลของสเปรดชีต

การเข้าถึงข้อมูลจากสเปรดชีตใน Node สามารถทำได้โดยใช้สูตร (Syntax) สำหรับอ้างอิงข้อมูลภายในแผ่นงาน โดยใช้รูปแบบ: {{ googleSheets.referenceKey.path }}

ตัวอย่างการเข้าถึงข้อมูล

ข้อมูลใน Google Sheets:

หากข้อมูลนี้ถูกเชื่อมต่อกับ Node ที่มีคีย์อ้างอิงชื่อ data คุณสามารถเข้าถึงข้อมูลแต่ละแถวได้ดังนี้:

• ดึงชื่อของแถวแรก: {{googleSheets.data.0.name}} จะได้ค่าเป็น Alice

• ดึงอายุของแถวแรก: {{googleSheets.data.0.age}} จะได้ค่าเป็น 25

• ดึงชื่อของแถวที่สอง: {{googleSheets.data.1.name}} จะได้ค่าเป็น Bob

• ดึงอายุของแถวที่สาม: {{googleSheets.data.2.age}} จะได้ค่าเป็น 35

กรณีใช้แถวแรกเป็นคีย์

หากคุณเลือกใช้แถวแรกเป็นคีย์ ค่าที่เรียกใช้อาจเปลี่ยนรูปแบบ เช่น:

• {{googleSheets.data.Alice.age}} จะได้ค่าเป็น 25

• {{googleSheets.data.Bob.age}} จะได้ค่าเป็น 30

คลิปสอนการใช้งานบล็อค Google Sheets