1. ยินดีต้อนรับสู่ Apps Script
Apps Script คืออะไร
Apps Script เป็นแพลตฟอร์มการพัฒนาแอปพลิเคชันที่รวดเร็วซึ่งช่วยให้คุณปรับแต่ง ปรับแต่ง และขยายประสบการณ์การใช้งาน Google Workspace ได้โดยอัตโนมัติ Apps Script ช่วยให้คุณประหยัดทั้งเวลาและแรงงานโดยการเพิ่มประสิทธิภาพการทํางานที่ยุ่งยากหรือซับซ้อนภายใน Google Workspace
ฟีเจอร์ของ Apps Script มีดังนี้
- บริการในตัวของ Apps Script ช่วยให้คุณอ่าน อัปเดต และจัดการข้อมูลแอปพลิเคชัน Google Workspace ด้วยสคริปต์ได้
- คุณสร้างสคริปต์โดยใช้ตัวแก้ไขโค้ดของ Apps Script' ได้ในเบราว์เซอร์โดยไม่ต้องติดตั้งหรือเรียกใช้ซอฟต์แวร์พัฒนาซอฟต์แวร์
- คุณออกแบบอินเทอร์เฟซผู้ใช้สําหรับเครื่องมือแก้ไข Google Workspace ซึ่งจะช่วยให้คุณเปิดใช้งานสคริปต์ได้โดยตรงจากเครื่องมือแก้ไขเหล่านั้นโดยใช้รายการในเมนู กล่องโต้ตอบ และแถบด้านข้าง
พื้นฐานนี้ของ Apps Script ที่มีเพลย์ลิสต์ Codelab ของ Google ชีตจะสอนพื้นฐานเกี่ยวกับ Apps Script และวิธีใช้เพื่อปรับปรุงประสบการณ์การใช้งาน Google ชีต Codelab นี้มุ่งเน้นที่การสอนพื้นฐานของ Apps Script
บริการสเปรดชีต
คุณจะใช้ Apps Script เพื่อขยายการใช้งาน Google ชีตเพื่อประหยัดแรงและเวลา Apps Script มีบริการสเปรดชีตที่ช่วยให้สคริปต์โต้ตอบกับไฟล์ Google ชีตและข้อมูลที่มีอยู่ได้ คุณสามารถใช้บริการนี้เพื่อกําหนดให้งานสเปรดชีตทั่วไปต่อไปนี้เป็นแบบอัตโนมัติ:
- สร้างหรือแก้ไขสเปรดชีต
- อ่านและอัปเดตข้อมูลเซลล์ สูตร และการจัดรูปแบบ
- สร้างปุ่มและเมนูที่กําหนดเอง
- นําเข้าและส่งออกข้อมูลจากแอปพลิเคชันอื่นๆ ของ Google หรือแหล่งที่มาของบุคคลที่สาม
- แชร์และควบคุมการเข้าถึงสเปรดชีต
สิ่งที่คุณจะได้เรียนรู้
เพลย์ลิสต์นี้ครอบคลุมหัวข้อทั้งหมดที่คุณจะต้องเริ่มต้นใช้งาน Apps Script กับ Google ชีต ดังนี้
- มาโครและฟังก์ชันที่กําหนดเอง
- สเปรดชีต ชีต และช่วง
- การทํางานกับข้อมูล
- การจัดรูปแบบข้อมูล
- แผนภูมิและการนําเสนอข้อมูลในสไลด์
Codelab ในเพลย์ลิสต์นี้มีไว้เพื่อการอ่านตามลําดับ ดังนั้นควรเริ่มอ่านและทําต่อให้เสร็จตามลําดับเพื่อการเรียนรู้ที่ดีที่สุด
ไปที่ส่วนถัดไปเพื่อดูข้อมูลเพิ่มเติมเกี่ยวกับโค้ด Codelab นี้
2. บทนำ
ยินดีต้อนรับสู่ Codelab แรกของเพลย์ลิสต์นี้ ใน Codelab นี้ คุณจะได้เรียนรู้พื้นฐานของการใช้ Apps Script กับ Google ชีต กล่าวคือ Codelab นี้จะมุ่งเน้นที่แนวคิดหลัก 2 อย่าง ได้แก่ มาโครและฟังก์ชันที่กําหนดเอง
มาโคร คือชุดการดําเนินการที่บันทึกไว้ภายใน Google ชีต เมื่อบันทึกแล้ว คุณจะเปิดใช้งานมาโครเพื่อทําซ้ําการดําเนินการดังกล่าวในภายหลังได้ด้วยรายการในเมนูหรือแป้นพิมพ์ลัด คุณสามารถสร้างและอัปเดตมาโครของคุณเองได้ทั้งบน Google ชีตและตัวแก้ไขโค้ดของ Apps Script
คุณยังสร้างฟังก์ชันที่กําหนดเองในเครื่องมือแก้ไขโค้ดของ Apps Script ได้ด้วย เช่นเดียวกับฟังก์ชันในตัวที่ชีตมีให้บริการ (เช่น SUM หรือ AVERAGE) คุณจะใช้ Apps Script เพื่อเขียนฟังก์ชันที่กําหนดเองสําหรับการดําเนินการที่ไม่ซับซ้อนและเฉพาะกลุ่ม (เช่น Conversion หรือการเชื่อมโยงสตริง) ได้ เมื่อสร้างแล้ว คุณจะเรียกใช้ฟังก์ชันเหล่านี้ได้ในชีตเช่นเดียวกับฟังก์ชันในตัว คุณใช้ฟังก์ชันที่กําหนดเองในสูตรเซลล์ที่คุณเขียนด้วยได้ และใช้ร่วมกับฟังก์ชันอื่นๆ ได้ตามต้องการ
อ่านแนวคิดด้านล่างเพื่อให้ทราบแนวคิดและข้อกําหนดต่างๆ เกี่ยวกับ Codelab นี้
สิ่งที่คุณจะได้เรียนรู้
- วิธีสร้างสคริปต์สําหรับ Google ชีต
- วิธีไปยังส่วนต่างๆ ในเครื่องมือแก้ไข Apps Script
- วิธีสร้างและอัปเดตมาโคร
- วิธีสร้างฟังก์ชันที่กําหนดเองแรกของชีต
สิ่งที่ต้องมี
- ทําความคุ้นเคยกับ JavaScript
- ทําความคุ้นเคยกับ Google ชีต
- ความสามารถในการอ่านรูปแบบ A1 ของชีต
บทนําจบแล้ว ไปที่ส่วนถัดไปเพื่อเริ่มทํางานกับมาโคร
3. สร้างมาโครในชีต
โดยปกติแล้ว เมื่อใช้งานในสเปรดชีต คุณจะเข้าสู่รอบการทํางานซ้ําๆ ได้ เช่น คัดลอกค่าเซลล์ การจัดรูปแบบ สร้างสูตร ฯลฯ ซึ่งอาจทําให้เกิดความเบื่อหน่ายและนําไปสู่ความผิดพลาดได้ Google ชีตมีมาโครโดยอัตโนมัติเพื่อดําเนินการซ้ําโดยอัตโนมัติ มาโครช่วยให้คุณ "บันทึก' ชุดการดําเนินการในชีตได้ เมื่อใช้มาโครที่บันทึกไว้ คุณจะทําซ้ําขั้นตอนเดิมกับที่อื่นในสเปรดชีตได้ด้วยการกดแป้นลัดง่ายๆ
ในส่วนนี้ คุณจะได้ทราบวิธีสร้างมาโครในชีต ในส่วนถัดไป คุณจะได้ดูวิธีสร้างมาโครโดยใช้ Apps Script
ข้อควรทราบก่อนที่จะเริ่มต้น
คุณต้องมีสเปรดชีตที่มีข้อมูลบางอย่างก่อนดําเนินการต่อ เราให้ข้อมูลให้คุณแล้ว โดยคลิกลิงก์นี้เพื่อคัดลอกแผ่นข้อมูล แล้วคลิกทําสําเนา
สําเนาสเปรดชีตตัวอย่างที่คุณใช้จะอยู่ในโฟลเดอร์ Google ไดรฟ์และตั้งชื่อ "สําเนาภาพยนตร์ทําคะแนนสูงสุด 10 อันดับแรก (2018)"
สร้างมาโคร
เมื่อคุณมีสเปรดชีตที่จะใช้งานแล้ว คุณก็สามารถบันทึกมาโครใน Google ชีตได้ สําหรับตัวอย่างนี้ คุณจะต้องสร้างมาโครที่จัดรูปแบบแถวส่วนหัวสําหรับข้อมูล เพียงทำตามขั้นตอนต่อไปนี้
- คลิกเซลล์ A1 เพื่อวางเคอร์เซอร์ในแถว นี่คือแถวส่วนหัวของคุณ
- ในเมนู ให้เลือกส่วนขยาย > มาโคร > บันทึกมาโคร
เมื่อเริ่มบันทึกแล้ว Google ชีตจะจดจําการดําเนินการทั้งหมดที่คุณทําในสเปรดชีต เช่น ไฮไลต์เซลล์ เพิ่มข้อมูล เปลี่ยนไปใช้ชีตอื่น จัดรูปแบบ ฯลฯ การทํางานเหล่านี้จะกลายเป็น "script'" ซึ่งจะเกิดซ้ําเมื่อคุณบันทึกและเปิดใช้งานมาโครในภายหลัง
- ในกล่องโต้ตอบมาโคร ให้เลือกการอ้างอิงแบบสัมพัทธ์
- เลือกแถว 1
- เปลี่ยนสีเติมสีของแถวบนสุดจากสีขาวเป็นสีม่วงแดงเข้ม 3
- เปลี่ยนสีข้อความของแถวบนสุดจากสีขาวเป็นสีขาว
- หากต้องการทําให้ข้อความเป็นตัวหนา ให้กด Ctrl+B (หรือ Cmd+B ใน macOS)
- หากต้องการตรึงแถวบนสุด ให้เลือกดู > ตรึง > 1 แถว
- คลิกบันทึกในกล่องโต้ตอบมาโคร กล่องโต้ตอบใหม่จะขอให้คุณตั้งชื่อมาโคร ป้อนชื่อ "Header" แล้วคลิกบันทึก
การใช้ชีต' อินเทอร์เฟซผู้ใช้ช่วยให้มาโครเป็นข้อมูลสําหรับการจัดรูปแบบส่วนหัวโดยเฉพาะ
เปิดใช้งานมาโคร
คุณใช้มาโครใหม่ในชีตได้โดยทําตามวิธีการต่อไปนี้
- หากต้องการสร้างชีต ให้คลิกเพิ่มแผ่นงาน
- ในชีตใหม่ ให้เพิ่มข้อความลงใน A1:C2 โปรดทําตามคําแนะนําด้านล่างนี้
- ไฮไลต์แถวแรก
- หากต้องการใช้มาโครกับพื้นที่ที่เลือก ให้คลิกส่วนขยาย> มาโคร> ส่วนหัว
- ให้สิทธิ์มาโครโดยทําตามวิธีการบนหน้าจอ
- ทําซ้ําขั้นตอนที่ 4 เพื่อเรียกใช้มาโครอีกครั้ง (การให้สิทธิ์มาโครจะหยุดการดําเนินการแรก)
ยินดีด้วย คุณได้ดูวิธีนํามาโครไปใช้ในชีตแล้ว สเปรดชีตควรมีลักษณะดังนี้
มาโครช่วยให้คุณสร้างสเปรดชีตได้อย่างมีประสิทธิภาพ และในส่วนถัดไปของ Codelab นี้ คุณจะได้เรียนรู้วิธีทําให้มาโครมีประสิทธิภาพมากยิ่งขึ้น เคล็ดลับมีดังนี้: เมื่อคุณบันทึกมาโคร สิ่งที่คุณจะทําจริงๆ คือการเขียน Apps Script ชีตจะดําเนินการสร้างโค้ดที่ตรงกับการทํางานมาโครในเบื้องหลัง ในส่วนถัดไป คุณจะได้ดูวิธีแก้ไขโค้ดโดยตรงโดยใช้เครื่องมือแก้ไขเบราว์เซอร์ของ Apps Script'
4. มาโครในโปรแกรมแก้ไขสคริปต์
เมื่อคุณสร้างมาโคร Google ชีตจะบันทึกการดําเนินการของคุณเป็นฟังก์ชัน Apps Script เมื่อเปิดใช้งานมาโคร Google ชีตจะเรียกใช้ฟังก์ชัน Apps Script เพื่อนําการดําเนินการเหล่านั้นไปใช้ในลําดับเดียวกัน
เครื่องมือแก้ไขสคริปต์
เมื่อสร้างมาโครแล้ว ต่อไปก็ดูโค้ดของมาโครได้ หากต้องการดูสคริปต์มาโคร ให้คลิกส่วนขยาย > Apps Script เพื่อเปิดตัวแก้ไขโค้ดของเบราว์เซอร์สําหรับ Apps Script
โปรแกรมแก้ไขสคริปต์ช่วยให้คุณเขียนโค้ดใน Apps Script และเรียกใช้สคริปต์เหล่านั้นในเซิร์ฟเวอร์ Google ได้
การวิเคราะห์ macro.gs
ตรวจสอบสคริปต์ปัจจุบัน ชีตสร้างไฟล์สคริปต์ macros.gs ไว้แล้วเมื่อคุณบันทึกมาโคร Header ซึ่งจะเติมด้วยฟังก์ชัน Apps Script ที่เกี่ยวข้องซึ่งเรียกว่า Header เมื่อคุณเปิดใช้งานมาโคร Header ชีตจะทํางานฟังก์ชันนี้
ดูรูปภาพด้านล่างเพื่อทําความคุ้นเคยกับโครงสร้างของฟังก์ชันมาโครใน Apps Script หากคุณบันทึกขั้นตอนในลําดับอื่นหรือคลิกรอบๆ สเปรดชีตขณะบันทึก โค้ดอาจดูต่างออกไปเล็กน้อย
บรรทัดแรกคือความคิดเห็นของคําอธิบายประกอบที่มีผลต่อการให้สิทธิ์
/** @OnlyCurrentDoc */
สคริปต์ส่วนใหญ่จะขอสิทธิ์บางอย่างจากผู้ใช้ก่อนจึงจะทํางานได้ สิทธิ์เหล่านี้จะควบคุมสิ่งที่ผู้ใช้อนุญาตให้สคริปต์ทําได้ เมื่อมีความคิดเห็น @OnlyCurrentDoc ในโครงการสคริปต์ Apps Script จะขอสิทธิ์เข้าถึงและเข้าถึงสเปรดชีตปัจจุบันเท่านั้น หากไม่มีความคิดเห็นนี้ Apps Script จะขอสิทธิ์เข้าถึงและอัปเดตสเปรดชีตทั้งหมดของผู้ใช้ คุณควรใส่คําอธิบายประกอบนี้เสมอเมื่อคุณทํางานกับไฟล์เดียวเท่านั้น โปรแกรมบันทึกมาโครจะเพิ่มความคิดเห็นนี้ให้คุณโดยอัตโนมัติ
หากต้องการดูฟังก์ชันการทํางานของ Apps Script ตามวิธีการของมาโคร คุณสามารถดูฟังก์ชันได้โดยทําดังนี้
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, sheet.getMaxColumns()).activate();
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
};
โค้ดนี้จะทํางานเมื่อคุณเปิดใช้งานมาโคร Header หลัง function ป้ายกํากับ Header() จะระบุชื่อฟังก์ชันและพารามิเตอร์ของฟังก์ชัน โปรดทราบว่า Header() ไม่ต้องใช้พารามิเตอร์ เนื่องจากฟังก์ชันมาโครใน Apps Script ไม่จําเป็นต้องป้อนอินพุต วงเล็บปีกกาจะใส่ไว้ในเนื้อหาฟังก์ชันของ Apps Script เสมอ
ในภายหลัง Codelab ในเพลย์ลิสต์นี้จะอธิบายคลาสและแนวคิดที่เกี่ยวข้องกับการสร้างมาโคร ในตอนนี้ คุณสามารถดูคําอธิบายโค้ดต่อไปนี้เพื่อดูแนวคิดทั่วไปเกี่ยวกับองค์ประกอบและบทบาทในการสร้างมาโครของคุณได้ ให้พิจารณาบรรทัดแรก
var spreadsheet = SpreadsheetApp.getActive();
ในที่นี้ getActive() จะแสดงออบเจ็กต์ที่แทนไฟล์สเปรดชีตที่ใช้งานอยู่ในชีตและตั้งค่าเป็นตัวแปรใหม่ spreadsheet
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, sheet.getMaxColumns()).activate();
บรรทัดเหล่านี้จะสอดคล้องกับการคลิกแถวแรกเพื่อไฮไลต์ ซึ่งเรียกว่าการเปิดใช้งาน บรรทัดแรกจะจัดเก็บชีตปัจจุบันไว้ในตัวแปร sheet ขณะที่บรรทัดที่ 2 จะได้รับทั้งแถวแรกโดยใช้เมธอด getRange() จากนั้นเรียก activate() เพื่อเปิดใช้ แถวแรกถูกระบุโดยใช้หมายเลขแถวและคอลัมน์ที่ระบุ การเรียก spreadsheet.getCurrentCell().getRow() จะแสดงผลจํานวนแถวปัจจุบัน ในขณะที่ sheet.getMaxColumns() แสดงผลจํานวนคอลัมน์สูงสุดในชีต
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
โค้ดนี้มีความซับซ้อนมากขึ้น เพื่อให้สามารถเรียกใช้เมธอดกับ spreadsheet ได้อย่างมีประสิทธิภาพ โค้ดจะซ้อนกัน 3 วิธีใน getActiveRangeList() เพื่อป้องกันไม่ให้โค้ดเรียกซ้ํากันใน spreadsheet เมธอดมากกว่า 1 ครั้ง ขณะเขียนโค้ดโดยใช้ Apps Script เพิ่มเติม คุณจะคุ้นเคยกับข้อกําหนดในการเรียกใช้วิธีการต่างๆ ในชั้นเรียนเดียวมากขึ้น (หรือที่เรียกว่าเชนเมธอด) สําหรับตอนนี้ โปรดอ่านคําอธิบายโดยย่อของแต่ละวิธีในบล็อกโค้ดดังต่อไปนี้
getActiveRangeList()แสดงผลRangeListที่ใช้งานอยู่ในปัจจุบันในspreadsheetในกรณีนี้คือแถวแรกที่เปิดใช้งานบรรทัดก่อนหน้า- ทั้งวิธี
setBackground(color)และsetFontColor(color)จะเปลี่ยนแอตทริบิวต์สีของเซลล์ในช่วงที่มีการใช้งาน setFontWeight(fontWeight)ปรับน้ําหนักของแบบอักษรสําหรับเซลล์ในช่วงที่มีการใช้งาน
สุดท้าย บรรทัดสุดท้ายจะเปลี่ยนเป็นแถวแรกของมาโคร
spreadsheet.getActiveSheet().setFrozenRows(1);
และนี่คือสคริปต์ที่คุณสร้างขึ้นเมื่อบันทึกมาโคร อย่ากังวลกับข้อกําหนดหรือวิธีการที่ไม่คุ้นเคยดังที่กล่าวไว้ข้างต้น คําอธิบายจัดทําขึ้นเพื่อให้คุณคิดถึงแนวคิดบางอย่างที่ Apps Script เน้นในฟังก์ชันมาโครโดยทั่วไป และหัวข้อ Codelab ในอนาคตจะมีเจาะลึก
ส่วนถัดไปจะมุ่งเน้นที่การควบคุมโค้ดฟังก์ชัน Header() เพื่อแสดงวิธีใช้โปรแกรมแก้ไขสคริปต์เพื่อปรับเปลี่ยนมาโครในแบบของคุณ
ปรับแต่งมาโครด้วย Apps Script
เครื่องมือแก้ไข Apps Script จะแสดงมาโครที่คุณสร้างไว้ก่อนหน้านี้ใน Google ชีต การปรับแต่งเนื้อหาของเนื้อหาฟังก์ชันทําให้คุณปรับแต่งวิธีการของมาโครและทําสิ่งต่างๆ เพิ่มเติมได้มากขึ้น แบบฝึกหัดต่อไปนี้สาธิตวิธีต่างๆ ในการจัดการมาโครด้วยโปรแกรมแก้ไขสคริปต์
เปลี่ยนเซลล์ที่ได้รับผลกระทบ
สมมติว่าคุณต้องการแก้ไขมาโคร จึงส่งผลต่อ 10 คอลัมน์แรกของแถวแรก ไม่ใช่ทั้งแถว คุณสามารถลบมาโครและบันทึกอีกครั้งได้ แต่การใช้เครื่องมือแก้ไข Apps Script จะทําให้คุณทําการเปลี่ยนแปลงได้โดยตรง วิธีดําเนินการมีดังนี้
- แทนที่
sheet.getMaxColumns()ด้วย10ในเครื่องมือแก้ไขสคริปต์ การแก้ไขนี้จะเปลี่ยนแปลงช่วงของเซลล์ที่มาโครมีต่อสเปรดชีต
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
/* sheet.getMaxColumns() replaced with 10.*/
spreadsheet.getActiveRangeList().setBackground('#4c1130')
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- หากต้องการบันทึกสคริปต์ ให้คลิกบันทึก
- หากต้องการเปลี่ยนชื่อโปรเจ็กต์ ให้ป้อน "มาโครและฟังก์ชันที่กําหนดเอง&เครื่องหมายคําพูด เป็นชื่อโปรเจ็กต์ใหม่และคลิกเปลี่ยนชื่อ
- หากต้องการสร้างชีต ให้คลิกเพิ่มแผ่นงาน ในชีต
- จากรายการเครื่องมือแก้ไขสคริปต์ ให้เลือก
Headerแล้วคลิก Run จากรายการฟังก์ชัน
คุณควรเห็นผลลัพธ์ต่อไปนี้ในชีตใหม่
การเปลี่ยนแปลงช่วงจะมีผลกับมาโครของแถวแรกเท่านั้น ด้วยการแก้ไขช่วงที่มีการใช้งานหรือเป้าหมาย โดย Apps Script หลายวิธีจะใช้ช่วงหรือเครื่องหมาย A1 เป็นพารามิเตอร์เพื่อระบุเซลล์ที่จะทํางาน
ถัดไป เราจะมาเรียนรู้เกี่ยวกับการปรับแต่งสีมาโคร
เปลี่ยนสีของมาโคร
Apps Script จะแก้ไขสีเติมหรือข้อความของข้อความตามช่วงเพื่อช่วยในการออกแบบรูปแบบสีของมาโครหรือองค์ประกอบอื่นๆ ในชีตได้ ดูวิธีการต่อไปนี้เพื่อดูวิธีปรับแต่งสีของมาโคร
วิธีการเหล่านี้เน้นการเปลี่ยนสีพื้นหลังของมาโคร
- ในชีต ให้เปลี่ยนกลับไปเป็นแผ่นงานที่มีข้อมูลต้นฉบับ (แผ่นงาน 1)
- คลิกแถวแรกเพื่อไฮไลต์
- แทนที่สีพื้นหลัง
#4c1130ด้วย#afeeeeในเครื่องมือแก้ไขสคริปต์ ค่าเหล่านี้แสดงถึงสีต่างๆ โดยใช้สัญกรณ์เลขฐานสิบหก
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
spreadsheet.getActiveRangeList().setBackground('#afeeee')
/* #4c1130 replaced with #afeeee.*/
.setFontColor('#ffffff')
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- หากต้องการบันทึกสคริปต์ ให้คลิกบันทึก
- จากรายการฟังก์ชัน ให้เลือก
Headerแล้วคลิกเรียกใช้
ในชีต สีพื้นหลังของ 10 คอลัมน์แรกในแถวแรกจะเปลี่ยนเป็นสีเทอร์ควอยซ์ที่กําหนดเอง
การเปลี่ยนรูปแบบสีแบบเลขฐาน 16 ในพารามิเตอร์ของ setBackground(color) จาก #4c1130 (ดาร์กม่วงa 3) เป็น #afeeee (เทอร์ควอยซ์สีอ่อน ซึ่งเป็นตัวเลือกที่เข้าถึงได้ในชีต' เมนูสีเริ่มต้น) คุณจะเปลี่ยนแปลงแอตทริบิวต์สีของสีพื้นหลังของมาโคร' ได้
ตอนนี้คุณได้แก้ไขสีพื้นหลังที่มาโครกําหนดไว้แล้ว หากต้องการเปลี่ยนสีข้อความด้วย ให้เปลี่ยนรหัสสีที่ 2
- ในชีต ให้คลิกแถวแรกเพื่อให้แน่ใจว่าระบบยังไฮไลต์แถวนั้นอยู่
- แทนที่สีแบบอักษร
#ffffffด้วย#191970ในเครื่องมือแก้ไขสคริปต์ ซึ่งทําให้มาโครตั้งค่าสีแบบอักษรของสีกรมท่า
/** @OnlyCurrentDoc */
function Header(){
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();
sheet.getRange(
spreadsheet.getCurrentCell().getRow(),
1, 1, 10).activate();
spreadsheet.getActiveRangeList().setBackground('#afeeee')
.setFontColor('#191970')/* #ffffff replaced with #191970.*/
.setFontWeight('bold');
spreadsheet.getActiveSheet().setFrozenRows(1);
}
- หากต้องการบันทึกสคริปต์ ให้คลิกบันทึก
- จากรายการฟังก์ชัน ให้เลือก
Headerแล้วคลิกเรียกใช้
กลับไปที่ชีตเพื่อดูสีของข้อความในแถวส่วนหัวเป็นสีกรมท่า
ตอนนี้คุณเห็นแล้วจริงๆ ว่ามาโครบันทึกการทํางานของชีตเป็นโค้ด Apps Script อย่างไร ในส่วนถัดไป คุณจะเห็นวิธีที่ Apps Script สามารถช่วยให้คุณทํางานร่วมกับ Google ชีตได้ นั่นก็คือฟังก์ชันที่กําหนดเอง
5. เขียนโค้ดสคริปต์แรก: ฟังก์ชันที่กําหนดเอง
Google ชีตมีฟังก์ชันสูตรในตัวมากมาย เช่น =SUM() ซึ่งช่วยให้คํานวณข้อมูลสเปรดชีตได้อย่างรวดเร็ว เช่นเดียวกับแอปพลิเคชันสเปรดชีตส่วนใหญ่ ฟังก์ชันที่กําหนดเองเป็นเพียงฟังก์ชันที่คุณระบุโดยใช้ Apps Script เมื่อระบุฟังก์ชันที่กําหนดเองแล้ว คุณจะใช้งานฟังก์ชันนั้นในสเปรดชีตได้เหมือนกับฟังก์ชันในตัว
ส่วนนี้จะอธิบายวิธีสร้างฟังก์ชันที่กําหนดเองใน Apps Script ที่ใช้ Conversion เป็นเงิน
สร้างไฟล์สคริปต์
เมื่อใช้สเปรดชีตและโครงการสคริปต์เดียวกันจากส่วน มาโคร ให้ทําตามวิธีการต่อไปนี้เพื่อสร้างสคริปต์ใหม่ (ซึ่งคุณสามารถใช้เพื่อสร้างฟังก์ชันที่กําหนดเองแรกได้)
- หากต้องการสร้างไฟล์ Apps Script ให้กลับไปที่เครื่องมือแก้ไขสคริปต์
- ข้างๆ ไฟล์ ให้คลิกเพิ่มไฟล์
> Script - ตั้งชื่อไฟล์สคริปต์ใหม่
customFunctionsแล้วกด Enter (สคริปต์แอปจะเพิ่มส่วนขยาย.gsต่อท้ายชื่อไฟล์สคริปต์โดยอัตโนมัติ)
แท็บใหม่ชื่อ customFunctions.gs จะปรากฏในเครื่องมือแก้ไข
เมื่อคุณสร้างสคริปต์สําหรับฟังก์ชันที่กําหนดเองโดยเฉพาะแล้ว ให้เติมโค้ดดังกล่าวด้วยโค้ด
แปลงดอลลาร์สหรัฐฯ เป็นฟรังก์สวิส
สมมติว่าคุณต้องการแก้ไขข้อมูลสําหรับ "ภาพยนตร์ที่มีรายได้รวมสูงสุด 10 อันดับแรกปี 2018'" เพื่อแสดงเฉพาะมูลค่ารวมทั่วโลกในสกุลเงินดอลลาร์สหรัฐเท่านั้น แต่ยังแสดงเฉพาะสกุลเงินฟรังก์สวิสด้วย โดยคุณจะใช้ฟังก์ชันที่กําหนดเองได้อย่างง่ายดาย การออกกําลังกายต่อไปนี้สาธิตวิธีสร้างฟังก์ชันที่กําหนดเองเพื่อแปลงมูลค่าเงินของคุณเป็นค่าทางคณิตศาสตร์
ก่อนที่จะเขียนฟังก์ชันที่กําหนดเองรายการแรก โปรดแก้ไขชุดข้อมูลเพื่อให้ฟังก์ชันแสดงเอาต์พุตที่เหมาะสมได้ โดยทำดังนี้
- ในชีต ให้คลิกขวาที่คอลัมน์ H
- คลิกแทรก 1 ขวาในเมนูที่ได้
- ติดป้ายกํากับคอลัมน์ "Worldwide_Gross (Swiss francs)" ในเซลล์ I1
ตอนนี้คุณมีคอลัมน์ที่จะจัดเก็บผลลัพธ์ของฟังก์ชัน Conversion ที่กําหนดเองแล้ว ต่อไปคุณจะใช้โปรแกรมแก้ไขสคริปต์เพื่อสร้างฟังก์ชันที่กําหนดเองรายการแรกได้
- ใน
customFunctions.gsให้แทนที่โค้ดสําหรับmyFunction()ด้วยรหัสต่อไปนี้
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The converted total of Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return swissFrancs;
}
ซึ่งเป็นรหัสที่จะแปลงดอลลาร์สหรัฐฯ เป็นฟรังก์สวิส ดูวิธีการด้านล่างและดูวิธีเรียกใช้ฟังก์ชันที่กําหนดเองในชีต
- หากต้องการบันทึกสคริปต์ ให้คลิกบันทึก
- เลือกเซลล์ I2 ในชีต
- ป้อน
=USDTOCHF(H2)ในแถบฟังก์ชัน
วิธีใช้สูตรกับเซลล์ที่เหลือในคอลัมน์
- เลื่อนเคอร์เซอร์ไปที่ด้านขวาล่างของเซลล์ I2 และเลือกช่องสีน้ําเงินขนาดเล็ก (เคอร์เซอร์ควรเปลี่ยนเป็น เมื่อชี้ไปที่ช่องสีน้ําเงิน)
- ลากช่องสีน้ําเงินลงเพื่อไฮไลต์ช่วง I3:I11
ตอนนี้คอลัมน์ I แสดงรายการ Conversion ฟรังก์สวิสของสกุลเงินดอลลาร์สหรัฐในคอลัมน์ H
ยินดีด้วย คุณสร้างฟังก์ชันที่กําหนดเองรายการแรกแล้ว ส่วนถัดไปจะอธิบายโค้ดที่ประกอบด้วย USDTOCHF()
การวิเคราะห์ USDTOCHF()
ความคิดเห็นเริ่มต้นอธิบายถึงวัตถุประสงค์ของโค้ด':
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {number} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
การบล็อกความคิดเห็นในลักษณะนี้มักใช้บ่อยในการอธิบายฟังก์ชันการทํางาน
ในความคิดเห็นนี้ คุณสามารถระบุส่วนได้ 2 ส่วน ได้แก่ คําอธิบายฟังก์ชัน (เพื่อแปลงสกุลเงินเป็นฟรังก์) และคําอธิบายประกอบที่อธิบายพารามิเตอร์และประเภทการคืนสินค้าของฟังก์ชัน
ในคําอธิบายประกอบ Apps Script จะใช้ JSDoc เพื่อช่วยจัดทําเอกสารและสร้างคําแนะนําการเติมข้อความอัตโนมัติสําหรับโค้ด อ่านวิธีที่คําอธิบายประกอบแต่ละรายการใน USDTOCHF() ช่วยเหลือคุณเกี่ยวกับการพัฒนา Apps Script ได้ที่ด้านล่าง
@param: คุณใช้คําอธิบายประกอบ@paramเพื่ออธิบายพารามิเตอร์แต่ละรายการที่ส่งไปยังฟังก์ชันได้@return: คุณสามารถใช้คําอธิบายประกอบ@returnเพื่ออธิบายฟังก์ชันที่ส่งคืนได้@customfunction: คุณควรเพิ่ม@customfunctionทุกครั้งในความคิดเห็นของเอกสารฟังก์ชันที่กําหนดเอง คําอธิบายประกอบนี้จะแจ้งเตือนชีตให้เติมฟังก์ชันที่กําหนดเองโดยอัตโนมัติเช่นเดียวกับที่ชีตเติมข้อความฟังก์ชันในตัวโดยอัตโนมัติเมื่อคุณป้อนชื่อฟังก์ชันในเซลล์ตามที่เห็นด้านล่างนี้
โปรดสังเกตว่าข้อความที่ปรากฏในป๊อปอัปการเติมข้อความอัตโนมัตินั้นตรงกับข้อความคําอธิบายที่คุณใส่ไว้ในบล็อกความคิดเห็น คุณทําให้ฟังก์ชันที่กําหนดเองใช้งานง่ายยิ่งขึ้นได้โดยการตรวจสอบว่าคําอธิบายที่คุณสร้างเขียนมาอย่างดีและสมบูรณ์
ถัดไป ให้เน้นที่โค้ดในฟังก์ชัน USDTOCHF() ดังนี้
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return swissFrancs;
}
ตามที่ได้กล่าวไปแล้ว USDTOCHF() จะใช้ตัวแปรตัวแปรตัวเลข นําไปคูณกับอัตราแลกเปลี่ยนคงที่ และส่งค่าที่แปลงเป็นฟรังก์สวิสในตัวแปรตัวเลข swissFrancs พารามิเตอร์อินพุตคือค่าที่อยู่ในเซลล์ที่ระบุเมื่อเพิ่มฟังก์ชันที่กําหนดเองในเซลล์ ในตัวอย่างนี้ จํานวนอินพุตที่ป้อนมาจากคอลัมน์ H ค่าเอาต์พุต swissFrancs จะอยู่ในเซลล์ฟังก์ชัน (คอลัมน์ I ในตัวอย่างนี้)
ฟังก์ชันที่กําหนดเองจะใช้ได้กับค่าตัวเลขหรือสตริงตามที่เห็นในส่วนถัดไป
เชื่อมต่อคํานําหน้าสตริง
สมมติว่าคุณต้องการให้เอาต์พุตที่เป็นตัวเลขของฟังก์ชัน USDTOCHF() รวมคํานําหน้าฟรังก์สวิส CHF ซึ่งทําได้โดยใช้ Apps Script โดยใช้โอเปอเรเตอร์การต่อ (+), ดังที่แสดงในวิธีการต่อไปนี้
- ในคําอธิบายประกอบสคริปต์ ให้อัปเดตคําอธิบายประกอบ
@returnเพื่อแสดงสตริงสตริงแทนตัวเลข - เปลี่ยน
return swissFrancsเป็นreturn 'CHF' + swissFrancs
โอเปอเรเตอร์ + จะเพิ่มสตริง CHF ต่อท้ายค่าที่อยู่ใน swissFrancs ตอนนี้โค้ดควรเป็นดังนี้
/**
* Converts US dollars to Swiss francs.
*
* @param {number} dollars The total number of dollars.
* @return {string} swissFrancs The provided value in Swiss francs.
* @customfunction
*/
function USDTOCHF(dollars){
var swissFrancs = dollars * .99;
return 'CHF' + swissFrancs;
}
- หากต้องการบันทึกสคริปต์ ให้คลิกบันทึก
ตอนนี้สตริง Franc ของสวิตเซอร์แลนด์นําหน้าค่าของคอลัมน์ I ดังนี้
ตอนนี้คุณไม่เพียงแปลงสกุลเงินดอลลาร์สหรัฐฯ เป็นเงินฟรังก์สวิสแล้ว แต่แปลงสกุลเงินด้วยคํานําหน้าสตริงด้วย
ขั้นสูง: ดึงข้อมูลภายนอก
นี่เป็นจุดเริ่มต้นที่ดีสําหรับฟังก์ชันที่กําหนดเองแบบพื้นฐาน แต่ตัวอย่างนี้จะถือว่าอัตราแลกเปลี่ยนสําหรับดอลลาร์เป็นฟรังก์สวิสเป็นราคาคงที่ สมมติว่าคุณต้องการใช้อัตราแลกเปลี่ยนปัจจุบัน เมื่อใดก็ตามที่มีการโหลดชีตซ้ํา ระบบจะคํานวณค่าใหม่เพื่อให้แสดงถึง Conversion ปัจจุบัน ในการทําเช่นนั้น คุณจะต้องมีวิธีค้นพบอัตราแลกเปลี่ยนปัจจุบัน ข้อมูลดังกล่าวไม่พร้อมให้บริการใน Google ชีต แต่โชคดีที่คุณมีสิทธิ์ใช้ Apps Script เพื่อรับข้อมูลดังกล่าว
คุณใช้โค้ดด้านล่างเพื่อรับอัตรา Conversion ปัจจุบันของฟรังก์สวิสเป็นดอลลาร์สหรัฐฯ ได้
function USDTOCHF(dollars){
// Gets a cache that is common to all users of the script.
var cache = CacheService.getScriptCache();
// Accesses the memory location (rates.CHF) of the script cache.
var rate = cache.get('rates.CHF');
// If a cache miss occurs, the program fetches the current
// CHF rate from an API and stores the rate in the cache
// for later convenience.
if (!rate) {
var response =
UrlFetchApp.fetch('https://api.exchangeratesapi.io/latest?base=USD');
var result = JSON.parse(response.getContentText());
rate = result.rates.CHF;
cache.put('rates.CHF', rate);
}
// Converts dollars to CHF according to the latest rate.
var swissFrancs = dollars * rate;
// Returns the CHF value.
return 'CHF' + swissFrancs;
}
โค้ดนี้จะดึงอัตราแลกเปลี่ยนปัจจุบันจากเซิร์ฟเวอร์ข้อมูลทางการเงินโดยใช้ API อัตราแลกเปลี่ยนของบุคคลที่สาม ซึ่งทําได้โดยใช้บริการ Apps Script เช่น UrlFetchApp และ CacheService แนวคิดขั้นสูงเหล่านั้นอยู่นอกเหนือขอบเขต Codelab นี้โดยเฉพาะ แต่คุณสามารถเริ่มดูความอเนกประสงค์ของ Apps Script เพื่อทําให้งานที่ซับซ้อนใน Google ชีตทํางานโดยอัตโนมัติได้
หลักเกณฑ์ฟังก์ชันที่กําหนดเอง
ขอแสดงความยินดีกับแบบฝึกหัดที่เรียกใช้ฟังก์ชันที่กําหนดเอง เมื่อใช้ฟังก์ชันที่กําหนดเองในโปรเจ็กต์ คุณจะต้องเข้าใจว่าฟังก์ชันเหล่านี้มีข้อจํากัดบางอย่าง รายการต่อไปนี้จะสรุปข้อจํากัดโดยละเอียดในคู่มือฟังก์ชันที่กําหนดเองใน Google ชีต
- อย่าสร้างฟังก์ชันที่กําหนดเองซึ่งต้องให้สิทธิ์ผู้ใช้ แต่ให้สร้างฟังก์ชันที่กําหนดเองเพื่อทํางานที่ง่ายขึ้น เช่น การคํานวณข้อมูลตัวอย่าง การแก้ไขข้อความ ฯลฯ ที่หัวข้อการใช้บริการ Apps Script
- อย่าตั้งชื่อฟังก์ชันที่กําหนดเองให้เหมือนกับฟังก์ชันในตัวอื่น หรือลงท้ายด้วยขีดล่าง อ่านหลักเกณฑ์การตั้งชื่อ
- อย่าส่งอาร์กิวเมนต์ตัวแปรไปยังฟังก์ชันที่กําหนดเอง คุณส่งผ่านค่าที่กําหนด (คงที่) ให้กับฟังก์ชันที่กําหนดเองเป็นอาร์กิวเมนต์ได้เท่านั้น การส่งอาร์กิวเมนต์ตัวแปร เช่น ผลลัพธ์ของ
=RAND()จะทําให้ฟังก์ชันที่กําหนดเองขัดข้อง ดูหลักเกณฑ์อาร์กิวเมนต์ - อย่าสร้างฟังก์ชันที่ใช้เวลามากกว่า 30 วินาที หากใช้เวลานานกว่านี้เกิดข้อผิดพลาด ดังนั้นโปรดเก็บรหัสฟังก์ชันให้เรียบง่ายและจํากัดไว้ในขอบเขต คุณควรคํานวณการคํานวณในฟังก์ชันที่กําหนดเองให้เรียบง่ายที่สุดเท่าที่จะทําได้ ดูหลักเกณฑ์ของค่าการคืนสินค้า
ตอนนี้คุณสามารถปรับปรุงสเปรดชีตโดยใช้โปรแกรมแก้ไขสคริปต์เพื่อทํางานกับมาโครและสร้างฟังก์ชันที่กําหนดเองได้แล้ว ในส่วนถัดไป คุณจะทบทวนสิ่งที่ได้เรียนรู้และสิ่งที่ทําได้ต่อไปเพื่อพัฒนาทักษะการเขียนสคริปต์
6. บทสรุป
คุณผ่าน Codelab แรกของ Fundamentals of Apps Script ด้วย Google ชีตแล้ว การสร้างและการแก้ไขมาโครของชีตและฟังก์ชันที่กําหนดเองจะช่วยให้คุณเรียนรู้แนวคิดพื้นฐานของ Apps Script ได้ คุณจะขยายขอบเขตความรู้เกี่ยวกับ Apps Script ได้มากขึ้นใน Codelab ถัดไป
คุณคิดว่า Codelab นี้มีประโยชน์ไหม
สิ่งที่เราพูดถึง
- แนวคิดพื้นฐานของ Apps Script
- วิธีไปยังส่วนต่างๆ ของเครื่องมือแก้ไขสคริปต์
- วิธีสร้างและอัปเดตมาโครชีต
- วิธีสร้างฟังก์ชันที่กําหนดเองสําหรับ Google ชีต
ขั้นต่อไปคืออะไร
Codelab ถัดไปในเพลย์ลิสต์นี้แสดงชั้นเรียนหลักและคําศัพท์ของบริการสเปรดชีตของ Apps Script บริการนี้ช่วยให้คุณควบคุมค่าและการนําเสนอข้อมูลใน Google ชีตอย่างละเอียดได้โดยใช้ Apps Script
ค้นหา Codelab ถัดไปที่สเปรดชีต ชีต และช่วง