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

Google ชีตมีฟังก์ชันในตัวหลายร้อยฟังก์ชัน เช่น AVERAGE, SUM และ VLOOKUP หากฟังก์ชันเหล่านี้ไม่เพียงพอต่อความต้องการ คุณสามารถใช้ Apps Script เพื่อเขียนฟังก์ชันที่กำหนดเอง แล้วใช้ฟังก์ชันเหล่านั้นในชีตได้เหมือนกับฟังก์ชันในตัว

ดูตัวอย่างฟังก์ชันที่กำหนดเองได้ในบทแนะนำต่อไปนี้

• คำนวณราคาขายของสินค้าที่ลดราคา (คู่มือเริ่มใช้งานฉบับย่อ)
• คำนวณส่วนลดราคาแบบเป็นขั้น
• คำนวณระยะทางในการขับรถและแปลงเมตรเป็นไมล์
• สรุปข้อมูลจากชีตหลายแผ่น
• ตรวจสอบข้อเท็จจริงของข้อความด้วย AI Agent ของ ADK และโมเดล Gemini

เริ่มต้นใช้งาน

ฟังก์ชันที่กำหนดเองสร้างขึ้นโดยใช้ 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 Workspace Marketplace เพื่อ ดูว่ามีคนสร้างฟังก์ชันที่กำหนดเองที่คุณต้องการไว้แล้วหรือไม่

สร้างฟังก์ชันที่กำหนดเอง

วิธีเขียนฟังก์ชันที่กำหนดเอง

1. สร้าง หรือเปิดสเปรดชีตในชีต
2. เลือกรายการเมนูส่วนเสริม > Apps Script
3. ลบรหัสใดๆ ในเครื่องมือแก้ไขสคริปต์ สำหรับฟังก์ชัน DOUBLE ที่แสดงไว้ก่อนหน้านี้ ให้คัดลอกและวางรหัสลงในเครื่องมือแก้ไขสคริปต์
4. คลิกบันทึก save ที่ด้านบน

ตอนนี้คุณใช้ฟังก์ชันที่กำหนดเองได้แล้ว

รับฟังก์ชันที่กำหนดเองจาก Google Workspace Marketplace

Google Workspace Marketplace มีฟังก์ชันที่กำหนดเองหลายฟังก์ชันเป็น ส่วนเสริมของ Google Workspace สำหรับ ชีต วิธีใช้หรือสำรวจส่วนเสริมเหล่านี้

1. สร้าง หรือเปิดสเปรดชีตในชีต
2. คลิกส่วนเสริม > ดาวน์โหลดส่วนเสริม ที่ด้านบน
3. เมื่อ Google Workspace Marketplace เปิดขึ้น ให้คลิกช่องค้นหาที่มุมขวาบน
4. พิมพ์ "ฟังก์ชันที่กำหนดเอง" แล้วกด Enter
5. หากพบส่วนเสริมฟังก์ชันที่กำหนดเองที่สนใจ ให้คลิกติดตั้ง เพื่อติดตั้ง
6. กล่องโต้ตอบอาจแจ้งให้คุณทราบว่าส่วนเสริมต้องมีการให้สิทธิ์ หากเป็นเช่นนั้น โปรดอ่านประกาศอย่างละเอียด แล้วคลิกอนุญาต
7. ส่วนเสริมจะพร้อมใช้งานในสเปรดชีต หากต้องการใช้ส่วนเสริมในสเปรดชีตอื่น ให้เปิดสเปรดชีตนั้น แล้วคลิกส่วนเสริม > จัดการส่วนเสริม ที่ด้านบน ค้นหาส่วนเสริมที่ต้องการใช้ แล้วคลิก ตัวเลือก more_vert > ใช้ใน เอกสารนี้

ใช้ฟังก์ชันที่กำหนดเอง

เมื่อเขียนฟังก์ชันที่กำหนดเองหรือติดตั้งฟังก์ชันจาก Google Workspace Marketplace แล้ว คุณจะใช้ฟังก์ชันนั้นได้เหมือนกับฟังก์ชันในตัว โดยทำดังนี้

