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 มิติ อาร์เรย์ของเซลล์ เช่น ในภาพหน้าจอด้านล่าง Apps Script จะตีความอาร์กิวเมนต์ใน=DOUBLE(A1:B2)เป็นdouble([[1,3],[2,4]])โปรดทราบว่าโค้ดตัวอย่างสำหรับDOUBLEจากด้านบนจะต้องเป็น แก้ไขให้ยอมรับอาร์เรย์เป็นอินพุต
อาร์กิวเมนต์ของฟังก์ชันที่กำหนดเองต้องเป็น definative นั่น คือฟังก์ชันสเปรดชีตในตัวที่ให้ผลลัพธ์ที่แตกต่างกันในแต่ละครั้ง ไม่อนุญาตให้ใช้ค่าที่คำนวณ เช่น
NOW()หรือRAND()เป็นอาร์กิวเมนต์ เป็นฟังก์ชันที่กำหนดเอง หากฟังก์ชันที่กำหนดเองพยายามแสดงผลค่าตาม ฟังก์ชันในตัวที่มีความผันผวนเหล่านี้ จะแสดงLoading...ไปเรื่อยๆ
แสดงผลค่า
ทุกฟังก์ชันที่กําหนดเองจะต้องแสดงผลค่าที่จะแสดง เช่น
- หากฟังก์ชันที่กำหนดเองแสดงผลค่า ค่าดังกล่าวจะแสดงในเซลล์ มีการเรียกฟังก์ชันนี้
- ถ้าฟังก์ชันที่กำหนดเองแสดงผลอาร์เรย์สองมิติของค่า ค่า เกินเข้าไปในเซลล์ที่ติดกันตราบใดที่เซลล์เหล่านั้นว่างเปล่า หากสิ่งนี้ ทำให้อาร์เรย์เขียนทับเนื้อหาของเซลล์ที่มีอยู่ ฟังก์ชันที่กำหนดเองจะ แสดงข้อผิดพลาดแทน ดูตัวอย่างได้ที่ส่วน การเพิ่มประสิทธิภาพฟังก์ชันที่กำหนดเอง
- ฟังก์ชันที่กำหนดเองจะไม่มีผลกับเซลล์อื่นๆ ที่ไม่ใช่เซลล์ที่ส่งกลับค่า กล่าวอีกนัยหนึ่งคือ ฟังก์ชันที่กำหนดเองไม่สามารถแก้ไขเซลล์ที่กำหนดเองได้ มีเพียง ที่เรียกและเซลล์ที่อยู่ติดกัน ในการแก้ไขเซลล์ที่กำหนดเอง ใช้เมนูที่กำหนดเองเพื่อเรียกใช้ฟังก์ชันแทน
- การเรียกใช้ฟังก์ชันที่กำหนดเองจะต้องย้อนกลับภายใน 30 วินาที ไม่เช่นนั้น ระบบจะ
เซลล์แสดง
#ERROR!และบันทึกของเซลล์คือExceeded maximum execution time (line 0).
ประเภทข้อมูล
Google ชีตจัดเก็บข้อมูลใน รูปแบบต่างๆ โดยขึ้นอยู่กับ ลักษณะของข้อมูล เมื่อใช้ค่าเหล่านี้ในฟังก์ชันที่กำหนดเอง สคริปต์จะถือว่าเป็น ประเภทข้อมูลที่เหมาะสมใน 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 Scripts ประเภทอื่นๆ ตรงที่จะไม่ขอให้ผู้ใช้ดำเนินการ ให้สิทธิ์เข้าถึงข้อมูลส่วนตัว ดังนั้น ลูกค้าสามารถโทรหาบริการ ที่ไม่มีสิทธิ์เข้าถึงข้อมูลส่วนตัว โดยเฉพาะในกรณีต่อไปนี้
| บริการที่รองรับ | หมายเหตุ |
|---|---|
| แคช | ใช้ได้ แต่ไม่มีประโยชน์มากนักในฟังก์ชันที่กำหนดเอง |
| 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 มิติ ที่สามารถเกินในเซลล์ที่เหมาะสม
ตัวอย่างเช่น คุณสามารถเขียนฟังก์ชัน 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)
ในทำนองเดียวกัน ฟังก์ชันที่กำหนดเองด้านล่างจะดึงเนื้อหาสดจาก อินเทอร์เน็ตและใช้อาร์เรย์สองมิติเพื่อแสดงผลลัพธ์สองคอลัมน์ด้วย การเรียกฟังก์ชันเพียงครั้งเดียว หากแต่ละเซลล์ต้องมีการเรียกฟังก์ชันของตัวเอง ฟังก์ชัน อาจใช้เวลานานกว่ามาก เนื่องจากเซิร์ฟเวอร์ 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;
}
เทคนิคเหล่านี้สามารถใช้กับฟังก์ชันที่กำหนดเองได้เกือบทุกฟังก์ชันที่ใช้ ซ้ำๆ ทั่วทั้งสเปรดชีต แตกต่างกันไปตามลักษณะการทำงานของฟังก์ชัน