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

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

เกี่ยวกับ Codelab นี้

subjectอัปเดตล่าสุดเมื่อ เม.ย. 4, 2022
account_circleเขียนโดย Google Workspace Developer Relations

1 บทนำ

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

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

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

ข้อควรทราบก่อนที่จะเริ่มต้น

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

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

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

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

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

4 ชั้นเรียนครอบคลุมรากฐานของบริการสเปรดชีต ได้แก่ SpreadsheetApp, Spreadsheet, Sheet และ Range ส่วนนี้จะอธิบายถึงชั้นเรียนเหล่านี้และวัตถุประสงค์ของชั้นเรียน

คลาสของ spreadsheetApp

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

สเปรดชีต ชีต และชั้นเรียน

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

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

f00cc1a9eb606f77.png

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

39dbb10f83e3082.png

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

คลาสของ Range

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

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

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 ให้คลิกโปรเจ็กต์ที่ไม่มีชื่อ ป้อน "Avocado price" เป็นชื่อโปรเจ็กต์ใหม่และคลิกเปลี่ยนชื่อ
  3. หากต้องการเรียกใช้สคริปต์ ให้เลือก renameSpreadsheet จากรายการฟังก์ชัน แล้วคลิกเรียกใช้
  4. ให้สิทธิ์มาโครโดยทําตามวิธีการบนหน้าจอ หากคุณได้รับ "แอปนี้ไม่ได้รับการยืนยัน &" คลิกขั้นสูง แล้วคลิกไปที่ราคา Avocado (ไม่ปลอดภัย) คลิกอนุญาตในหน้าจอถัดไป

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

ไฟล์ 226c7bc3c2fbf33e.png

มาดูรหัสที่คุณป้อนกัน เมธอด getActiveSpreadsheet() จะแสดงออบเจ็กต์ที่แสดงสเปรดชีตที่ใช้งานอยู่ ซึ่งก็คือสําเนาของสเปรดชีตการออกกําลังกายที่คุณสร้าง ระบบจะจัดเก็บออบเจ็กต์สเปรดชีตนี้ไว้ในตัวแปร mySS การโทรหา rename(newName) ในวันที่ mySS จะเปลี่ยนชื่อไฟล์สเปรดชีตใน Google ไดรฟ์เป็น "2017 Avocado Price ในพอร์ตแลนด์ รัฐซีแอตเทิล&&quot

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

ทําสําเนาแผ่นงานที่ใช้งานอยู่

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

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

กลับไปที่ชีตเพื่อดูแท็บ "Copy of Sheet_Original" แท็บใหม่ในชีต

D24f9f4ae20bf7d4.gif

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

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

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

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

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

คุณจะเปลี่ยนชื่อชีตได้ง่ายๆ เหมือนเปลี่ยนชื่อสเปรดชีตใน renameSpreadsheet() ต้องใช้การเรียกวิธีการเดียวเท่านั้น

  1. ใน Google ชีต ให้คลิก Sheet_Original ชีตเพื่อเปิดใช้งาน
  2. โปรดแก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ใน Apps Script ให้ตรงกับรายการต่อไปนี้
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. โปรดแก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ใน Apps Script ให้ตรงกับรายการต่อไปนี้
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 และวิธีการจะมีตัวเลือกการปรับแต่งและการจัดรูปแบบข้อมูลส่วนใหญ่ในบริการสเปรดชีต

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

ย้ายช่วง

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

มาอัปเดตวิธี duplicateAndOrganizeActiveSheet() เพื่อย้ายข้อมูลบางส่วนกัน:

  1. ใน Google ชีต ให้คลิก Sheet_Original ชีตเพื่อเปิดใช้งาน
  2. โปรดแก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ใน Apps Script ให้ตรงกับรายการต่อไปนี้
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. โปรดแก้ไขฟังก์ชัน duplicateAndOrganizeActiveSheet() ใน Apps Script ให้ตรงกับรายการต่อไปนี้
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 นี้และดูตัวอย่าง Codelab ถัดไปในเพลย์ลิสต์นี้

7 บทสรุป

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

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

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

สิ่งที่เราพูดถึง

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

ขั้นต่อไปคืออะไร

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

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

bug_report รายงานความผิดพลาด