พื้นฐานของ Apps Script กับ Google ชีต #2: สเปรดชีต ชีต และช่วง

1. บทนำ

ยินดีต้อนรับสู่ส่วนที่ 2 ของเพลย์ลิสต์ Codelab พื้นฐานของ Apps Script กับ Google ชีต Codelab ก่อนหน้านี้มุ่งเน้นที่แนวคิดของเครื่องมือแก้ไขสคริปต์ มาโคร และฟังก์ชันที่กำหนดเอง โค้ดแล็บนี้จะเจาะลึกบริการสเปรดชีตที่คุณใช้เพื่ออ่าน เขียน และจัดการข้อมูลใน Google ชีตได้

สิ่งที่คุณจะได้เรียนรู้

  • วิธีแสดงสเปรดชีต ชีต และช่วงใน Apps Script
  • วิธีเข้าถึง สร้าง และเปลี่ยนชื่อสเปรดชีตที่ใช้งานอยู่ (เปิดอยู่) ด้วยคลาส SpreadsheetApp และ Spreadsheet
  • วิธีเปลี่ยนชื่อชีตและแนวคอลัมน์/แถวของช่วงโดยใช้คลาส Sheet
  • วิธีระบุ เปิดใช้งาน ย้าย และจัดเรียงกลุ่มเซลล์หรือช่วงข้อมูลโดยใช้คลาส Range

ก่อนเริ่มต้น

นี่คือ Codelab ที่ 2 ในเพลย์ลิสต์พื้นฐานของ Apps Script กับ Google ชีต ก่อนเริ่มต้น โปรดทำ Codelab แรกให้เสร็จก่อน นั่นคือ มาโครและฟังก์ชันที่กำหนดเอง

สิ่งที่คุณต้องมี

  • ความเข้าใจในหัวข้อพื้นฐานของ Apps Script ที่ได้เรียนรู้ใน Codelab ก่อนหน้าของเพลย์ลิสต์นี้
  • คุ้นเคยกับเครื่องมือแก้ไข Apps Script ในระดับพื้นฐาน
  • คุ้นเคยกับ Google ชีต ในระดับพื้นฐาน
  • ความสามารถในการอ่านรูปแบบ A1 ของชีต
  • มีความคุ้นเคยกับ JavaScript และคลาส String ในระดับพื้นฐาน

ส่วนถัดไปจะแนะนำคลาสหลักของบริการสเปรดชีต

2. ข้อมูลเบื้องต้นเกี่ยวกับบริการสเปรดชีต

คลาส 4 คลาสเป็นรากฐานของบริการสเปรดชีต ได้แก่ SpreadsheetApp, Spreadsheet, Sheet และ Range ส่วนนี้จะอธิบายคลาสเหล่านี้และสิ่งที่ใช้

คลาส SpreadsheetApp

ก่อนที่จะเจาะลึกเกี่ยวกับสเปรดชีต ชีต และช่วง คุณควรตรวจสอบคลาสหลักของรายการดังกล่าว ซึ่งก็คือ SpreadsheetApp สคริปต์จำนวนมากเริ่มต้นด้วยการเรียกใช้SpreadsheetAppเมธอด เนื่องจากเมธอดเหล่านี้เป็นจุดเริ่มต้นในการเข้าถึงไฟล์ Google ชีต คุณสามารถคิดว่า SpreadsheetApp เป็นคลาสหลักของบริการสเปรดชีต เราจะไม่เจาะลึกถึงคลาส SpreadsheetApp ในที่นี้ อย่างไรก็ตาม คุณจะเห็นทั้งตัวอย่างและแบบฝึกหัดที่จะช่วยให้เข้าใจคลาสนี้ได้ใน Codelab นี้

สเปรดชีต ชีต และคลาสของชีต

ในแง่ของชีต สเปรดชีตคือไฟล์ Google ชีต (จัดเก็บไว้ใน Google ไดรฟ์) ที่มีข้อมูลซึ่งจัดระเบียบตามแถวและคอลัมน์ บางครั้งสเปรดชีตก็เรียกว่า "Google ชีต" เช่นเดียวกับที่เอกสารเรียกว่า "Google เอกสาร"

