Googleスクリプトを使って、スプレッドシートから自動でメールを送信する

写真拡大 (全7枚)

MakeUseOf:Googleスクリプトは、スプレッドシートやドキュメントといったGoogle製の生産性ツールを自動化できる、強力なツールです。さらにすごいのは、メールの送信を自動化できるのです。

たとえば、Googleスプレッドシートで自動計算した内容を毎月上司にメールで報告できたら良いと思いませんか? もしくは、部下ひとりひとりに対して、パフォーマンスの評価や業務の進捗状況などをメールで連絡するのに時間が掛かりすぎてうんざりしていませんか? 今回紹介するGoogleスクリプトの関数は、そうしたタスクや、それ以外の業務の遂行にも、きっと役立つでしょう。

1度だけ少し面倒な作業をする必要はありますが、それさえ済めば、あの煩わしいメールの手動送信を行う必要はなくなります。Googleスクリプトがあなたのパーソナルアシスタントとなり、すべての作業を代行してくれます。

Googleスプレッドシートでメールを送信する設定

Googleスプレッドシートからメールを送信できるようにGoogleスクリプトを設定するには、最初のステップとして、送信先の名前、メールアドレス、メッセージ本文のすべてを記入したGoogleスプレッドシートを適切に設定する必要があります。

原文の執筆者であるRyan Dubeさんの例では、Googleアナリティクスで公開された過去1カ月分の記事をすべて自動的にインポートするように設定したGoogleスプレッドシートから始めました。Dubeさんは、掲載された記事を筆者別に集計する「All Authors」という名のシートを作成しました。そしてその集計結果を各筆者にメールで送れるようにしたい、と。

まず、今開いているGoogleスプレッドシートに「Send-Emails」という名前の新規シートを追加して、これをメール専用シートにします。

170808send_email_google_scripts4
Image: MakeUseOf

このシートでは、ヘッダーを作成します。各行には、個々の送信先に対応するメール情報を記入していきます。この例では、受信者名、受信者のメールアドレス、そして、後でスクリプトでつなぎ合わせていくためのメッセージのパーツを入力しました。

170808send_email_google_scripts5
Image: MakeUseOf

静的情報と動的情報をつなぎ合わせるための列を足していけば、メールのサイズや構成は自在に変えられます。

170808send_email_google_scripts6
Image: MakeUseOf

ここでいう静的情報とは、セルに入力したただのテキストです。これは月が変わっても変わらず、ずっと同じものです。この部分は常にそのまま、メールのメッセージの一部として使われます。一方で動的情報である、毎月変えたいデータに対しては、同じGoogleスプレッドシート内にあるいずれかのシートから関連データをインポートするための関数を挿入できます。

170808send_email_google_scripts7
Image: MakeUseOf

ここでの目的は、自動作成されたレポートを見るためにシートを開いた時にはいつでもメニューアイテムが表示されるようにして、それをクリックするとメールを送信するためのGoogleスクリプトが実行され、集計結果が各筆者に配信されるようにすることです。この新規メニューアイテムのカスタマイズ方法については、記事の最後のほうで説明します。

このシートが完成すると、以下のようになります。

170808send_email_google_scripts1
Image: MakeUseOf

各筆者のメール情報をシートに入力したら、次はいよいよスクリプトの作成です。

メールの自動送信スクリプト

スクリプトを作成するには、スクリプトエディタを使う必要があります。スクリプトエディタを使用するには、「ツール」 > 「スクリプトエディタ」をクリックします。

スクリプトエディタを開くと、以下のような画面が表示されます。ここで、右側のペインの上部に新しく関数を記述していきます。

170808send_email_google_scripts2
Image: MakeUseOf

記述エリアに、以下のスクリプトをコピー&ペーストしてください。

function sendArticleCountEmails() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

ss.setActiveSheet(ss.getSheetByName("Send-Emails"));

var sheet = SpreadsheetApp.getActiveSheet();

var dataRange = sheet.getRange("A2:F4");

var data = dataRange.getValues();

for (i in data) {

var rowData = data[i];

var emailAddress = rowData[1];

var recipient = rowData[0];

var message1 = rowData[2];

var message2 = rowData[3];

var parameter2 = rowData[4];

var message3 = rowData[5];

var message = 'Dear ' + recipient + ',\n\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;

var subject = 'Your article count for this month';

MailApp.sendEmail(emailAddress, subject, message);

}

}

このスクリプトは一見難しそうに見えますが、実際はそう複雑なものではありません。では、各行で何をしているのかを理解できるように、紐解いていきましょう。

コードの各要素の解説

