5 ฟังก์ชันสคริปต์ Google ชีตที่คุณต้องรู้

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

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

ในบทความนี้ คุณจะได้เรียนรู้วิธีเริ่มต้นใช้งานสคริปต์ Google Apps การสร้างสคริปต์พื้นฐานใน Google ชีตเพื่ออ่านและเขียนข้อมูลเซลล์ และฟังก์ชันขั้นสูงของสคริปต์ Google ชีตที่มีประสิทธิภาพสูงสุด

วิธีสร้างสคริปต์ Google Apps

คุณสามารถเริ่มต้นได้ทันทีโดยสร้างสคริปต์ Google Apps ตัวแรกจากภายใน Google ชีต

ในการดำเนินการนี้ ให้เลือก เครื่องมือ จากเมนู แล้วตามด้วย ตัวแก้ไขสคริปต์ .

ซึ่งจะเปิดหน้าต่างตัวแก้ไขสคริปต์และตั้งค่าเริ่มต้นเป็นฟังก์ชันที่เรียกว่า myfunction() . ที่นี่คุณสามารถสร้างและทดสอบ Google Script ของคุณได้

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

ฟังก์ชันในการรับข้อมูลจากเซลล์คือ getRange() และ getValue() ฟังก์ชั่น. คุณสามารถระบุเซลล์ตามแถวและคอลัมน์ ดังนั้นหากคุณมีค่าในแถวที่ 2 และคอลัมน์ที่ 1 (คอลัมน์ A) ส่วนแรกของสคริปต์ของคุณจะมีลักษณะดังนี้:

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

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

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

เมื่อคุณเขียนฟังก์ชันเสร็จแล้ว ให้เลือกไอคอนดิสก์เพื่อบันทึก

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

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

เมื่อคุณรู้วิธีเขียนฟังก์ชันสคริปต์พื้นฐานของ Google Apps แล้ว มาดูฟังก์ชันขั้นสูงเพิ่มเติมกันบ้าง

ใช้ getValues ​​เพื่อโหลดอาร์เรย์

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

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

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

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

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) หมายถึงแถวที่สองเมื่อ i=1 (เนื่องจากแถวที่ 1 เป็นส่วนหัว) และ 2 คือคอลัมน์ที่สองซึ่งเก็บผลลัพธ์ไว้

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

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

ทำได้ง่ายๆ ด้วย appendRow การทำงาน. ฟังก์ชันนี้จะไม่รบกวนข้อมูลที่มีอยู่ในแผ่นงาน มันจะต่อท้ายแถวใหม่กับแผ่นงานที่มีอยู่

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

ฟังก์ชันนี้จะมีลักษณะดังนี้:

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

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

นี่คือผลลัพธ์เมื่อคุณเรียกใช้ฟังก์ชันนี้

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

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

นี่เป็นวิธีการ DIY เพื่อสร้างสเปรดชีตโปรแกรมอ่านฟีด RSS ของคุณเอง!

สคริปต์ในการทำเช่นนี้ก็ไม่ซับซ้อนเกินไป

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");   

// Parsing single items in the 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 และแยกแต่ละบรรทัดออกเป็นชื่อ ลิงก์ วันที่ และคำอธิบาย

การใช้ ผนวกแถว คุณสามารถใส่รายการเหล่านี้ลงในคอลัมน์ที่เหมาะสมสำหรับทุกรายการในฟีด RSS

ผลลัพธ์ในชีตของคุณจะมีลักษณะดังนี้:

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

เชื่อมสตริงและเพิ่มการส่งคืนรถ

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

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

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

เมื่อต้องการทำเช่นนี้ ให้เริ่มต้นหัวเรื่องและข้อความโดยวางบรรทัดต่อไปนี้ก่อน "รายการ" สำหรับลูป

var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’

จากนั้น ที่ส่วนท้ายของ “items” for loop (หลังฟังก์ชัน appendRow) ให้เพิ่มบรรทัดต่อไปนี้

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

สัญลักษณ์ “+” จะเชื่อมทั้งสี่รายการเข้าด้วยกัน ตามด้วย “\n” สำหรับการขึ้นบรรทัดใหม่หลังแต่ละบรรทัด ในตอนท้ายของแต่ละช่องข้อมูลชื่อเรื่อง คุณจะต้องการขึ้นบรรทัดใหม่สองครั้งสำหรับเนื้อหาอีเมลที่มีรูปแบบสวยงาม

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

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

ส่วนถัดไปของ Google Script ของคุณคือส่ง "หัวเรื่อง" และ "เนื้อหา" ทางอีเมล การทำเช่นนี้ใน Google Script นั้นง่ายมาก

var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);

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

นี่คือสิ่งที่อีเมลผลลัพธ์จะมีลักษณะเช่นนี้

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

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