คุณใช้คลาส Spreadsheet เพื่อเข้าถึงและแก้ไขข้อมูลไฟล์ Google ชีตได้ นอกจากนี้ คุณยังใช้คลาสนี้สำหรับการดำเนินการระดับไฟล์อื่นๆ ได้ด้วย เช่น การเพิ่มผู้ทำงานร่วมกัน

f00cc1a9eb606f77.png

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

39dbb10f83e3082.png

โดยสรุปแล้ว คลาส Spreadsheet จะทำงานกับคอลเล็กชันของชีตและกำหนดไฟล์ Google ชีตใน Google ไดรฟ์ คลาส Sheet จะทำงานในชีตแต่ละรายการภายในสเปรดชีต

คลาส Range

การดำเนินการจัดการข้อมูลส่วนใหญ่ (เช่น การอ่าน การเขียน หรือการจัดรูปแบบข้อมูลในเซลล์) กำหนดให้คุณต้องกำหนดเซลล์ที่จะใช้การดำเนินการ คุณใช้คลาส Range เพื่อเลือกชุดเซลล์ที่เฉพาะเจาะจงภายในชีตได้ อินสแตนซ์ของคลาสนี้แสดงถึงช่วง ซึ่งเป็นกลุ่มของเซลล์ที่อยู่ติดกันอย่างน้อย 1 เซลล์ในชีต คุณระบุช่วงได้โดยใช้หมายเลขแถวและคอลัมน์ หรือใช้รูปแบบ A1

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

3. ตั้งค่า

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

5376f721894b10d9.png

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

โปรดทราบว่าคุณสามารถเปิดโปรแกรมแก้ไขสคริปต์จาก Google ชีตได้โดยคลิกส่วนขยาย> Apps Script

เมื่อเปิดโปรเจ็กต์ Apps Script ในเครื่องมือแก้ไขสคริปต์เป็นครั้งแรก เครื่องมือแก้ไขสคริปต์จะสร้างทั้งโปรเจ็กต์สคริปต์และไฟล์สคริปต์ให้คุณ

ส่วนถัดไปจะแสดงวิธีใช้คลาส Spreadsheet เพื่อปรับปรุงสเปรดชีตนี้

4. เข้าถึงและแก้ไขสเปรดชีต

ในส่วนนี้ คุณจะได้เรียนรู้วิธีใช้คลาส SpreadsheetApp และ Spreadsheet เพื่อเข้าถึงและแก้ไขสเปรดชีต โดยเฉพาะอย่างยิ่ง แบบฝึกหัดจะสอนวิธีเปลี่ยนชื่อสเปรดชีตและทำซ้ำแผ่นงานภายในสเปรดชีต

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

เปลี่ยนชื่อสเปรดชีตที่ใช้งานอยู่

สมมติว่าคุณต้องการเปลี่ยนชื่อเริ่มต้น "สำเนาของสเปรดชีตที่ไม่มีชื่อ" เป็นชื่อที่สื่อถึงวัตถุประสงค์ของสเปรดชีตได้ดีขึ้น โดยใช้ชั้นเรียน SpreadsheetApp และ Spreadsheet

  1. ในตัวแก้ไขสคริปต์ ให้แทนที่myFunction()บล็อกโค้ดเริ่มต้นด้วยโค้ดต่อไปนี้
function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}
  1. หากต้องการบันทึกสคริปต์ ให้คลิกบันทึก เซฟ
  2. หากต้องการเปลี่ยนชื่อโปรเจ็กต์ Apps Script ให้คลิกโปรเจ็กต์ที่ไม่มีชื่อ ป้อน "ราคาอะโวคาโด" เป็นชื่อโปรเจ็กต์ใหม่ แล้วคลิกเปลี่ยนชื่อ
  3. หากต้องการเรียกใช้สคริปต์ ให้เลือก renameSpreadsheet จากรายการฟังก์ชัน แล้วคลิกเรียกใช้
  4. ให้สิทธิ์มาโครโดยทำตามวิธีการบนหน้าจอ หากได้รับข้อความ "แอปนี้ไม่ได้รับการยืนยัน" ให้คลิกขั้นสูง แล้วคลิกไปที่ราคาอะโวคาโด (ไม่ปลอดภัย) ในหน้าจอถัดไป ให้คลิกอนุญาต

