5 ฟังก์ชัน Google Apps Script ที่คุณควรทราบใน Google Sheets

Google Sheets เป็นเครื่องมือสเปรดชีตบนคลาวด์ที่ทรงพลัง ทำให้คุณสามารถทำงานเกือบทุกอย่างที่ทำได้ใน Microsoft Excel แต่พลังจริงของ Google Sheets อยู่ที่ฟีเจอร์ Google Apps Script ที่มาพร้อมกัน

Google Apps Script เป็นเครื่องมือสคริปต์เบื้องหลังที่ทำงานไม่ได้แค่ใน Google Sheets เท่านั้น แต่ยังรองรับ Google Docs, Gmail, Google Analytics และบริการคลาวด์ของ Google อื่นๆ อีกมากมาย มันช่วยให้คุณทำงานอัตโนมัติในแอปแต่ละตัว และเชื่อมต่อแอปเหล่านั้นเข้าด้วยกันได้

วิธีสร้าง Google Apps Script

คุณสามารถเริ่มสร้าง Google Apps Script แรกได้ทันทีจากภายใน Google Sheets

ทำได้โดยเลือกเมนู Tools แล้วเลือก Script Editor

จะเปิดหน้าต่าง Script Editor ขึ้นมา และมีฟังก์ชันเริ่มต้นชื่อ myFunction() นี่คือที่ที่คุณจะเขียนและทดสอบ Google Script ของคุณ

ลองสร้างฟังก์ชันที่อ่านข้อมูลจากเซลล์หนึ่ง ทำการคำนวณ แล้วเขียนผลลัพธ์ไปยังเซลล์อื่น ฟังก์ชันสำหรับดึงข้อมูลจากเซลล์คือ getRange() และ getValue() โดยระบุแถวและคอลัมน์ เช่น หากมีค่าอยู่ที่แถว 2 คอลัมน์ 1 (คอลัมน์ A) ส่วนแรกของสคริปต์จะเป็นดังนี้:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row = 2;
  var col = 1;
  var data = sheet.getRange(row, col).getValue();
}

โค้ดนี้จะเก็บค่าจากเซลล์นั้นไว้ในตัวแปร data คุณสามารถคำนวณต่อได้ และเขียนผลลัพธ์ไปยังเซลล์อื่น ดังนี้:

var results = data * 100;
sheet.getRange(row, col+1).setValue(results);
}

เมื่อเขียนเสร็จ ให้กดไอคอนดิสก์เพื่อบันทึก

ครั้งแรกที่รันสคริปต์ (โดยกดไอคอนรัน) คุณจะต้องอนุมัติสิทธิ์ให้สคริปต์ทำงานบนบัญชี Google ของคุณ

อนุญาตสิทธิ์เพื่อดำเนินต่อไป เมื่อสคริปต์ทำงานเสร็จ คุณจะเห็นผลการคำนวณปรากฏในเซลล์เป้าหมาย

ตอนนี้คุณรู้พื้นฐานแล้ว มาดูฟังก์ชันขั้นสูงกัน

ใช้ getValues เพื่อโหลดข้อมูลเป็นอาร์เรย์

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

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();

ตัวแปร data เป็นอาร์เรย์หลายมิติที่เก็บข้อมูลทั้งหมดในชีท ในการคำนวณ ให้ใช้ลูป for ตัวนับจะวนผ่านแต่ละแถว ส่วนคอลัมน์จะคงที่ตามคอลัมน์ที่ต้องการดึงข้อมูล

ในตัวอย่าง สามารถคำนวณข้อมูล 3 แถวได้ดังนี้:

for (var i = 1; i < data.length; i++) {
  var result = data[i][0] * 100;
  sheet.getRange(i+1, 2).setValue(result);
}
}

บันทึกและรันสคริปต์ คุณจะเห็นผลลัพธ์เติมเต็มคอลัมน์ 2 ทั้งหมด

สังเกตว่าการอ้างอิงเซลล์ในอาร์เรย์ต่างจาก getRange:

