知っておくべき5つのGoogleスプレッドシートスクリプト関数

Googleスプレッドシートは、MicrosoftExcelで実行できるほぼすべてのことを実行できる強力なクラウドベースのスプレッドシートツールです。しかし、Googleスプレッドシートの真の力は、それに付属するGoogleScripting機能です。

Google Appsスクリプティングは、Googleスプレッドシートだけでなく、Googleドキュメント、Gmail、Google Analytics、およびその他のほぼすべてのGoogleクラウドサービスでも機能するバックグラウンドスクリプティングツールです。これにより、これらの個々のアプリを自動化し、それらの各アプリを相互に統合できます。

この記事では、Google Apps Scriptingの使用を開始する方法、セルデータを読み書きするためのGoogle Sheetsでの基本的なスクリプトの作成、および最も効果的な高度なGoogleSheetsスクリプト機能について学習します。

GoogleAppsスクリプトを作成する方法

今すぐ、Googleスプレッドシート内から最初のGoogleAppsスクリプトの作成を開始できます。

これを行うには、ツールを選択します メニューから、次にスクリプトエディタ 。

これにより、スクリプトエディタウィンドウが開き、デフォルトで myfunction（）という関数になります。 。ここで、GoogleScriptを作成してテストできます。

試してみるには、あるセルからデータを読み取り、そのセルで計算を実行し、データ量を別のセルに出力する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アカウントで実行するための承認を提供する必要があります。

続行する権限を許可します。スクリプトを実行すると、スクリプトが計算結果をターゲットセルに書き込んだことがわかります。

基本的なGoogleAppsスクリプト関数の記述方法がわかったところで、さらに高度な関数を見てみましょう。

getValuesを使用して配列をロードする

配列を使用して、スクリプトを使用してスプレッドシート内のデータを計算するという概念を新しいレベルに引き上げることができます。 getValuesを使用してGoogleAppsスクリプトに変数を読み込むと、変数はシートから複数の値を読み込むことができる配列になります。

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] 配列の次元を指します。最初の次元は行で、2番目の次元は列です。これらは両方ともゼロから始まります。

getRange（i + 1、2） i =1の場合（行1がヘッダーであるため）、2番目の行を参照し、2は結果が格納される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を組み合わせることができます 任意のWebサイトからRSSフィードを取得し、そのWebサイトに最近公開されたすべての記事のスプレッドシートに行を書き込みます。

これは基本的に、独自の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を含むフィールドを作成し、監視するWebサイトごとに1つずつ、複数のシートを作成することができます。

文字列を連結してキャリッジリターンを追加する

いくつかのテキスト操作機能を追加して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」変数は電子メールメッセージ文字列全体を保持します。これで、メールを送信する準備が整いました。

GoogleAppsScriptでメールを送信する方法

Google Scriptの次のセクションでは、「件名」と「本文」をメールで送信します。 GoogleScriptでこれを行うのは非常に簡単です。

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

MailAppは、Google Appsスクリプト内の非常に便利なクラスであり、Googleアカウントのメールサービスにアクセスしてメールを送受信できます。このおかげで、sendEmail関数を使用した1行で、メールアドレス、件名、本文だけで任意のメールを送信できます。

結果の電子メールは次のようになります。

ウェブサイトのRSSフィードを抽出し、それをGoogleスプレッドシートに保存し、URLリンクを含めて自分自身に送信する機能を組み合わせると、あらゆるウェブサイトの最新コンテンツをフォローするのに非常に便利です。

これは、アクションを自動化し、複数のクラウドサービスを統合するためにGoogleAppsスクリプトで利用できる機能の一例にすぎません。