この関数が正しく実行されるようにするには、すべてのメール情報が保存されているシートをアクティブなシートにする必要があります。そうしないと、続く処理が正常に行われません。以下の2行は、アクティブなシートにするためのものです。

var ss = SpreadsheetApp.getActiveSpreadsheet();

ss.setActiveSheet(ss.getSheetByName("Send-Emails"));

次に、そのシートからすべてのデータを抽出する必要があります。sheet.GetRange()メソッドは、クォーテーション内で指定されたセルの範囲から情報を抽出します。次に、dataRange.getValues()メソッドが実際にその値を抽出し、dataと呼ばれる2次元配列に格納します。

var sheet = SpreadsheetApp.getActiveSheet();

var dataRange = sheet.getRange("A2:F4");

var data = dataRange.getValues();

これですべてのデータが配列に格納されたので、ループさせるためのfor文を使って配列にループ処理をさせることができます。配列の各要素は、列要素の1次元配列を含んでいる行になります。各列は、数字を使って参照できます。

var rowData = data[i];

var emailAddress = rowData[1];

var recipient = rowData[0];

var message1 = rowData[2];

var message2 = rowData[3];

var parameter2 = rowData[4];

var message3 = rowData[5];

上の例からわかるように、原文筆者は列要素をrowDataと呼ばれる1次元配列に格納しました。そして、メールアドレスを取得するために、2つ目の列をrowData[1]で参照させます。配列の最初の要素は常にゼロなので、rowData[1]は配列の2番目の要素になります。

この関数の次のステップは、メールの本文を構成するメッセージのすべてのセグメントを1つにまとめることです。ここでの秘訣は、キャリッジ・リターンの\n文字を使って、メールのフォーマットの見栄えをきれいにすることです。メール本文の要素の連結は、次のようになります。

var message = 'Dear ' + recipient + ',\n\n' + message1 + ' ' + message2 + ' ' + parameter2 + ' ' + message3;

加算演算子の+文字は、連結コマンドです。シングルクォーテーション内には、実際の静的なテキストを記入します。したがって、このメッセージでは、受信者名変数の前に「Dear 」という文字が挿入され、つなぎ合わされます。「Dear 」にスペースが含まれている点に注意してください。

次に、末尾に2つのキャリッジ・リターンを追加します。これにより、実際のメッセージが2行下の本文内に挿入されます。次に、メッセージの1つ目の構成要素、スペース、メッセージの2つ目の構成要素、スペース、各月のメッセージに挿入される変数、スペース、そして最後にメッセージの最後の構成要素を追加します。

必要に応じて好きなだけメッセージの構成要素を入れられます。その場合は、さらに多くの列を使って、メッセージ全体の構成要素を作り上げていくだけです。コードの最後の2行は、メールの件名を設定するもので、もちろんここにも、Googleスプレッドシートからデータを挿入することができます。そしていよいよsendEmail()の登場です。

スクリプトの実行方法

スクリプトエディタのウィンドウに戻り、「onOpen」トリガーを探しましょう。見つかった場合は、そのトリガーの角括弧内に、いくつかのコードが記述してあるでしょう。コードの最後の部分に、次のスクリプトを追加します。

var spreadsheet = SpreadsheetApp.getActive();

var menuItems = [

{name: 'Send Emails', functionName: 'sendArticleCountEmails'}

];

spreadsheet.addMenu('Send Emails', menuItems);

気を付けてほしいのは、functionNameパラメータのシングルクォーテーション内には、あなたが使っている関数の名前を正確に入力しなければならないということです。これにより、シートを開くたびに、そのシートにカスタムメニューがロードされます。

170808send_email_google_scripts3
Image: MakeUseOf

これで、毎月Googleスプレッドシートを開いてすべてのデータをレビューするたびに、メニューアイテムをクリックするだけでメールが送信されるようになります。その際、今回作成した新しい関数が実行され、すべてのメールがあなたに代わって送信されます。これまで何時間も掛かっていた業務も、クリック1回で片付くようになるはずです。

Googleスクリプトで時間を節約

今回紹介したものは、Googleスクリプトを使った自動化で時間を節約できる数多くのことの一例に過ぎません。面倒なのは、ちょっとした事前作業だけです。今回の例のように、スクリプトを使った自動化に費やした1分1秒が、結局は何時間もの節約につながります。その浮いた数時間で、どんな素晴らしいことができるのかを想像してみてください!

How to Send Email in a Google Sheet With Google Scripts | MakeUseOf

Image: Africa Studio/Shutterstock.com

Source: MakeUseOf(1, 2), Google