เมื่อฟังก์ชันทำงานแล้ว ชื่อไฟล์ของสเปรดชีตควรเปลี่ยนเป็นดังนี้

226c7bc3c2fbf33e.png

มาดูรหัสที่คุณป้อนกัน เมธอด getActiveSpreadsheet() จะแสดงผลออบเจ็กต์ที่แสดงถึงสเปรดชีตที่ใช้งานอยู่ ซึ่งก็คือสำเนาของสเปรดชีตแบบฝึกหัดที่คุณสร้างขึ้น ระบบจะจัดเก็บออบเจ็กต์สเปรดชีตนี้ไว้ในตัวแปร mySS การเรียกใช้ rename(newName) ใน mySS จะเปลี่ยนชื่อไฟล์สเปรดชีตใน Google ไดรฟ์เป็น "2017 Avocado Prices in Portland, Seattle"

เนื่องจากตัวแปร mySS เป็นการอ้างอิงถึงสเปรดชีต คุณจึงทำให้โค้ดสะอาดและมีประสิทธิภาพมากขึ้นได้โดยการเรียกใช้เมธอด Spreadsheet ใน mySS แทนการเรียกใช้ getActiveSpreadsheet() ซ้ำๆ

ทำซ้ำชีตที่ใช้งานอยู่

ในสเปรดชีตปัจจุบัน คุณมีชีตเพียงชีตเดียว คุณสามารถเรียกใช้เมธอด Spreadsheet.duplicateActiveSheet() เพื่อทำสำเนาชีตได้โดยทำดังนี้

  1. เพิ่มฟังก์ชันใหม่ต่อไปนี้ใต้ฟังก์ชัน renameSpreadsheet() ที่มีอยู่ในโปรเจ็กต์สคริปต์อยู่แล้ว
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}
  1. บันทึกโปรเจ็กต์สคริปต์
  2. หากต้องการเรียกใช้สคริปต์ ให้เลือก duplicateAndOrganizeActiveSheet จากรายการฟังก์ชัน แล้วคลิกเรียกใช้

กลับไปที่ชีตเพื่อดูว่าระบบได้เพิ่มแท็บแผ่นงาน "สำเนาของชีต_ต้นฉบับ" ใหม่ลงในสเปรดชีตแล้ว

d24f9f4ae20bf7d4.gif

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

ในส่วนถัดไป คุณจะใช้คลาส Sheet เพื่อเปลี่ยนชื่อและจัดรูปแบบชีตที่ซ้ำกัน

5. จัดรูปแบบชีตด้วยคลาสชีต

คลาส Sheet มีเมธอดที่ช่วยให้สคริปต์อ่านและอัปเดตชีตได้ ในส่วนนี้ คุณจะได้เรียนรู้วิธีเปลี่ยนชื่อชีตและความกว้างของคอลัมน์ด้วยเมธอดจากคลาส Sheet

เปลี่ยนชื่อชีต

การเปลี่ยนชื่อแผ่นงานทำได้ง่ายเหมือนกับการเปลี่ยนชื่อสเปรดชีตใน renameSpreadsheet() คุณต้องเรียกใช้เมธอดเพียงครั้งเดียว

  1. ใน Google ชีต ให้คลิกชีต Sheet_Original เพื่อเปิดใช้งาน
  2. ใน Apps Script ให้แก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ให้ตรงกับฟังก์ชันต่อไปนี้
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}
  1. บันทึกและเรียกใช้ฟังก์ชัน

ใน Google ชีต ระบบจะสร้างและเปลี่ยนชื่อชีตที่ซ้ำกันเมื่อคุณเรียกใช้ฟังก์ชัน

91295f42354f62e7.gif

ในโค้ดที่เพิ่มเข้ามา เมธอด setName(name) จะเปลี่ยนชื่อของ duplicateSheet โดยใช้ getSheetID() เพื่อรับหมายเลขรหัสที่ไม่ซ้ำกันของชีต โอเปอเรเตอร์ + จะต่อรหัสชีตไว้ท้ายสตริง "Sheet_"