1. คลิกเซลล์ที่ต้องการใช้ฟังก์ชัน
2. พิมพ์เครื่องหมายเท่ากับ (=) ตามด้วยชื่อฟังก์ชันและค่าอินพุต — เช่น =DOUBLE(A1) — แล้วกด Enter
3. เซลล์จะแสดง Loading... ชั่วครู่ แล้วแสดงผลลัพธ์

หลักเกณฑ์สำหรับฟังก์ชันที่กำหนดเอง

ก่อนเขียนฟังก์ชันที่กำหนดเอง คุณควรทราบหลักเกณฑ์บางประการ

การตั้งชื่อฟังก์ชัน

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

  
  

• อาร์กิวเมนต์ของฟังก์ชันที่กำหนดเองต้องเป็น แบบกำหนดได้ นั่นคือ ฟังก์ชันสเปรดชีตในตัวที่แสดงผลลัพธ์แตกต่างกันในแต่ละครั้งที่คำนวณ เช่น NOW() หรือ RAND() จะใช้เป็นอาร์กิวเมนต์ของฟังก์ชันที่กำหนดเองไม่ได้ หากฟังก์ชันที่กำหนดเองพยายามแสดงผลค่าตาม ฟังก์ชันในตัวที่เปลี่ยนแปลงได้เหล่านี้ ฟังก์ชันจะแสดง Loading... อย่างไม่มีกำหนด

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

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

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

• หากฟังก์ชันที่กำหนดเองแสดงผลค่า ค่าดังกล่าวจะแสดงในเซลล์ที่เรียกฟังก์ชัน
• หากฟังก์ชันที่กำหนดเองแสดงผลอาร์เรย์ 2 มิติของค่า ค่าจะล้นไปยังเซลล์ที่อยู่ติดกันตราบใดที่เซลล์เหล่านั้นว่างอยู่ หากการดำเนินการนี้จะทำให้อาร์เรย์เขียนทับเนื้อหาเซลล์ที่มีอยู่ ฟังก์ชันที่กำหนดเองจะแสดงข้อผิดพลาดแทน ดูตัวอย่างได้ในส่วน การเพิ่มประสิทธิภาพฟังก์ชันที่กำหนดเอง
• ฟังก์ชันที่กำหนดเองจะส่งผลต่อเซลล์ได้เฉพาะเซลล์ที่ฟังก์ชันแสดงผลค่าเท่านั้น กล่าวอีกนัยหนึ่งคือ ฟังก์ชันที่กำหนดเองจะแก้ไขเซลล์ใดก็ได้ไม่ได้ แต่จะแก้ไขได้เฉพาะเซลล์ที่เรียกฟังก์ชันและเซลล์ที่อยู่ติดกันเท่านั้น หากต้องการแก้ไขเซลล์ใดก็ได้ ให้ใช้เมนูที่กำหนดเองเพื่อเรียกใช้ฟังก์ชันแทน
• การเรียกใช้ฟังก์ชันที่กำหนดเองต้องแสดงผลภายใน 30 วินาที หากไม่เป็นเช่นนั้น เซลล์จะแสดง #ERROR! และหมายเหตุของเซลล์จะเป็น Exceeded maximum execution time (line 0).

ประเภทข้อมูล

ชีตจะจัดเก็บข้อมูลใน รูปแบบต่างๆ โดยขึ้นอยู่กับ ลักษณะของข้อมูล เมื่อใช้ค่าเหล่านี้ในฟังก์ชันที่กำหนดเอง Apps Script จะถือว่าค่าเหล่านั้นเป็น ประเภทข้อมูลที่เหมาะสมใน JavaScript โดยส่วนที่มักเกิดความสับสนมีดังนี้

• เวลาและวันที่ในชีตจะกลายเป็น ออบเจ็กต์ Date ใน Apps Script หากสเปรดชีตและสคริปต์ใช้เขตเวลาที่แตกต่างกัน (ปัญหาที่พบได้ยาก) ฟังก์ชันที่กำหนดเองจะต้องชดเชย
• ค่าระยะเวลาในชีตจะกลายเป็นออบเจ็กต์ Date ด้วยเช่นกัน แต่ การทำงานกับค่าเหล่านี้อาจซับซ้อน.
• ค่าเปอร์เซ็นต์ในชีตจะกลายเป็นเลขทศนิยมใน Apps Script เช่น เซลล์ที่มีค่า 10% จะกลายเป็น 0.1 ใน Apps Script

