ฟังก์ชันที่กำหนดเองใน Google ชีต (original) (raw)

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

ค่าที่แสดงผล

ฟังก์ชันที่กำหนดเองทุกฟังก์ชันต้องแสดงค่าเพื่อแสดงผล โดยมีลักษณะดังนี้

หากฟังก์ชันที่กำหนดเองแสดงผลค่า ค่าจะแสดงในเซลล์ที่เรียกใช้ฟังก์ชัน
หากฟังก์ชันที่กำหนดเองแสดงผลอาร์เรย์ค่า 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	เข้าถึงแหล่งข้อมูลบนเว็บโดยการดึงข้อมูล 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)

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

คุณสามารถใช้เทคนิคเหล่านี้กับฟังก์ชันที่กำหนดเองเกือบทุกฟังก์ชันที่ใช้ซ้ำๆ ในสเปรดชีตได้ แม้ว่ารายละเอียดการใช้งานจะแตกต่างกันไปตามลักษณะการทำงานของฟังก์ชันก็ตาม