แก้ไขคอลัมน์และแถวของชีต

นอกจากนี้ คุณยังใช้คลาส Sheet เพื่อจัดรูปแบบชีตได้ด้วย เช่น เราสามารถอัปเดตduplicateAndOrganizeActiveSheet()ฟังก์ชันเพื่อปรับขนาดคอลัมน์ของชีตที่ซ้ำกันและเพิ่มแถวที่ตรึงได้ด้วย

  1. ใน Google ชีต ให้คลิกชีต Sheet_Original เพื่อเปิดใช้งาน
  2. ใน Apps Script ให้แก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ให้ตรงกับฟังก์ชันต่อไปนี้
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}
  1. บันทึกและเรียกใช้ฟังก์ชัน

ใน Google ชีต ระบบจะสร้าง ทำการเปลี่ยนชื่อ เปิดใช้งาน และจัดรูปแบบชีตที่ซ้ำกันดังนี้

2e57c917ab157dad.gif

โค้ดที่คุณเพิ่มใช้ autoResizeColumns(startColumn, numColumns) เพื่อปรับขนาดคอลัมน์ของชีตให้อ่านง่าย เมธอด setFrozenRows(rows) จะตรึงจำนวนแถวที่ระบุ (ในกรณีนี้คือ 2 แถว) ซึ่งจะทำให้แถวส่วนหัวยังคงปรากฏอยู่เมื่อผู้อ่านเลื่อนลงในสเปรดชีต

ในส่วนถัดไป คุณจะได้เรียนรู้เกี่ยวกับช่วงและการจัดการข้อมูลพื้นฐาน

6. จัดเรียงข้อมูลใหม่ด้วยคลาส Range

คลาส Range และเมธอดของคลาสนี้มีตัวเลือกการจัดการและการจัดรูปแบบข้อมูลส่วนใหญ่ใน Spreadsheet service

ส่วนนี้จะแนะนําการจัดการข้อมูลขั้นพื้นฐานด้วยช่วง แบบฝึกหัดเหล่านี้มุ่งเน้นวิธีใช้ช่วงใน Apps Script ขณะที่ Codelab อื่นๆ ในเพลย์ลิสต์นี้จะเจาะลึกเรื่องการจัดการและการจัดรูปแบบข้อมูล

ย้ายช่วง

คุณสามารถเปิดใช้งานและย้ายช่วงข้อมูลด้วยเมธอดของคลาสและรูปแบบ A1 ซึ่งเป็นรูปแบบย่อสำหรับการระบุชุดเซลล์ที่เฉพาะเจาะจงภายในสเปรดชีต หากต้องการทบทวนอีกครั้ง คุณสามารถดูคำอธิบายสัญกรณ์ A1 นี้ได้

มาอัปเดตduplicateAndOrganizeActiveSheet()เพื่อย้ายข้อมูลบางอย่างกัน

  1. ใน Google ชีต ให้คลิกชีต Sheet_Original เพื่อเปิดใช้งาน
  2. ใน Apps Script ให้แก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ให้ตรงกับฟังก์ชันต่อไปนี้
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}
  1. บันทึกและเรียกใช้ฟังก์ชัน

เมื่อเรียกใช้ฟังก์ชันนี้ ระบบจะสร้าง เปิดใช้งาน และจัดรูปแบบชีตที่ซ้ำกัน และระบบจะย้ายเนื้อหาของคอลัมน์ F ไปยังคอลัมน์ C

10ea483aec52457e.gif

โค้ดใหม่ใช้วิธี getRange(a1Notation) เพื่อระบุช่วงของข้อมูลที่จะย้าย การป้อนรูปแบบ A1 "F2:F" เป็นพารามิเตอร์ของเมธอดจะระบุคอลัมน์ F (ยกเว้น F1) หากมีช่วงที่ระบุ เมธอด getRange(a1Notation) จะแสดงผลอินสแตนซ์ Range ของช่วงนั้น โค้ดจะจัดเก็บอินสแตนซ์ในตัวแปร myRange เพื่อให้ใช้งานได้ง่าย

