Google ชีตมีฟังก์ชันในตัวหลายร้อยรายการ เช่น AVERAGE, SUM และ VLOOKUP หากฟังก์ชันเหล่านี้ไม่เพียงพอต่อความต้องการ คุณสามารถใช้ Google Apps Script เพื่อเขียนฟังก์ชันที่กำหนดเองได้ เช่น แปลงเมตรเป็นไมล์หรือดึงข้อมูลสดจากอินเทอร์เน็ต จากนั้นนำไปใช้ใน Google ชีตได้เช่นเดียวกับฟังก์ชันในตัว
เริ่มต้นใช้งาน
ฟังก์ชันที่กําหนดเองสร้างขึ้นโดยใช้ JavaScript มาตรฐาน หากคุณเพิ่งเริ่มใช้ JavaScript ทาง Codecademy มีหลักสูตรที่ยอดเยี่ยมสำหรับผู้เริ่มต้น (หมายเหตุ: หลักสูตรนี้ไม่ได้พัฒนาโดยและไม่เกี่ยวข้องกับ 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 และไม่มีเวลาเรียนรู้ ให้ไปที่ Store ของส่วนเสริมเพื่อดูว่ามีคนสร้างฟังก์ชันที่กำหนดเองซึ่งคุณต้องการไว้ให้ใช้งานแล้วหรือยัง
การสร้างฟังก์ชันที่กําหนดเอง
วิธีเขียนฟังก์ชันที่กําหนดเอง
- สร้างหรือเปิดสเปรดชีตใน Google ชีต
- เลือกรายการเมนูส่วนเสริม > Apps Script
- ลบโค้ดในเครื่องมือแก้ไขสคริปต์ สําหรับฟังก์ชัน
DOUBLEด้านบน เพียงคัดลอกและวางโค้ดลงในเครื่องมือแก้ไขสคริปต์ - คลิกบันทึก ที่ด้านบน
ตอนนี้คุณใช้ฟังก์ชันที่กําหนดเองได้แล้ว
การรับฟังก์ชันที่กําหนดเองจาก Google Workspace Marketplace
Google Workspace Marketplace มีฟังก์ชันที่กำหนดเองหลายรายการเป็นส่วนเสริมสำหรับ Google ชีต วิธีใช้หรือสำรวจส่วนเสริมเหล่านี้
- สร้างหรือเปิดสเปรดชีตใน Google ชีต
- คลิกส่วนเสริม > ดาวน์โหลดส่วนเสริมที่ด้านบน
- เมื่อ Google Workspace Marketplace เปิดตัวแล้ว ให้คลิกช่องค้นหาที่มุมขวาบน
- พิมพ์ "ฟังก์ชันที่กำหนดเอง" แล้วกด Enter
- หากพบส่วนเสริมฟังก์ชันที่กำหนดเองที่สนใจ ให้คลิกติดตั้งเพื่อติดตั้ง
- กล่องโต้ตอบอาจแจ้งให้ทราบว่าส่วนเสริมต้องมีการให้สิทธิ์ หากใช่ ให้อ่านประกาศอย่างละเอียด แล้วคลิกอนุญาต
- ส่วนเสริมจะพร้อมใช้งานในสเปรดชีต หากต้องการใช้ส่วนเสริมในสเปรดชีตอื่น ให้เปิดสเปรดชีตอื่น แล้วคลิกส่วนเสริม > จัดการส่วนเสริมที่ด้านบน ค้นหาส่วนเสริมที่ต้องการใช้ แล้วคลิกตัวเลือก > ใช้ในเอกสารนี้
การใช้ฟังก์ชันที่กําหนดเอง
เมื่อเขียนฟังก์ชันที่กำหนดเองหรือติดตั้งฟังก์ชันจากGoogle Workspace Marketplaceแล้ว คุณจะใช้งานฟังก์ชันดังกล่าวได้ง่ายๆ เช่นเดียวกับฟังก์ชันในตัว ดังนี้
- คลิกเซลล์ที่ต้องการใช้ฟังก์ชัน
- พิมพ์เครื่องหมายเท่ากับ (
=) ตามด้วยชื่อฟังก์ชันและค่าอินพุต (เช่น=DOUBLE(A1)) แล้วกด Enter - เซลล์จะแสดง
Loading...ชั่วครู่ จากนั้นจะแสดงผลลัพธ์
หลักเกณฑ์สําหรับฟังก์ชันที่กําหนดเอง
โปรดดูหลักเกณฑ์ต่อไปนี้ก่อนเขียนฟังก์ชันที่กําหนดเอง
การตั้งชื่อ
นอกเหนือจากรูปแบบมาตรฐานการตั้งชื่อฟังก์ชัน JavaScript แล้ว โปรดคำนึงถึงสิ่งต่อไปนี้ด้วย
- ชื่อของฟังก์ชันที่กำหนดเองต้องแตกต่างจากชื่อของฟังก์ชันในตัว เช่น
SUM() - ชื่อของฟังก์ชันที่กำหนดเองต้องไม่ลงท้ายด้วยขีดล่าง (
_) ซึ่งบ่งบอกถึงฟังก์ชันส่วนตัวในสคริปต์ของแอป - ชื่อของฟังก์ชันที่กําหนดเองต้องประกาศด้วยไวยากรณ์
function myFunction()ไม่ใช่var myFunction = new Function() - การใช้อักษรตัวพิมพ์ใหญ่และเล็กไม่สำคัญ แม้ว่าชื่อฟังก์ชันสเปรดชีตตามธรรมเนียมแล้วจะเป็นตัวพิมพ์ใหญ่
อาร์กิวเมนต์
ฟังก์ชันที่กําหนดเองสามารถใช้อาร์กิวเมนต์เป็นค่าอินพุตได้เช่นเดียวกับฟังก์ชันในตัว ดังนี้
- หากคุณเรียกใช้ฟังก์ชันโดยอ้างอิงเซลล์เดียวเป็นอาร์กิวเมนต์ (เช่น
=DOUBLE(A1)) อาร์กิวเมนต์จะเป็นค่าของเซลล์นั้น หากคุณเรียกใช้ฟังก์ชันโดยอ้างอิงถึงช่วงของเซลล์เป็นอาร์กิวเมนต์ (เช่น
=DOUBLE(A1:B10)) อาร์กิวเมนต์จะเป็นอาร์เรย์ 2 มิติของค่าของเซลล์ เช่น ในภาพหน้าจอด้านล่าง Apps Script จะตีความอาร์กิวเมนต์ใน=DOUBLE(A1:B2)เป็น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 บางรายการเพื่อดําเนินการที่ซับซ้อนมากขึ้น เช่น ฟังก์ชันที่กําหนดเองสามารถเรียกบริการภาษาเพื่อแปลวลีภาษาอังกฤษเป็นภาษาสเปน
ฟังก์ชันที่กำหนดเองจะไม่ขอสิทธิ์เข้าถึงข้อมูลส่วนบุคคลจากผู้ใช้ ต่างจากสคริปต์แอปประเภทอื่นๆ ส่วนใหญ่ ดังนั้น บริการดังกล่าวจะเรียกใช้เฉพาะบริการที่ไม่มีสิทธิ์เข้าถึงข้อมูลส่วนบุคคลได้ ดังนี้
| บริการที่รองรับ | หมายเหตุ |
|---|---|
| แคช | ใช้งานได้ แต่ไม่มีประโยชน์มากนักในฟังก์ชันที่กำหนดเอง |
| 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 เพื่อเรียกใช้ DOUBLE ในค่าทุกค่าในอาร์เรย์ 2 มิติของเซลล์แบบซ้ำ โดยจะแสดงผลลัพธ์เป็นอาร์เรย์ 2 มิติ วิธีนี้ช่วยให้คุณเรียกใช้ DOUBLE ได้เพียงครั้งเดียวแต่ให้คํานวณสําหรับเซลล์จํานวนมากพร้อมกันได้ ดังที่แสดงในภาพหน้าจอด้านล่าง (คุณทําสิ่งเดียวกันได้โดยใช้if stmt ที่ฝังแทนการเรียกใช้ 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;
}
เทคนิคเหล่านี้ใช้ได้กับฟังก์ชันที่กำหนดเองเกือบทุกรายการที่ใช้ซ้ำๆ ในสเปรดชีต แม้ว่ารายละเอียดการใช้งานจะแตกต่างกันไปตามลักษณะการทํางานของฟังก์ชัน