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 ลักษณะความสับสนที่พบบ่อยที่สุดมีดังนี้
- วันที่และเวลาในชีตจะกลายเป็นออบเจ็กต์วันที่ใน 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 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 ที่ฝังไว้แทนการเรียก 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;
}
เทคนิคเหล่านี้นำไปใช้กับฟังก์ชันที่กำหนดเองได้เกือบทุกฟังก์ชันที่ใช้ซ้ำๆ ทั้งสเปรดชีต แม้ว่ารายละเอียดการใช้งานจะแตกต่างกันไปตามลักษณะการทำงานของฟังก์ชัน