เมื่อระบุช่วงแล้ว เมธอด moveTo(target) จะนำเนื้อหาของ myRange (ทั้งค่าและการจัดรูปแบบ) ไปยังตำแหน่งใหม่ ระบบจะระบุปลายทาง (คอลัมน์ C) โดยใช้รูปแบบ A1 "C2" ซึ่งเป็นเซลล์เดียว ไม่ใช่คอลัมน์ เมื่อย้ายข้อมูล คุณไม่จำเป็นต้องจับคู่ขนาดกับช่วงเป้าหมายและปลายทาง Apps Script จะจัดแนวเซลล์แรกของแต่ละรายการ

จัดเรียงช่วง

คลาส Range ช่วยให้คุณอ่าน อัปเดต และจัดระเบียบกลุ่มเซลล์ได้ เช่น คุณจัดเรียงช่วงข้อมูลได้โดยใช้วิธี Range.sort(sortSpecObj) ดังนี้

  1. ใน Google ชีต ให้คลิกชีต Sheet_Original เพื่อเปิดใช้งาน
  2. ใน Apps Script ให้แก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ให้ตรงกับฟังก์ชันต่อไปนี้
function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}
  1. บันทึกและเรียกใช้ฟังก์ชัน

ตอนนี้ฟังก์ชันจะจัดเรียงข้อมูลทั้งหมดในตารางโดยใช้ข้อมูลราคาในคอลัมน์ C นอกเหนือจากการจัดรูปแบบก่อนหน้านี้

a6cc9710245fae8d.png

โค้ดใหม่ใช้ getRange(a1Notation) เพื่อระบุช่วงใหม่ที่ครอบคลุม A3:D55 (ทั้งตารางยกเว้นส่วนหัวของคอลัมน์) จากนั้นโค้ดจะเรียกใช้เมธอด sort(sortSpecObj) เพื่อจัดเรียงตาราง ในที่นี้ พารามิเตอร์ sortSpecObj คือหมายเลขคอลัมน์ที่จะใช้จัดเรียง เมธอดจะจัดเรียงช่วงเพื่อให้ค่าคอลัมน์ที่ระบุเรียงจากต่ำสุดไปสูงสุด (ค่าที่เพิ่มขึ้น) เมธอด sort(sortSpecObj) สามารถดำเนินการตามข้อกำหนดการจัดเรียงที่ซับซ้อนกว่าได้ แต่คุณไม่จำเป็นต้องใช้ในที่นี้ คุณดูวิธีต่างๆ ในการเรียกช่วงการจัดเรียงได้ในเอกสารประกอบอ้างอิงเมธอด

ยินดีด้วย คุณทำแบบฝึกหัดทั้งหมดในโค้ดแล็บเสร็จเรียบร้อยแล้ว ส่วนถัดไปจะทบทวนประเด็นสำคัญของ Codelab นี้และดูตัวอย่าง Codelab ถัดไปในเพลย์ลิสต์นี้

7. บทสรุป

คุณมาถึงตอนจบของ Codelab นี้แล้ว ตอนนี้คุณใช้และกำหนดคลาสและคำศัพท์ที่จำเป็นของบริการสเปรดชีตใน Apps Script ได้แล้ว

คุณพร้อมที่จะไปยัง Codelab ถัดไปแล้ว

คุณคิดว่า Codelab นี้มีประโยชน์ไหม

ใช่ ไม่

สิ่งที่เราได้พูดถึงไปแล้ว

  • วิธีแสดงสเปรดชีต ชีต และช่วงใน Apps Script
  • การใช้งานพื้นฐานบางอย่างของคลาส SpreadsheetApp, Spreadsheet, Sheet และ Range

ขั้นตอนถัดไป

Codelab ถัดไปในเพลย์ลิสต์นี้จะอธิบายรายละเอียดเพิ่มเติมเกี่ยวกับวิธีอ่าน เขียน และแก้ไขข้อมูลภายในสเปรดชีต

ดู Codelab ถัดไปได้ที่การทำงานกับข้อมูล