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 และไม่มีเวลาเรียนรู้ ให้ไปที่สโตร์ของส่วนเสริมเพื่อดูว่ามีผู้อื่นสร้างฟังก์ชันที่กำหนดเองที่คุณต้องการไว้แล้วหรือไม่
การสร้างฟังก์ชันที่กำหนดเอง
วิธีเขียนฟังก์ชันที่กำหนดเอง
- สร้างหรือเปิดสเปรดชีตใน 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)) อาร์กิวเมนต์จะเป็นอาร์เรย์ 2 มิติของค่าเซลล์ ตัวอย่างเช่น ในภาพหน้าจอด้านล่าง อาร์กิวเมนต์ใน=DOUBLE(A1:B2)จะตีความโดย Apps Script เป็นdouble([[1,3],[2,4]])โปรดทราบว่าโค้ดตัวอย่างสำหรับDOUBLEจากด้านบน จะต้องถูก ยอมรับอาร์เรย์เป็นอินพุต
อาร์กิวเมนต์ฟังก์ชันที่กำหนดเองต้องเป็นแบบเชิงกำหนด กล่าวคือ ฟังก์ชันสเปรดชีตในตัวที่ให้ผลลัพธ์ที่ต่างกันทุกครั้งที่คำนวณ เช่น
NOW()หรือRAND()ไม่ได้รับอนุญาตให้เป็นอาร์กิวเมนต์ของฟังก์ชันที่กำหนดเอง หากฟังก์ชันที่กำหนดเองพยายามแสดงผลค่าตามฟังก์ชันในตัวที่มีความผันผวน ฟังก์ชันดังกล่าวจะแสดงLoading...อย่างไม่มีกำหนด
แสดงผลค่า
ทุกฟังก์ชันที่กําหนดเองจะต้องแสดงผลค่าที่จะแสดง เช่น
- หากฟังก์ชันที่กำหนดเองแสดงผลค่า ค่าดังกล่าวจะแสดงในเซลล์ที่มีการเรียกฟังก์ชันนั้น
- หากฟังก์ชันที่กำหนดเองแสดงผลอาร์เรย์ 2 มิติของค่า ค่าจะตกอยู่ในเซลล์ที่ติดกันตราบใดที่เซลล์เหล่านั้นว่างเปล่า หากการดำเนินการนี้ทำให้อาร์เรย์เขียนทับเนื้อหาของเซลล์ที่มีอยู่ ฟังก์ชันที่กำหนดเองจะแสดงข้อผิดพลาดแทน ตัวอย่างเช่น ดูส่วนการเพิ่มประสิทธิภาพฟังก์ชันที่กำหนดเอง
- ฟังก์ชันที่กำหนดเองจะไม่มีผลกับเซลล์อื่นๆ นอกเหนือจากเซลล์ที่ส่งคืนค่า กล่าวคือ ฟังก์ชันที่กำหนดเองจะแก้ไขเซลล์ที่กำหนดเองไม่ได้ แต่จะแก้ไขได้เฉพาะเซลล์ที่เรียกใช้และเซลล์ที่อยู่ติดกัน หากต้องการแก้ไขเซลล์ที่กำหนดเอง ให้ใช้เมนูที่กำหนดเองเพื่อเรียกใช้ฟังก์ชันแทน
- การเรียกใช้ฟังก์ชันที่กำหนดเองจะต้องย้อนกลับภายใน 30 วินาที หากไม่มี เซลล์จะแสดงข้อผิดพลาด:
Internal error executing the custom function.
ประเภทข้อมูล
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 ประเภทอื่นๆ ตรงที่จะไม่ขอให้ผู้ใช้ให้สิทธิ์เข้าถึงข้อมูลส่วนตัว ดังนั้น จึงสามารถเรียกใช้ได้เฉพาะบริการที่ไม่มีสิทธิ์เข้าถึงข้อมูลส่วนตัวเท่านั้น โดยเฉพาะบริการต่อไปนี้
| บริการที่รองรับ | Notes |
|---|---|
| แคช | ใช้ได้ แต่ไม่มีประโยชน์มากนักในฟังก์ชันที่กำหนดเอง |
| 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 เพื่อเปิดเครื่องมือแก้ไขสคริปต์ จากนั้นคัดลอกข้อความสคริปต์จากสเปรดชีตต้นฉบับ และวางลงในเครื่องมือแก้ไขสคริปต์ของสเปรดชีตอื่น
- ทำสำเนาสเปรดชีตที่มีฟังก์ชันที่กำหนดเองโดยคลิกไฟล์ > ทำสำเนา เมื่อคัดลอกสเปรดชีตแล้ว สคริปต์ที่แนบอยู่ จะถูกคัดลอกไปด้วย ทุกคนที่มีสิทธิ์เข้าถึงสเปรดชีตสามารถคัดลอกสคริปต์ได้ (ผู้ทำงานร่วมกันที่มีสิทธิ์ดูอย่างเดียวไม่สามารถเปิดเครื่องมือแก้ไขสคริปต์ในสเปรดชีตต้นฉบับได้ อย่างไรก็ตาม เมื่อทำสำเนา บุคคลนั้นจะกลายเป็นเจ้าของสำเนาและเห็นสคริปต์)
- เผยแพร่สคริปต์เป็นส่วนเสริมเครื่องมือแก้ไขใน 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;
}
วิธีการข้างต้นใช้วิธีการแมปของออบเจ็กต์ Array ของ JavaScript เพื่อเรียก DOUBLE ซ้ำๆ ในทุกค่าในอาร์เรย์ 2 มิติของเซลล์ โดยจะแสดงอาร์เรย์ 2 มิติที่มีผลลัพธ์ วิธีนี้จะทำให้คุณเรียก DOUBLE ได้เพียงครั้งเดียว แต่ให้คำนวณจำนวนเซลล์จำนวนมากพร้อมกัน ดังที่แสดงในภาพหน้าจอด้านล่าง (คุณสามารถดำเนินการแบบเดียวกันนี้ได้ด้วยคำสั่ง if แบบซ้อนแทนการเรียก 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;
}
เทคนิคเหล่านี้สามารถใช้กับฟังก์ชันที่กำหนดเองแทบทุกฟังก์ชันที่ใช้ซ้ำๆ กันในสเปรดชีต แม้ว่ารายละเอียดการใช้งานจะแตกต่างกันไปตามลักษณะการทำงานของฟังก์ชันก็ตาม