Google ชีตมีฟังก์ชันในตัวหลายร้อยฟังก์ชัน เช่น AVERAGE SUM และ VLOOKUP หากฟังก์ชันเหล่านี้ไม่เพียงพอต่อความต้องการ คุณสามารถใช้ Google Apps Script เพื่อเขียนฟังก์ชันที่กำหนดเองได้ เช่น เพื่อแปลงเมตรเป็นไมล์หรือดึงข้อมูล เนื้อหาแบบเรียลไทม์จากอินเทอร์เน็ต จากนั้นใช้ฟังก์ชันเหล่านั้น ใน Google ชีตได้เหมือนกับฟังก์ชันในตัว
เริ่มต้นใช้งาน
ฟังก์ชันที่กำหนดเองสร้างขึ้นโดยใช้ JavaScript มาตรฐาน หากคุณเพิ่งเริ่มใช้ JavaScript ทาง Codecademy มีหลักสูตรที่ยอดเยี่ยมสำหรับผู้เริ่มต้น (หมายเหตุ: หลักสูตรนี้ไม่ได้พัฒนาโดย Google และไม่ได้เชื่อมโยงกับ Google)
ต่อไปนี้คือฟังก์ชันที่กำหนดเองอย่างง่ายชื่อ DOUBLE ซึ่งจะคูณ ค่าอินพุตด้วย 2
/** * Multiplies an input value by 2. * @param {number} input The number to double. * @return The input multiplied by 2. * @customfunction */ function DOUBLE(input) { return input * 2; } หากไม่ทราบวิธีเขียน JavaScript และไม่มีเวลาเรียนรู้ ให้ตรวจสอบร้านค้าส่วนเสริมเพื่อดูว่า มีผู้อื่นสร้างฟังก์ชันที่กำหนดเองที่คุณต้องการแล้วหรือไม่
การสร้างฟังก์ชันที่กำหนดเอง
วิธีเขียนฟังก์ชันที่กำหนดเอง
- สร้าง หรือเปิดสเปรดชีตใน Google ชีต
- เลือกรายการเมนูส่วนขยาย > Apps Script
- ลบโค้ดในตัวแก้ไขสคริปต์ สำหรับฟังก์ชัน
DOUBLEด้านบน เพียงคัดลอกและวางโค้ดลงในโปรแกรมแก้ไขสคริปต์ - คลิกบันทึก ที่ด้านบน
ตอนนี้คุณใช้ฟังก์ชันที่กำหนดเองได้แล้ว
การรับฟังก์ชันที่กำหนดเองจาก Google Workspace Marketplace
Google Workspace Marketplace มีฟังก์ชันที่กำหนดเองหลายอย่างเป็นส่วนเสริมสำหรับ Google ชีต วิธีใช้หรือสำรวจส่วนเสริมเหล่านี้
- สร้าง หรือเปิดสเปรดชีตใน Google ชีต
- คลิกส่วนเสริม > ดาวน์โหลดส่วนเสริมที่ด้านบน
- เมื่อ Google Workspace Marketplace เปิดขึ้น ให้คลิกช่องค้นหาที่มุมขวาบน
- พิมพ์ "ฟังก์ชันที่กำหนดเอง" แล้วกด Enter
- หากพบส่วนเสริมฟังก์ชันที่กำหนดเองที่สนใจ ให้คลิกติดตั้ง เพื่อติดตั้ง
- กล่องโต้ตอบอาจแจ้งให้คุณทราบว่าส่วนเสริมต้องมีการให้สิทธิ์ หากเป็นเช่นนั้น โปรดอ่านประกาศอย่างละเอียด แล้วคลิกอนุญาต
- ส่วนเสริมจะพร้อมใช้งานในสเปรดชีต หากต้องการใช้ส่วนเสริมใน สเปรดชีตอื่น ให้เปิดสเปรดชีตนั้น แล้วคลิกส่วนเสริม > จัดการส่วนเสริมที่ด้านบน ค้นหาส่วนเสริมที่ต้องการใช้ แล้วคลิก ตัวเลือก > ใช้ในเอกสารนี้
การใช้ฟังก์ชันที่กำหนดเอง
เมื่อเขียนฟังก์ชันที่กำหนดเองหรือติดตั้งฟังก์ชันจากGoogle Workspace Marketplaceแล้ว คุณจะใช้ฟังก์ชันนั้นได้ง่ายเหมือนกับฟังก์ชันในตัว โดยทำดังนี้
- คลิกเซลล์ที่ต้องการใช้ฟังก์ชัน
- พิมพ์เครื่องหมายเท่ากับ (
=) ตามด้วยชื่อฟังก์ชันและค่าอินพุต — เช่น=DOUBLE(A1)— แล้วกด Enter - เซลล์จะแสดง
Loading...ชั่วครู่ แล้วแสดงผลลัพธ์
หลักเกณฑ์สำหรับฟังก์ชันที่กำหนดเอง
ก่อนที่จะเขียนฟังก์ชันที่กำหนดเอง คุณควรทราบหลักเกณฑ์ 2-3 ข้อ
การตั้งชื่อ
นอกเหนือจากรูปแบบมาตรฐานสำหรับการตั้งชื่อฟังก์ชัน JavaScript แล้ว โปรด ทราบข้อมูลต่อไปนี้
- ชื่อของฟังก์ชันที่กำหนดเองต้องแตกต่างจากชื่อของฟังก์ชันในตัว เช่น
SUM() - ชื่อของฟังก์ชันที่กำหนดเองต้องไม่ลงท้ายด้วยขีดล่าง (
_) ซึ่ง ระบุฟังก์ชันส่วนตัวใน Apps Script - ต้องประกาศชื่อของฟังก์ชันที่กำหนดเองด้วยไวยากรณ์
function myFunction()ไม่ใช่var myFunction = new Function() - การใช้อักษรตัวพิมพ์ใหญ่ไม่มีผล แม้ว่าชื่อฟังก์ชันสเปรดชีต มักจะใช้อักษรตัวพิมพ์ใหญ่ก็ตาม
อาร์กิวเมนต์
ฟังก์ชันที่กำหนดเองสามารถรับอาร์กิวเมนต์เป็นค่าอินพุตได้เช่นเดียวกับฟังก์ชันในตัว
- หากเรียกฟังก์ชันโดยมีการอ้างอิงเซลล์เดียวเป็นอาร์กิวเมนต์ (เช่น
=DOUBLE(A1)) อาร์กิวเมนต์จะเป็นค่าของเซลล์ หากเรียกใช้ฟังก์ชันโดยมีการอ้างอิงไปยังช่วงของเซลล์เป็นอาร์กิวเมนต์ (เช่น
=DOUBLE(A1:B10)) อาร์กิวเมนต์จะเป็นอาร์เรย์สองมิติของค่าในเซลล์ ตัวอย่างเช่น ในภาพหน้าจอด้านล่าง อาร์กิวเมนต์ใน=DOUBLE(A1:B2)จะได้รับการตีความโดย Apps Script เป็นdouble([[1,3],[2,4]])โปรดทราบว่าโค้ดตัวอย่างสำหรับDOUBLEจากด้านบนจะต้องแก้ไขเพื่อยอมรับอาร์เรย์เป็นอินพุต
อาร์กิวเมนต์ของฟังก์ชันที่กำหนดเองต้องกำหนดได้ กล่าวคือ ฟังก์ชันสเปรดชีตในตัวที่แสดงผลลัพธ์ที่แตกต่างกันทุกครั้งที่มีการคำนวณ เช่น
NOW()หรือRAND()จะไม่ได้รับอนุญาตให้ใช้เป็นอาร์กิวเมนต์ในฟังก์ชันที่กำหนดเอง หากฟังก์ชันที่กำหนดเองพยายามแสดงค่าตาม ฟังก์ชันในตัวที่เปลี่ยนแปลงได้เหล่านี้ ฟังก์ชันดังกล่าวจะแสดงLoading...อย่างไม่มีกำหนด
ค่าที่แสดงผล
ฟังก์ชันที่กำหนดเองทุกฟังก์ชันต้องส่งคืนค่าเพื่อแสดง โดยมีลักษณะดังนี้
- หากฟังก์ชันที่กำหนดเองแสดงผลค่า ค่าดังกล่าวจะแสดงในเซลล์ ที่เรียกใช้ฟังก์ชัน
- หากฟังก์ชันที่กำหนดเองแสดงผลอาร์เรย์ค่า 2 มิติ ค่าจะ ล้นไปยังเซลล์ที่อยู่ติดกันตราบใดที่เซลล์เหล่านั้นว่างอยู่ หากการดำเนินการนี้ทำให้อาร์เรย์เขียนทับเนื้อหาของเซลล์ที่มีอยู่ ฟังก์ชันที่กำหนดเองจะแสดงข้อผิดพลาดแทน ดูตัวอย่างได้ที่ส่วนเกี่ยวกับการเพิ่มประสิทธิภาพฟังก์ชันที่กำหนดเอง
- ฟังก์ชันที่กำหนดเองจะส่งผลต่อเซลล์อื่นไม่ได้นอกเหนือจากเซลล์ที่ฟังก์ชันแสดงค่า กล่าวคือ ฟังก์ชันที่กำหนดเองจะแก้ไขเซลล์ใดก็ได้ไม่ได้ แต่จะแก้ไขได้เฉพาะเซลล์ที่เรียกใช้ฟังก์ชันและเซลล์ที่อยู่ติดกันเท่านั้น หากต้องการแก้ไขเซลล์ใดๆ ให้ใช้เมนูที่กำหนดเองเพื่อเรียกใช้ฟังก์ชันแทน
- การเรียกใช้ฟังก์ชันที่กำหนดเองต้องส่งคืนภายใน 30 วินาที หากไม่มี เซลล์จะแสดง
#ERROR!และหมายเหตุของเซลล์จะเป็นExceeded maximum execution time (line 0).
ประเภทข้อมูล
Google ชีตจะจัดเก็บข้อมูลในรูปแบบต่างๆ โดยขึ้นอยู่กับลักษณะของข้อมูล เมื่อใช้ค่าเหล่านี้ในฟังก์ชันที่กำหนดเอง Apps Script จะถือว่าค่าเหล่านี้เป็นประเภทข้อมูลที่เหมาะสมใน JavaScript สิ่งที่คนทั่วไปมักสับสนมีดังนี้
- เวลาและวันที่ในชีตจะกลายเป็นออบเจ็กต์ Date ใน Apps Script หากสเปรดชีตและสคริปต์ใช้เขตเวลาที่ต่างกัน (ซึ่งเป็นปัญหาที่พบได้ยาก) ฟังก์ชันที่กำหนดเองจะต้องชดเชย
- ค่าระยะเวลาในชีตจะกลายเป็นออบเจ็กต์
Dateด้วย แต่การทำงานกับค่าเหล่านี้อาจซับซ้อน - ค่าเปอร์เซ็นต์ในชีตจะกลายเป็นตัวเลขทศนิยมใน Apps Script เช่น เซลล์ที่มีค่า
10%จะกลายเป็น0.1ใน Apps Script
เติมข้อความอัตโนมัติ
Google ชีตรองรับการเติมข้อความอัตโนมัติสำหรับฟังก์ชันที่กำหนดเองเช่นเดียวกับฟังก์ชันในตัว ขณะที่คุณพิมพ์ชื่อฟังก์ชันในเซลล์ คุณจะเห็นรายการฟังก์ชันสำเร็จรูปและฟังก์ชันที่กำหนดเองที่ตรงกับสิ่งที่คุณป้อน
ฟังก์ชันที่กำหนดเองจะปรากฏในรายการนี้หากสคริปต์มีแท็ก JsDoc @customfunction ดังในตัวอย่าง DOUBLE() ด้านล่าง
/** * Multiplies the input value by 2. * * @param {number} input The value to multiply. * @return The input multiplied by 2. * @customfunction */ function DOUBLE(input) { return input * 2; } ขั้นสูง
การใช้บริการของ Google Apps Script
ฟังก์ชันที่กำหนดเองสามารถเรียกใช้บริการของ Google Apps Script บางอย่างเพื่อทำงานที่ซับซ้อนมากขึ้นได้ ตัวอย่างเช่น ฟังก์ชันที่กำหนดเองสามารถเรียกใช้บริการภาษาเพื่อแปลวลีภาษาอังกฤษเป็นภาษาสเปนได้
ฟังก์ชันที่กำหนดเองจะไม่ขอให้ผู้ใช้ ให้สิทธิ์เข้าถึงข้อมูลส่วนตัว ซึ่งต่างจาก Apps Script ประเภทอื่นๆ ส่วนใหญ่ ดังนั้น จึงเรียกใช้ได้เฉพาะบริการที่ไม่มีสิทธิ์เข้าถึงข้อมูลส่วนตัว ซึ่งได้แก่
| บริการที่รองรับ | หมายเหตุ |
|---|---|
| แคช | ใช้ได้ แต่ไม่ค่อยมีประโยชน์ในฟังก์ชันที่กำหนดเอง |
| HTML | สร้าง HTML ได้ แต่แสดงไม่ได้ (ไม่ค่อยมีประโยชน์) |
| JDBC | |
| ภาษา | |
| ล็อก | ใช้ได้ แต่ไม่ค่อยมีประโยชน์ในฟังก์ชันที่กำหนดเอง |
| Maps | คำนวณเส้นทางได้ แต่แสดงแผนที่ไม่ได้ |
| พร็อพเพอร์ตี้ | getUserProperties() จะรับเฉพาะพร็อพเพอร์ตี้ของ เจ้าของสเปรดชีต ผู้แก้ไขสเปรดชีตตั้งค่าพร็อพเพอร์ตี้ผู้ใช้ใน ฟังก์ชันที่กำหนดเองไม่ได้ |
| สเปรดชีต | อ่านอย่างเดียว (ใช้ได้กับget*()วิธีส่วนใหญ่ แต่ใช้กับset*()ไม่ได้)เปิดสเปรดชีตอื่นๆ ไม่ได้ ( SpreadsheetApp.openById() หรือ SpreadsheetApp.openByUrl()) |
| การดึงข้อมูล URL | |
| ยูทิลิตี | |
| XML |
หากฟังก์ชันที่กำหนดเองแสดงข้อความแสดงข้อผิดพลาด You do not have permission to call X service. แสดงว่าบริการต้องมีการให้สิทธิ์จากผู้ใช้ จึงไม่สามารถใช้ในฟังก์ชันที่กำหนดเองได้
หากต้องการใช้บริการอื่นๆ นอกเหนือจากบริการที่ระบุไว้ข้างต้น ให้สร้างเมนูที่กำหนดเองซึ่งเรียกใช้ฟังก์ชัน Apps Script แทนการเขียนฟังก์ชันที่กำหนดเอง ฟังก์ชันที่ทริกเกอร์จากเมนู จะขอการให้สิทธิ์จากผู้ใช้หากจำเป็น และจะใช้บริการ Apps Script ทั้งหมดได้
การแชร์
ฟังก์ชันที่กำหนดเองจะเริ่มต้นด้วยการเชื่อมโยงกับสเปรดชีตที่สร้างฟังก์ชันนั้น ซึ่งหมายความว่าคุณจะใช้ฟังก์ชันที่กำหนดเองซึ่งเขียนในสเปรดชีตหนึ่งในสเปรดชีตอื่นไม่ได้ เว้นแต่จะใช้วิธีใดวิธีหนึ่งต่อไปนี้
- คลิกส่วนเสริม > Apps Script เพื่อ เปิดโปรแกรมแก้ไขสคริปต์ จากนั้นคัดลอก ข้อความสคริปต์จากสเปรดชีตต้นฉบับ แล้ววางลงในโปรแกรมแก้ไขสคริปต์ ของสเปรดชีตอื่น
- ทำสำเนาสเปรดชีตที่มีฟังก์ชันที่กำหนดเองโดยคลิกไฟล์ > ทำสำเนา เมื่อคัดลอกสเปรดชีต ระบบจะคัดลอกสคริปต์ที่แนบมากับสเปรดชีตนั้นด้วย ทุกคนที่มีสิทธิ์เข้าถึงสเปรดชีตจะคัดลอกสคริปต์ได้ (ผู้ทำงานร่วมกันที่มีสิทธิ์เข้าถึงแบบดูอย่างเดียวจะเปิดเครื่องมือแก้ไขสคริปต์ ในสเปรดชีตต้นฉบับไม่ได้ แต่เมื่อทำสำเนาแล้ว ผู้ใช้จะกลายเป็น เจ้าของสำเนาและดูสคริปต์ได้)
- เผยแพร่สคริปต์เป็นส่วนเสริมของเอดิเตอร์ใน Google ชีต
การเพิ่มประสิทธิภาพ
ทุกครั้งที่มีการใช้ฟังก์ชันที่กำหนดเองในสเปรดชีต Google ชีตจะทำการเรียกไปยังเซิร์ฟเวอร์ Apps Script แยกต่างหาก หากสเปรดชีตมีการเรียกฟังก์ชันที่กำหนดเองหลายสิบรายการ (หรือหลายร้อยหรือหลายพันรายการ) กระบวนการนี้อาจค่อนข้างช้า โปรเจ็กต์บางรายการที่มีฟังก์ชันที่กำหนดเองจำนวนมากหรือซับซ้อนอาจ เกิดความล่าช้าชั่วคราวในการดำเนินการ
ดังนั้น หากคุณวางแผนที่จะใช้ฟังก์ชันที่กำหนดเองหลายครั้งใน ช่วงข้อมูลขนาดใหญ่ ให้พิจารณาแก้ไขฟังก์ชันเพื่อให้ยอมรับช่วงเป็น อินพุตในรูปแบบของอาร์เรย์ 2 มิติ จากนั้นแสดงผลอาร์เรย์ 2 มิติที่สามารถล้นไปยังเซลล์ที่เหมาะสมได้
ตัวอย่างเช่น คุณสามารถเขียนฟังก์ชัน DOUBLE() ที่แสดงด้านบนใหม่เพื่อยอมรับเซลล์เดียวหรือช่วงของเซลล์ได้ดังนี้
/** * Multiplies the input value by 2. * * @param {number|Array<Array<number>>} input The value or range of cells * to multiply. * @return The input multiplied by 2. * @customfunction */ function DOUBLE(input) { return Array.isArray(input) ? input.map(row => row.map(cell => cell * 2)) : input * 2; } แนวทางข้างต้นใช้วิธีการ map ของออบเจ็กต์ Array ของ JavaScript กับวิธีการใน อาร์เรย์สองมิติของเซลล์เพื่อรับแต่ละแถว จากนั้นสำหรับแต่ละแถว จะใช้ map อีกครั้งเพื่อส่งคืนค่าของแต่ละเซลล์เป็น 2 เท่า โดยจะแสดงอาร์เรย์ 2 มิติที่มีผลลัพธ์ ด้วยวิธีนี้ คุณจะเรียกใช้ฟังก์ชัน DOUBLE เพียงครั้งเดียว แต่ให้ฟังก์ชันคำนวณค่าสำหรับเซลล์จำนวนมากพร้อมกันได้ ดังที่แสดงใน ภาพหน้าจอด้านล่าง (คุณสามารถทำสิ่งเดียวกันนี้ได้ด้วยif คำสั่งmapที่ซ้อนกันแทนการเรียกใช้ map)
ในทำนองเดียวกัน ฟังก์ชันที่กำหนดเองด้านล่างจะดึงเนื้อหาแบบเรียลไทม์จากอินเทอร์เน็ตอย่างมีประสิทธิภาพ และใช้อาร์เรย์ 2 มิติเพื่อแสดงผลลัพธ์ 2 คอลัมน์ด้วยการเรียกฟังก์ชันเพียงครั้งเดียว หากแต่ละเซลล์ต้องมีการเรียกฟังก์ชันของตัวเอง การดำเนินการจะใช้เวลานานขึ้นมาก เนื่องจากเซิร์ฟเวอร์ Apps Script จะต้องดาวน์โหลดและแยกวิเคราะห์ฟีด XML ทุกครั้ง
/** * Show the title and date for the first page of posts on the * Developer blog. * * @return Two columns of data representing posts on the * Developer blog. * @customfunction */ function getBlogPosts() { var array = []; var url = 'https://gsuite-developers.googleblog.com/atom.xml'; var xml = UrlFetchApp.fetch(url).getContentText(); var document = XmlService.parse(xml); var root = document.getRootElement(); var atom = XmlService.getNamespace('http://www.w3.org/2005/Atom'); var entries = document.getRootElement().getChildren('entry', atom); for (var i = 0; i < entries.length; i++) { var title = entries[i].getChild('title', atom).getText(); var date = entries[i].getChild('published', atom).getValue(); array.push([title, date]); } return array; } คุณสามารถใช้เทคนิคเหล่านี้กับฟังก์ชันที่กำหนดเองเกือบทุกฟังก์ชันที่ใช้ซ้ำๆ ในสเปรดชีตได้ แม้ว่ารายละเอียดการใช้งานจะแตกต่างกันไปตามลักษณะการทำงานของฟังก์ชันก็ตาม