• data[i][0] หมายถึงมิติของอาร์เรย์ มิติแรกคือแถว มิติที่สองคือคอลัมน์ ทั้งคู่เริ่มจากศูนย์
• getRange(i+1, 2) หมายถึงแถวที่ 2 เมื่อ i=1 (เพราะแถว 1 เป็นหัวตาราง) และ 2 คือคอลัมน์ที่ 2 สำหรับเก็บผลลัพธ์

ใช้ appendRow เพื่อเขียนผลลัพธ์

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

ตัวอย่าง: สร้างฟังก์ชันนับ 1 ถึง 10 และแสดงตัวคูณ 2 ในคอลัมน์ Counter

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var i = 1; i<11; i++) {
    var result = i * 2;
    sheet.appendRow([i,result]);
  }
}

ประมวลผล RSS Feed ด้วย URLFetchApp

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

นี่คือวิธี DIY สร้าง RSS feed reader ของตัวเองในสเปรดชีท!

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var item, date, title, link, desc;
  var txt = UrlFetchApp.fetch("https://www.topsecretwriters.com/rss").getContentText();
  var doc = Xml.parse(txt, false);
  title = doc.getElement().getElement("channel").getElement("title").getText();
  var items = doc.getElement().getElement("channel").getElements("item");
  // วนลูปแต่ละไอเทมใน RSS Feed
  for (var i in items) {
    item = items[i];
    title = item.getElement("title").getText();
    link = item.getElement("link").getText();
    date = item.getElement("pubDate").getText();
    desc = item.getElement("description").getText();
    sheet.appendRow([title,link,date,desc]);
  }
}

Xml.parse จะแยกแต่ละไอเทมจาก RSS feed และแยกเป็น title, link, date และ description แล้วใช้ appendRow ใส่ลงคอลัมน์ที่เหมาะสม

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

รวมสตริงและเพิ่ม Carriage Return

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

ใต้สคริปต์ส่วนก่อนหน้า ให้เพิ่มโค้ดสกัดข้อมูลทั้งหมดในสเปรดชีท สร้างหัวข้ออีเมลและเนื้อหาโดยรวมข้อมูลจากอาร์เรย์ items เดียวกัน

ประกาศตัวแปร subject และ message ก่อนลูป for ของ items:

var subject = 'Latest 10 articles published at mysite.com';
var message = '';

ท้ายลูป for (หลัง appendRow) ให้เพิ่มบรรทัดนี้:

message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';

เครื่องหมาย + จะต่อสตริงทั้ง 4 ตัวพร้อม \n สำหรับขึ้นบรรทัดใหม่หลังแต่ละบรรทัด ท้ายบล็อกข้อมูลแต่ละ title ให้เว้น 2 บรรทัดเพื่ออ่านง่าย

เมื่อประมวลผลทุกแถวเสร็จ ตัวแปร message จะเก็บข้อความอีเมลทั้งหมด พร้อมส่งอีเมล!

วิธีส่งอีเมลใน Google Apps Script

ส่วนต่อไปคือส่ง subject และ body ผ่านอีเมล ทำได้ง่ายมากใน Google Script

var emailAddress = 'myemail@gmail.com';
MailApp.sendEmail(emailAddress, subject, message);

คลาส MailApp ให้เข้าถึงบริการอีเมลของบัญชี Google ได้สะดวก ฟังก์ชัน sendEmail เพียงบรรทัดเดียวก็ส่งอีเมลได้ โดยต้องการแค่ที่อยู่อีเมล หัวข้อ และเนื้อหา

การรวมความสามารถดึง RSS feed เก็บใน Google Sheets และส่งอีเมลพร้อมลิงก์ ทำให้ติดตามเนื้อหาล่าสุดของเว็บไซต์ใดๆ ได้สะดวกมาก

นี่เป็นเพียงตัวอย่างหนึ่งของพลัง Google Apps Script ในการทำงานอัตโนมัติและผสานบริการคลาวด์หลายตัวเข้าด้วยกัน