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

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

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

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... ชั่วครู่ จากนั้นจะแสดงผลลัพธ์

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

โปรดดูหลักเกณฑ์ต่อไปนี้ก่อนเขียนฟังก์ชันที่กําหนดเอง

การตั้งชื่อ

นอกเหนือจากรูปแบบมาตรฐานการตั้งชื่อฟังก์ชัน JavaScript แล้ว โปรดคำนึงถึงสิ่งต่อไปนี้ด้วย

• ชื่อของฟังก์ชันที่กำหนดเองต้องแตกต่างจากชื่อฟังก์ชันในตัว เช่น SUM()
• ชื่อของฟังก์ชันที่กำหนดเองต้องไม่ลงท้ายด้วยขีดล่าง (_) ซึ่งบ่งบอกถึงฟังก์ชันส่วนตัวในสคริปต์ของแอป
• ชื่อของฟังก์ชันที่กําหนดเองต้องประกาศด้วยไวยากรณ์ 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... อยู่เรื่อยๆ

แสดงผลค่า

ฟังก์ชันที่กําหนดเองทุกรายการต้องแสดงผลค่า เช่น

• หากฟังก์ชันที่กำหนดเองแสดงผลค่า ค่าดังกล่าวจะแสดงในเซลล์ที่มีการเรียกฟังก์ชันนั้น
• หากฟังก์ชันที่กำหนดเองแสดงผลอาร์เรย์ 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 บางรายการเพื่อดําเนินการที่ซับซ้อนมากขึ้น เช่น ฟังก์ชันที่กําหนดเองสามารถเรียกบริการภาษาเพื่อแปลวลีภาษาอังกฤษเป็นภาษาสเปน

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

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

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

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

ทุกครั้งที่มีการใช้ฟังก์ชันที่กำหนดเองในสเปรดชีต 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 เพื่อเรียกใช้ DOUBLE ในค่าทุกค่าในอาร์เรย์ 2 มิติของเซลล์แบบซ้ำ โดยจะแสดงอาร์เรย์ 2 มิติที่มีผลลัพธ์ วิธีนี้ช่วยให้คุณเรียกใช้ DOUBLE ได้เพียงครั้งเดียวแต่ให้คํานวณสําหรับเซลล์จํานวนมากพร้อมกันได้ ดังที่แสดงในภาพหน้าจอด้านล่าง (คุณทําสิ่งเดียวกันได้โดยใช้if stmt ที่ฝังแทนการเรียกใช้ 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;
}

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