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

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

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

วิธีสร้างสคริปต์ Google Apps^{(How to Create a Google Apps Script)}

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

ในการดำเนินการนี้ ให้เลือกเครื่องมือ^(Tools)จากเมนู จากนั้นเลือกScript Editor^{(Script Editor)}

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

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

ฟังก์ชันเพื่อรับข้อมูลจากเซลล์คือฟังก์ชัน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 ชีต^{(Google Sheets)} ใหม่ เช่นนี้ (โดยการเลือกไอคอนเรียกใช้) คุณจะต้องให้การอนุญาต^{(Authorization)}สำหรับสคริปต์เพื่อทำงานในบัญชี Google^{(Google Account)}ของคุณ

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

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

ใช้ getValues ​​เพื่อโหลดอาร์เรย์^{(Use getValues To Load Arrays)}

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

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

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

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

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

บันทึก^(Save)และเรียกใช้สคริปต์นี้เหมือนกับที่คุณทำด้านบน คุณจะเห็นว่าผลลัพธ์ทั้งหมดอยู่ในคอลัมน์ 2 ในสเปรดชีตของคุณ

คุณจะสังเกตเห็นว่าการอ้างอิงเซลล์และแถวในตัวแปรอาร์เรย์นั้นแตกต่างจากฟังก์ชัน getRange 

data[i][0]หมายถึงมิติอาร์เรย์ที่มิติแรกคือแถวและส่วนที่สองคือคอลัมน์ ทั้งสองสิ่งนี้เริ่มต้นที่ศูนย์

getRange(i+1, 2)หมายถึงแถวที่สองเมื่อ i=1 (เนื่องจากแถวที่ 1 เป็นส่วนหัว) และ 2 คือคอลัมน์ที่สองที่เก็บผลลัพธ์

ใช้ appendRow เพื่อเขียนผลลัพธ์^{(Use appendRow To Write Results)}

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

ซึ่งทำได้ง่ายด้วยฟังก์ชัน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 ด้วย URLFetchApp^{(Process RSS Feeds With URLFetchApp)}

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

นี่เป็น วิธีการ DIYเพื่อสร้างสเปรดชีตโปรแกรมอ่านฟีดRSS ของคุณเอง!^(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และแยกแต่ละบรรทัดออกเป็นชื่อ ลิงก์ วันที่ และคำอธิบาย 

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

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

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

^{(Concatenate Strings)}เชื่อม ต่อสตริงและเพิ่ม^(Add)Carriage Return

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

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

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

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

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';

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

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

วิธีส่งอีเมลใน Google Apps Script^{(How To Send Email In Google Apps Script)}

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

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

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

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

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

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

5 Google Sheets Script Functions You Need to Know

Goоgle Sheеts is a powerful cloud-based spreadsheet tool that lеts you do nearly everything you could do in Micrоsoft Excel. But the real power of Google Sheets is the Google Scripting feature that comes with it.

Google Apps scripting is a background scripting tool that works not only in Google Sheets but also Google Docs, Gmail, Google Analytics, and nearly every other Google cloud service. It lets you automate those individual apps, and integrate each of those apps with each other.

In this article you’ll learn how to get started with Google Apps scripting, creating a basic script in Google Sheets to read and write cell data, and the most effective advanced Google Sheets script functions.

How to Create a Google Apps Script

You can get started right now creating your first Google Apps script from inside Google Sheets. 

To do this, select Tools from the menu, then Script Editor.

This opens the script editor window and defaults to a function called myfunction(). This is where you can create and test your Google Script.

To give it a shot, try creating a Google Sheets script function that will read data from one cell, perform a calculation on it, and output the data amount to another cell.

The function to get data from a cell is the getRange() and getValue() functions. You can identify the cell by row and column. So if you have a value in row 2 and column 1 (the A column), the first part of your script will look like this:

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

This stores the value from that cell in the data variable. You can perform a calculation on the data, and then write that data to another cell. So the last part of this function will be:

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

When you’re done writing your function, select the disk icon to save. 

The first time you run a new Google Sheets script function like this (by selecting the run icon), you’ll need to provide Authorization for the script to run on your Google Account.

Allow permissions to continue. Once your script runs, you’ll see that the script wrote the calculation results to the target cell.

Now that you know how to write a basic Google Apps script function, let’s take a look at some more advanced functions.

Use getValues To Load Arrays

You can take the concept of doing calculations on data in your spreadsheet with scripting to a new level by using arrays. If you load a variable in Google Apps script using getValues, the variable will be an array that can load multiple values from the sheet.

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

The data variable is a multi-dimensional array that holds all of the data from the sheet. To perform a calculation on the data, you use a for loop. The counter of the for loop will work through each row, and the column remains constant, based on the column where you want to pull the data.

In our example spreadsheet, you can perform calculations on the three rows of data as follows.

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

Save and run this script just as you did above. You’ll see that all of the results are filled into column 2 in your spreadsheet.

You’ll notice that referencing a cell and row in an array variable is different than with a getRange function. 

data[i][0] refers to the array dimensions where the first dimension is the row and the second is the column. Both of these start at zero.

getRange(i+1, 2) refers to the second row when i=1 (since row 1 is the header), and 2 is the second column where the results are stored.

Use appendRow To Write Results

What if you have a spreadsheet where you want to write data into a new row instead of a new column?

This is easy to do with the appendRow function. This function won’t bother any existing data in the sheet. It’ll just append a new row to the existing sheet.

As an example, make a function that will count from 1 to 10 and show a counter with multiples of 2 in a Counter column.

This function would look like this:

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

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

Here are the results when you run this function.

Process RSS Feeds With URLFetchApp

You could combine the previous Google Sheets script function and the URLFetchApp to pull the RSS feed from any website, and write a row to a spreadsheet for every article recently published to that website.

This is basically a DIY method to create your own RSS feed reader spreadsheet!

The script to do this isn’t too complicated either.

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]);
}
}

