알아야 할 5가지 Google 스프레드시트 스크립트 기능

Google 스프레드시트는 Microsoft Excel에서 할 수 있는 거의 모든 작업을 수행할 수 있는 강력한 클라우드 기반 스프레드시트 도구입니다. 하지만 Google 스프레드시트의 진정한 힘은 함께 제공되는 Google 스크립팅 기능입니다.

Google Apps 스크립팅은 Google 스프레드시트뿐만 아니라 Google 문서, Gmail, Google 애널리틱스 및 거의 모든 기타 Google 클라우드 서비스에서도 작동하는 백그라운드 스크립팅 도구입니다. 개별 앱을 자동화하고 각 앱을 서로 통합할 수 있습니다.

이 문서에서는 Google Apps 스크립팅을 시작하는 방법, Google 스프레드시트에서 기본 스크립트를 생성하여 셀 데이터를 읽고 쓰는 방법, 가장 효과적인 고급 Google 스프레드시트 스크립트 기능을 배웁니다.

Google Apps 스크립트를 만드는 방법

Google 스프레드시트 내에서 첫 번째 Google Apps 스크립트를 지금 바로 시작할 수 있습니다.

이렇게 하려면 도구를 선택하세요. 메뉴에서 스크립트 편집기 .

그러면 스크립트 편집기 창이 열리고 기본적으로 myfunction()이라는 함수가 사용됩니다. . 여기에서 Google 스크립트를 만들고 테스트할 수 있습니다.

한 번 시도해 보려면 한 셀에서 데이터를 읽고 계산을 수행하고 데이터 양을 다른 셀로 출력하는 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를 사용하여 배열 로드

배열을 사용하여 스크립팅을 통해 스프레드시트의 데이터에 대한 계산을 수행하는 개념을 새로운 수준으로 끌어올릴 수 있습니다. getValues를 사용하여 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); 
   }
}

위에서 했던 것처럼 이 스크립트를 저장하고 실행합니다. 모든 결과가 스프레드시트의 2열에 채워진 것을 볼 수 있습니다.

배열 변수에서 셀과 행을 참조하는 것은 getRange 함수를 참조하는 것과 다릅니다.

데이터[i][0] 첫 번째 차원이 행이고 두 번째 차원이 열인 배열 차원을 나타냅니다. 둘 다 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]);
   }
}

다음은 이 기능을 실행했을 때의 결과입니다.

URLFetchApp으로 RSS 피드 처리

이전 Google 스프레드시트 스크립트 기능과 URLFetchApp을 결합할 수 있습니다. 웹사이트에서 RSS 피드를 가져오고 해당 웹사이트에 최근에 게시된 모든 기사에 대해 스프레드시트에 행을 작성합니다.

이것은 기본적으로 자신만의 RSS 피드 리더 스프레드시트를 만드는 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 RSS 피드에서 각 항목을 가져오고 각 줄을 제목, 링크, 날짜 및 설명으로 구분합니다.

appendRow 사용 기능을 사용하면 이러한 항목을 RSS 피드의 모든 단일 항목에 대해 적절한 열에 넣을 수 있습니다.

시트의 출력은 다음과 같습니다.

RSS 피드 URL을 스크립트에 포함하는 대신 시트에 URL이 있는 필드를 만든 다음 모니터링하려는 모든 웹사이트에 대해 하나씩 여러 시트를 가질 수 있습니다.

문자열 연결 및 캐리지 리턴 추가

일부 텍스트 조작 기능을 추가하여 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';

'+' 기호는 4개의 항목을 모두 연결하고 각 줄 뒤에 캐리지 리턴을 위해 '\n'을 붙입니다. 각 제목 데이터 블록의 끝에서 멋진 형식의 이메일 본문에 대해 두 개의 캐리지 리턴이 필요합니다.

모든 행이 처리되면 "본문" 변수에 전체 이메일 메시지 문자열이 저장됩니다. 이제 이메일을 보낼 준비가 되었습니다!

Google Apps Script에서 이메일을 보내는 방법

Google 스크립트의 다음 섹션은 이메일을 통해 "제목"과 "본문"을 보내는 것입니다. Google Script에서 이 작업을 수행하는 것은 매우 쉽습니다.

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

MailApp은 Google 계정의 이메일 서비스에 액세스하여 이메일을 보내거나 받을 수 있도록 하는 Google Apps 스크립트 내의 매우 편리한 클래스입니다. 덕분에 sendEmail 기능이 있는 한 줄로 이메일 주소, 제목, 본문만 있으면 어떤 이메일이든 보낼 수 있습니다.

결과 이메일은 다음과 같습니다.

웹사이트의 RSS 피드를 추출하여 Google 시트에 저장하고 URL 링크를 포함하여 자신에게 보내는 기능을 결합하면 모든 웹사이트의 최신 콘텐츠를 매우 편리하게 팔로우할 수 있습니다.

이는 작업을 자동화하고 여러 클라우드 서비스를 통합하기 위해 Google Apps 스크립트에서 사용할 수 있는 기능의 한 예일 뿐입니다.