เติมข้อความอัตโนมัติ

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

ฟังก์ชันที่กำหนดเองจะปรากฏในรายการนี้หากสคริปต์มี JSDoc @customfunction แท็ก เช่น ในตัวอย่างDOUBLE()

/**
 * Multiplies the input value by 2.
 *
 * @param {number} input The value to multiply.
 * @return {number} The input multiplied by 2.
 * @customfunction
 */
function DOUBLE(input) {
  return input * 2;
}

ขั้นสูง

ส่วนนี้ครอบคลุมหัวข้อฟังก์ชันที่กำหนดเองขั้นสูง

ใช้บริการ Google Apps Script

ฟังก์ชันที่กำหนดเองสามารถเรียกบริการบางอย่างของ Apps Script เพื่อทำงานที่ ซับซ้อนมากขึ้นได้ ตัวอย่างเช่น ฟังก์ชันที่กำหนดเองสามารถเรียกใช้บริการ ภาษาเพื่อแปลวลีภาษาอังกฤษ เป็นภาษาสเปน

ฟังก์ชันที่กำหนดเองจะไม่ขอให้ผู้ใช้ให้สิทธิ์เข้าถึงข้อมูลส่วนบุคคล ซึ่งแตกต่างจาก Apps Script ประเภทอื่นๆ ส่วนใหญ่ ดังนั้น ฟังก์ชันที่กำหนดเองจึงเรียกได้เฉพาะบริการที่ไม่มีสิทธิ์เข้าถึงข้อมูลส่วนตัว ซึ่งได้แก่บริการต่อไปนี้

หากฟังก์ชันที่กำหนดเองแสดงข้อความแสดงข้อผิดพลาด You do not have permission to call X service. แสดงว่าบริการดังกล่าวต้องมีการให้สิทธิ์จากผู้ใช้ จึงใช้ในฟังก์ชันที่กำหนดเองไม่ได้

หากต้องการใช้บริการอื่นนอกเหนือจากบริการในรายการก่อนหน้า ให้สร้าง เมนูที่กำหนดเองซึ่งเรียกใช้ ฟังก์ชัน Apps Script แทนการเขียนฟังก์ชันที่กำหนดเอง ฟังก์ชันที่ทริกเกอร์จากเมนูจะขอให้ผู้ใช้ให้สิทธิ์หากจำเป็น และสามารถใช้บริการทั้งหมดของ Apps Script ได้

แชร์ฟังก์ชันที่กำหนดเอง

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

• คลิกส่วนเสริม > Apps Script เพื่อ เปิดเครื่องมือแก้ไขสคริปต์ จากนั้นคัดลอก ข้อความสคริปต์จากสเปรดชีตต้นฉบับแล้ววางลงในเครื่องมือแก้ไขสคริปต์ ของสเปรดชีตอื่น
• ทำสำเนาสเปรดชีตที่มีฟังก์ชันที่กำหนดเองโดยคลิกไฟล์ > ทำสำเนา เมื่อมีการคัดลอกสเปรดชีต ระบบจะคัดลอกสคริปต์ที่แนบมาด้วย ทุกคนที่มีสิทธิ์เข้าถึงสเปรดชีตจะคัดลอกสคริปต์ได้ (ผู้ทำงานร่วมกันที่มีสิทธิ์ดูเท่านั้นจะเปิดเครื่องมือแก้ไขสคริปต์ในสเปรดชีตต้นฉบับไม่ได้ แต่เมื่อทำสำเนา ผู้ทำงานร่วมกันจะกลายเป็นเจ้าของสำเนาและจะเห็นสคริปต์)
• เผยแพร่สคริปต์เป็นส่วนเสริมของโปรแกรมแก้ไขชีต Editor add-on

สคริปต์ที่ผูกกับคอนเทนเนอร์ทั้งหมดจะแชร์รายการการเข้าถึงเดียวกันกับคอนเทนเนอร์ ซึ่งหมายความว่าทุกคนที่มีสิทธิ์แก้ไขสเปรดชีตจะแก้ไขโค้ด Apps Script ที่แนบมาได้ด้วย ดูข้อมูลเพิ่มเติมได้ที่ สิทธิ์เข้าถึงสคริปต์ที่ผูก

การเพิ่มประสิทธิภาพ

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

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