初心者でもわかる!知っておくべきGoogleスプレッドシートのスクリプト関数5選

Googleスプレッドシートは、Microsoft Excelでできることのほぼすべてをクラウド上で実現できる強力な表計算ツールです。しかし、その真価を発揮するのは、標準搭載されている「Google Apps Script(スクリプト機能)」です。

Google Apps Scriptはバックグラウンドで動作するスクリプト環境で、Googleスプレッドシートだけでなく、Googleドキュメント、Gmail、Googleアナリティクスなど、ほぼすべてのGoogleクラウドサービスで利用できます。個々のアプリを自動化できるだけでなく、アプリ同士を連携させることも可能です。

この記事では、Google Apps Scriptの始め方から、セルデータの読み書きを行う基本スクリプトの作成方法、そして実務で役立つ高度なスクリプト関数までを解説します。

Google Apps Scriptの作成方法

Googleスプレッドシートからすぐに最初のスクリプトを作成できます。メニューの「拡張機能」から「Apps Script(スクリプトエディタ)」を選択してください。

スクリプトエディタが開き、デフォルトでmyFunction()という関数が表示されます。ここが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();
}

これでセルの値がdata変数に格納されます。このデータに対して計算を行い、結果を別のセルに書き込むには、関数の後半を次のようにします。

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

関数の記述が完了したら、フロッピーディスクのアイコンを選択して保存しましょう。

このような新しいスクリプトを初めて実行する際(実行アイコンを選択)は、自分のGoogleアカウントでスクリプトを実行するための承認が必要です。

権限を許可して続行します。スクリプトが実行されると、計算結果が対象のセルに書き込まれているのが確認できます。

基本的なGoogle Apps Scriptの書き方がわかったところで、次はより高度な関数を見ていきましょう。

getValuesで配列を読み込む

配列を使うことで、スプレッドシートのデータに対する計算処理をさらに高度化できます。Google Apps ScriptでgetValuesを使って変数を読み込むと、その変数はシートから複数の値を格納できる配列になります。

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

data変数は、シート上の全データを保持する多次元配列です。このデータに対して計算を行うには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関数とは異なる点です。

data[i][0]は配列の次元を指しており、最初の次元が行、2番目が列を表します。どちらも0から始まります。

一方、getRange(i+1, 2)は、i=1のとき2行目を指します(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]);
   }
}

この関数を実行した結果がこちらです。

URLFetchAppでRSSフィードを処理する

先ほどのスクリプト関数とURLFetchAppを組み合わせると、任意のWebサイトからRSSフィードを取得し、そのサイトに最近公開された記事ごとにスプレッドシートへ1行ずつ書き込むことができます。

これはいわば、自作の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");   

// RSSフィード内の各アイテムを解析

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用のフィールドを設ければ、監視したいWebサイトごとに複数のシートを持たせることも可能です。

文字列の連結と改行の追加

RSSスプレッドシートにテキスト操作関数を追加し、さらにメール送信機能を組み合わせれば、サイトのRSSフィードにある新着記事のまとめを自分宛てにメールで送ることもできます。

そのために、前のセクションで作成したスクリプトの下に、スプレッドシート内の全情報を抽出するスクリプトを追加します。

件名とメール本文は、RSSデータをスプレッドシートに書き込む際に使用したのと同じ「items」配列から情報を組み立てて作成します。

具体的には、「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」が付加されます。各記事データブロックの末尾には改行を2つ入れることで、見やすい整形されたメール本文になります。

すべての行の処理が完了すると、「body」変数にメール本文全体の文字列が格納されます。これでメールを送信する準備が整いました!

Google Apps Scriptでメールを送信する方法

スクリプトの次のパートでは、「subject(件名)」と「body(本文)」をメールで送信します。Google Apps Scriptでのメール送信は非常に簡単です。

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

MailAppはGoogle Apps Scriptに用意された非常に便利なクラスで、Googleアカウントのメールサービスにアクセスして、メールの送受信が行えます。おかげで、sendEmail関数の1行だけで、メールアドレス・件名・本文を指定して任意のメールを送信できます。

実際に届いたメールはこのようになります。

WebサイトのRSSフィードを抽出し、それをGoogleスプレッドシートに保存して、リンク付きで自分宛てに送信する——この一連の流れを組み合わせれば、あらゆるサイトの最新コンテンツを簡単にフォローできます。

これは、Google Apps Scriptを使ってアクションを自動化し、複数のクラウドサービスを統合できる可能性のほんの一例にすぎません。ぜひあなた自身のアイデアで、業務の自動化に役立ててみてください。