As you can see, Xml.parse pulls each item out of the RSS feed and separates each line into the title, link, date and description. 

Using the appendRow function, you can put these items into appropriate columns for every single item in the RSS feed.

The output in your sheet will look something like this:

Instead of embedding the RSS feed URL into the script, you could have a field in your sheet with the URL, and then have multiple sheets – one for every website you want to monitor.

Concatenate Strings and Add a Carriage Return

You could take the RSS spreadsheet a step further by adding some text manipulation functions, and then use email functions to send yourself an email with a summary of all new posts in the site’s RSS feed.

To do this, under the script you created in the previous section, you’ll want to add some scripting that will extract all of the information in the spreadsheet. 

You’ll want to build the subject line and the email text body by parsing together all of the information from the same “items” array that you used to write the RSS data to the spreadsheet. 

To do this, initialize the subject and message by placing the following lines before the “items” For loop.

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

Then, at the end of the “items” for loop (right after the appendRow function), add the following line.

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

The “+” symbol  will concatenate all four items together followed by “\n” for a carriage return after each line. At the end of each title data block, you’ll want two carriage returns for a nicely formatted email body.

Once all rows are processed, the “body” variable holds the entire email message string. Now you’re ready to send the email!

How To Send Email In Google Apps Script

The next section of your Google Script will be to send the “subject” and the “body” via email. Doing this in Google Script is very easy.

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

The MailApp is a very convenient class inside of Google Apps scripts that gives you access to your Google Account’s email service to send or receive emails. Thanks to this, the single line with the sendEmail function lets you send any email with just the email address, subject line, and body text.

This is what the resulting email will look like. 

Combining the ability to extract a website’s RSS feed, store it in a Google Sheet, and send it to yourself with URL links included, makes it very convenient to follow the latest content for any website.

This is just one example of the power that’s available in Google Apps scripts to automate actions and integrate multiple cloud services.

Related posts

• 4 วิธีในการแปลง Excel เป็น Google ชีต

• Google ชีตกับ Microsoft Excel - อะไรคือความแตกต่าง?

• วิธีลบขอบตารางใน Google เอกสาร

• Google Docs Chat ช่วยให้คุณทำงานร่วมกันบนเอกสารได้อย่างไร

• วิธีใส่ลายน้ำใน Word และ Google Docs

• วิธีใช้การวิเคราะห์แบบ What-If ของ Excel

• ทำให้ OpenOffice Writer มีลักษณะและฟังก์ชันเหมือน Microsoft Word

• เปลี่ยนเป็นการวางแนวแนวนอนใน Google เอกสาร

• Microsoft 365 คืออะไร?

• วิธีเพิ่มหมายเลขสไลด์ในงานนำเสนอ PowerPoint

• วิธีการกู้คืนอีเมลที่ถูกลบใน Office 365

• วิธีการเลเยอร์วัตถุในการนำเสนอ PowerPoint

• วิธีสร้างเอกสาร PDF ใน Microsoft Office

• วิธีลบหน้าใน Microsoft Word

• วิธีแก้ไขข้อผิดพลาดที่คั่นหน้าไม่ได้กำหนดไว้ใน Word

• วิธีการเปลี่ยนขนาดสไลด์ใน PowerPoint เพื่อการนำเสนอที่ดีขึ้น

• วิธีสร้างมาโครหรือสคริปต์ VBA ใน Excel

• รับสถิติอีเมล Outlook โดยใช้ StatView

• วิธีใช้ฟีเจอร์ Speak Cells ของ Excel

• 7 ทางเลือกแทน PowerPoint ที่คุณสามารถใช้ได้ออนไลน์