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

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

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

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

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

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

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

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

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

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

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

1. คลิกเซลล์ที่ต้องการใช้ฟังก์ชัน
2. พิมพ์เครื่องหมายเท่ากับ (=) ตามด้วยชื่อฟังก์ชันและค่าอินพุต เช่น =DOUBLE(A1) — แล้วกด Enter
3. เซลล์จะแสดง 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 วินาที ไม่เช่นนั้น ระบบจะ เซลล์จะแสดงข้อผิดพลาด: Internal error executing the custom function.

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

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 ชีต

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

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

ทุกครั้งที่มีการใช้ฟังก์ชันที่กำหนดเองในสเปรดชีต 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;
}

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