ฟังก์ชันที่กำหนดเองใน 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 ชีต วิธีใช้หรือสำรวจส่วนเสริมเหล่านี้

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

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

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

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

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