Google 스프레드시트 는 (Google Sheets)Microsoft Excel 에서 할 수 있는 거의 모든 작업을 수행할 수 있는 강력한 클라우드 기반 스프레드시트 도구입니다 . 그러나 Google 스프레드시트(Google Sheets) 의 진정한 힘 은 함께 제공 되는 Google 스크립팅(Google Scripting) 기능입니다.
Google Apps 스크립팅은 (Google Apps)Google 스프레드시트(in Google Sheets) 뿐만 아니라 Google 문서도구, Gmail, Google 애널리틱스(Google Analytics) 및 거의 모든 기타 Google 클라우드 서비스 에서도 작동하는 백그라운드 스크립팅 도구입니다 . 개별 앱을 자동화하고 각 앱을 서로 통합할 수 있습니다.
이 문서에서는 Google Apps(Google Apps) 스크립팅 을 시작하는 방법 , Google 스프레드시트(Google Sheets) 에서 기본 스크립트를 생성하여 셀 데이터를 읽고 쓰는 방법, 가장 효과적인 고급 Google 스프레드시트(Google Sheets) 스크립트 기능을 학습합니다.
Google Apps 스크립트를 만드는 방법(How to Create a Google Apps Script)
Google 스프레드시트 내에서 첫 번째 (Google Sheets)Google Apps 스크립트 만들기를 지금 바로 시작할 수 있습니다 .
이렇게 하려면 메뉴에서 도구(Tools) 를 선택한 다음 스크립트 편집기(Script Editor) 를 선택 합니다.
그러면 스크립트 편집기 창이 열리고 기본적으로 myfunction() 이라는 함수가 사용 됩니다. 여기에서 Google Script(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) 스크립트 기능 을 처음 실행할 때 Google 계정(Google Account) 에서 실행할 스크립트에 대한 승인(Authorization) 을 제공해야 합니다 .
계속하려면 권한을 허용하세요. 스크립트가 실행되면 스크립트가 계산 결과를 대상 셀에 쓴 것을 볼 수 있습니다.
기본 Google Apps(Google Apps) 스크립트 기능 을 작성하는 방법을 알았 으므로 이제 몇 가지 고급 기능을 살펴보겠습니다.
getValues를 사용하여 배열 로드(Use getValues To Load Arrays)
배열을 사용하여 스크립팅을 통해 스프레드시트의 데이터에 대한 계산을 수행하는 개념을 새로운 수준으로 끌어올릴 수 있습니다. getValues를 사용하여 Google Apps(Google Apps) 스크립트 에서 변수를 로드하는 경우 변수는 시트에서 여러 값을 로드할 수 있는 배열이 됩니다.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
데이터 변수는 시트의 모든 데이터를 보유하는 다차원 배열입니다. 데이터에 대한 계산을 수행하려면 for 루프를 사용합니다. for 루프의 카운터는 각 행에서 작동하며 데이터를 가져오려는 열을 기반으로 열이 일정하게 유지됩니다.
예제 스프레드시트에서 다음과 같이 3개의 데이터 행에 대해 계산을 수행할 수 있습니다.
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] 은 첫 번째 차원이 행이고 두 번째 차원이 열인 배열 차원을 나타냅니다. 둘 다 0에서 시작합니다.
getRange(i+1, 2) 는 i=1일 때 두 번째 행을 참조하고(1행이 헤더이므로) 2는 결과가 저장되는 두 번째 열입니다.
appendRow를 사용하여 결과 쓰기(Use appendRow To Write Results)
새 열 대신 새 행에 데이터를 쓰려는 스프레드시트가 있는 경우 어떻게 하시겠습니까?
이는 appendRow 함수로 쉽게 수행할 수 있습니다. 이 기능은 시트의 기존 데이터를 방해하지 않습니다. 기존 시트에 새 행을 추가하기만 하면 됩니다.
예를 들어, 1에서 10까지 세고 카운터(Counter) 열에 2의 배수가 있는 카운터를 표시하는 함수를 만듭니다 .
이 함수는 다음과 같습니다.
function myFunction() {
var sheet = SpreadsheetApp.getActiveSheet();
for (var i = 1; i<11; i++) {
var result = i * 2;
sheet.appendRow([i,result]);
}
}
다음은 이 기능을 실행할 때의 결과입니다.
URLFetchApp으로 RSS 피드 처리(Process RSS Feeds With URLFetchApp)
이전 Google 스프레드시트(Google Sheets) 스크립트 기능과 URLFetchApp 을 결합하여 모든 웹사이트에서 (URLFetchApp)RSS 피드 를 가져 오고 해당 웹사이트에 최근에 게시된 모든 기사에 대해 스프레드시트에 행을 작성할 수 있습니다.
이것은 기본적으로 자신의 RSS 피드 리더 스프레드시트 를 만드는 DIY 방법입니다!(DIY)
이 작업을 수행하는 스크립트도 너무 복잡하지 않습니다.
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 는 (Xml.parse)RSS 피드 에서 각 항목을 가져 오고 각 줄을 제목, 링크, 날짜 및 설명으로 구분합니다.
appendRow 함수를 사용하여 RSS 피드 의 모든 단일 항목에 대해 이러한 항목을 적절한 열에 넣을 수 있습니다 .
시트의 출력은 다음과 같습니다.
RSS 피드 URL 을 스크립트 에 포함하는 대신 URL 이 있는 시트에 필드를 만든 다음 모니터링하려는 모든 웹 사이트에 대해 하나씩 여러 시트를 가질 수 있습니다.
문자열 연결(Concatenate Strings) 및 캐리지(Carriage Return) 리턴 추가(Add)
일부 텍스트 조작 기능을 추가 하여 RSS 스프레드시트를 한 단계 더 발전시킨 다음 이메일 기능을 사용하여 사이트의 RSS 피드에 있는 모든 새 게시물의 요약이 포함된 이메일을 자신에게 보낼 수 있습니다.
이렇게 하려면 이전 섹션에서 만든 스크립트 아래에 스프레드시트의 모든 정보를 추출하는 일부 스크립팅을 추가해야 합니다.
RSS 데이터를 스프레드시트 에 쓰는 데 사용한 동일한 "항목" 배열의 모든 정보를 함께 구문 분석하여 제목 줄과 이메일 텍스트 본문을 작성하려고 할 것 입니다.
이렇게 하려면 "items" For 루프 앞에 다음 줄을 배치하여 제목과 메시지를 초기화합니다.
var subject = ‘Latest 10 articles published at mysite.com’
var message = ‘’
그런 다음 "items" for 루프의 끝에(appendRow 함수 바로 뒤) 다음 줄을 추가합니다.
message = message + title + '\n' + link + '\n' + date + '\n' + desc + '\n' + '\n \n';
"+" 기호는 네 항목을 모두 연결하고 각 줄 뒤에 캐리지 리턴을 위해 " "를 표시합니다. 각 제목 데이터 블록의 끝에서 멋진 형식의 이메일 본문에 대해 두 개의 캐리지 리턴이 필요합니다.
모든 행이 처리되면 "본문" 변수에 전체 이메일 메시지 문자열이 저장됩니다. 이제 이메일을 보낼 준비가 되었습니다!
Google Apps Script에서 이메일을 보내는 방법(How To Send Email In Google Apps Script)
Google 스크립트(Google Script) 의 다음 섹션은 이메일을 통해 "제목"과 "본문"을 보내는 것입니다. Google Script 에서 이 작업을 수행하는 것은 매우 쉽습니다.
var emailAddress = [email protected];
MailApp.sendEmail(emailAddress, subject, message);
MailApp 은 Google 계정의 이메일 서비스에 액세스하여 이메일을 보내거나 받을 수 있도록 하는 Google Apps 스크립트 내의 매우 편리한 클래스 입니다. 덕분에 sendEmail 기능이 있는 한 줄로 이메일 주소, 제목, 본문만 있으면 어떤 이메일이든 보낼 수 있습니다.(send any email)
결과 이메일은 다음과 같습니다.
웹사이트의 RSS 피드 를 추출하여 Google 시트 에 저장하고 (Google Sheet)URL 링크를 포함 하여 자신에게 보내는 기능을 결합 하면 모든 웹사이트의 최신 콘텐츠를 매우 편리하게 팔로우할 수 있습니다.
이는 작업을 자동화하고 여러 클라우드 서비스를 통합하기 위해 Google Apps 스크립트에서 사용할 수 있는 기능의 한 예일 뿐입니다.(Google Apps)
5 Google Sheets Script Functions You Need to Know
Google Sheets is a powerfυl cloud-based spreаdsheet tool that lets you do nearly everything you could dо in Microsoft 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.
