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

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

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

• คำนวณราคาลดของสินค้าที่มีส่วนลด (เริ่มใช้งานฉบับย่อ)
• คำนวณส่วนลดราคาตามลำดับชั้น
• คำนวณระยะทางขับรถและแปลงเมตรเป็นไมล์
• สรุปข้อมูลจากหลายชีต
• ตรวจสอบข้อเท็จจริงของคำกล่าวด้วยเอเจนต์ AI ของ 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 เพื่อ ดูว่ามีใครสร้างฟังก์ชันที่กำหนดเองที่คุณต้องการแล้วหรือไม่

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

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

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

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

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

ฟังก์ชันที่กำหนดเองจะปรากฏในรายการนี้หากสคริปต์มีแท็ก @customfunction JSDoc 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 เพื่อ เปิดโปรแกรมแก้ไขสคริปต์ จากนั้นคัดลอก ข้อความสคริปต์จากสเปรดชีตต้นฉบับ แล้ววางลงในโปรแกรมแก้ไขสคริปต์ ของสเปรดชีตอื่น
• ทำสำเนาสเปรดชีตที่มีฟังก์ชันที่กำหนดเองโดยคลิกไฟล์ > ทำสำเนา เมื่อคัดลอกสเปรดชีต ระบบจะคัดลอกสคริปต์ที่แนบมากับสเปรดชีตนั้นด้วย ทุกคนที่มีสิทธิ์เข้าถึงสเปรดชีตจะคัดลอกสคริปต์ได้ (ผู้ทำงานร่วมกันที่มีสิทธิ์เข้าถึงแบบดูอย่างเดียวจะเปิดเครื่องมือแก้ไขสคริปต์ ในสเปรดชีตต้นฉบับไม่ได้ แต่เมื่อทำสำเนาแล้ว ผู้ใช้จะกลายเป็น เจ้าของสำเนาและดูสคริปต์ได้)
• เผยแพร่สคริปต์เป็นส่วนเสริมของเอดิเตอร์ในชีต

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

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