#26 スプレッドシートの内容に応じた証明書を作成して、メールで送信する

Google が提供している GAS のクイックスタートを見ていると、いくつも参考になりそうなプログラムが公開されていたので、どんなプログラムなのかを紹介します。

Apps Script samples overview  |  Google Developers

これらのプログラムの動作が理解できれば、似たような動作・処理を行うプログラムを作成する参考になると思います。
この記事で説明するのは、以下のサンプルプログラムです。

Send personalized appreciation certificates to employees  |  Apps Script  |  Google Developers

どんなプログラム？

サンプルプログラムを案内しているページでは、以下のように説明されています。

About this solution
Automatically customize the Google Slides Employee Certificate template with employee data in Google Sheets, then send the certificates using Gmail.

https://developers.google.com/apps-script/samples/automations/employee-certificate

簡単に翻訳すると、下図のように Google スプレッドシートにまとめられたの従業員データを使用して、Google スライドの「テンプレート」を自動的にカスタマイズして、Gmail を使用して個別の証明書を送信するものです。

スプレッドシートに保存されているデータ

テンプレートとして提供されているスライド

メールに添付されてきた PDF

企業で利用されることを想定して、「Employee certificates」という表現になっていますが、学校教育に置き換えれば記録証などの送信に応用できるのではないでしょうか？
「差し込み印刷」ではなく、「差し込み PDF」として使える技術だと思います。

サンプルプログラムを実行してみる

サンプルプログラムを案内しているページでは、「Try it」として次のような 3つの手順を説明しています。順に、どのようなことをしているのか、説明していきます。以降では、元となるページへのリンクも設定してありますが、ファイルをコピーするためのリンク以外は参照しなくても、読み進めていけると思います。

Step1

Step 1: Create the Slides presentation and a Drive folder
ここでは、サンプルプログラムを実行する準備として、次の 2つのこと行うと説明されています。

• 個別に生成される証明書のテンプレートとなる Google スライドの用意。ひとまず、前述のような「Employee certificates スライドテンプレート」を、自身の Google ドライブにコピーします。
  そして、スライドの「ファイル ID」を控えておきます。　※実際に指定するのは、次のステップです。

• 証明書が作成される Google ドライブの「フォルダ ID」を確認して控えておきます。プログラムを実行した結果、複数のファイルが作成されるので、空のフォルダを作成して、そのフォルダの「フォルダ ID」を指定するとよいでしょう。　※実際に指定するのは、次のステップです。

Step 2

Step 2: Set up the spreadsheet
ここでは、「Employee certificates スプレッドシート」を自身の Google ドライブにコピーして、スクリプトを実行するための準備を行います。
プログラムを変更したら、「プロジェクトの保存」を忘れずに行っておきましょう。

• スプレッドシートで、「拡張機能」→「Apps Script」でスクリプトエディタを開きます。

• 1行目の変数 slideTemplateId に、Step 1 で控えておいたスライドの「ファイル ID」を設定します。

• 2行目の変数 tempFolderId に、Step 1 で控えておいたフォルダの「フォルダ ID」を設定します。

Step 3

Step 3: Create and send the certificates
ここでは、実際にスクリプトを実行します。　※必要に応じて、プログラムを実行するために必要な権限の確認があります。

実際に生成されたファイルがどのように届くのかを確認するために、スプレッドシートの列 F に入力されているメールアドレスを、すべて自分で受信できるメールアドレスに変更しておきましょう。すべて同一のもので構いませんし、Gmail のメールアドレスでなくても構いません。

送信先のメールアドレスを変更しておきましょう

スプレッドシートのメニューから「Appreciation」→「Create certificates」を選択して、プログラムを実行します。

メニューからプログラムを実行する

列 G に、個別に生成されたスライドの「ファイル ID」、列 H には「CREATED」と設定されます。

「Create certificates」の実行後

実際に Step 1 で控えておいたフォルダを確認すると、下図のように個別に Google スライドのファイルが作成されています。

指定したフォルダに、個別のファイルが作成される

同様にスプレッドシートのメニューから「Appreciation」→「Send certificates」を選択して、プログラムを実行します。
順番に処理され、列 H には「SENT」と設定されます。

「Send certificates」の実行後

上記の手順でプログラムを実行すると、スプレッドシートに設定されていたメールアドレスには、図のようなメールが届きます。生成されていたのは、Google スライドのファイルでしたが、送信先に届いているのは PDF ファイルとなっています。

送信されたメール（PDF ファイルが添付されている）

実際のプログラム

それでは、どんなプログラムなのかを簡単に説明します。
以下は、コピーされたスプレッドシートに保存されていた GAS のプログラムそのままの状態です。

グローバル変数

let slideTemplateId = "PRESENTATION_ID";
let tempFolderId = "FOLDER_ID"; // Create an empty folder in Google Drive

1行目、2行目は、Step 2 で変更する部分です。
プログラム中で使用するテンプレートとなるスライドの「ファイル ID」、個別に生成したファイルの保存先となるフォルダの「フォルダ ID」を設定する部分です。　※上記のようなサンプルプログラムのままでは動作しません。
一般的に、このように関数外に宣言されて、どの関数からでも参照できる変数をグローバル変数と呼びます。

関数 onOpen()

4 ～ 15行目は、スプレッドシートにカスタムメニューを追加している部分です。

function onOpen(e) {

8行目で宣言されている onOpen(e) という関数は、Simple Triggers として用意されているもので、編集権限を持っているユーザーがスプレッドシートを開いたときに実行されるトリガー関数です。
今回のようにカスタムメニューを追加する目的でよく利用されています。

  let ui = SpreadsheetApp.getUi();

9行目では、getUi() でスプレッドシートにメニューを追加するために必要な「Class Ui」を取得しています。

10 ～ 14行目は、行末に ; もなく、1つの命令のように感じられますが、それぞれ別の命令です。

  ui.createMenu('Appreciation')

10行目の createMenu(caption) で、カスタムメニューを作成しています。引数として与えられている文字列がメニューとして表示される文字列です。変更すれば、変更した内容でメニュー項目が表示されます。
この関数の戻り値が「Class Menu」となっていて、11 ～ 14行目の関数を呼び出すもとになっています。

  .addItem('Create certificates', 'createCertificates')

11行目は、10行目で生成された「Class Menu」に対して、addItem(caption, functionName) によってメニュー項目を追加しています。1つ目の引数がメニュー項目として表示される文字列、2つ目の引数がその項目が選択されたときに実行される関数、となっています。　※それぞれ文字列として指定
この addItem(caption, functionName) が戻すのは、呼び出した「Class Menu」となっているので、12 ～ 14行目も ; を行末に書くことなく続いています。

  .addSeparator()

12行目では、addSeparator() によってメニュー項目にセパレーター（区切り線）を追加しています。プログラムの直接的な動作にはあまり関係がありません。

  .addItem('Send certificates', 'sendCertificates')

13行目では、11行目と同様に、メニュー項目を追加しています。

  .addToUi();

14行目の addToUi() によって、実際にカスタムメニューがスプレッドシートに追加されます。

このように onOpen() を利用すると、自作した GAS のプログラムを呼び出すために、スプレッドシートに図形描画でボタンを作成し、スクリプトを割り当てるのではなく、メニューから実行できるようになります。

図形に「スクリプトを割り当て」

関数 createCertificates()

17 ～ 65行目の createCertificates() では、スプレッドシートの内容をもとにして、個別のファイル（Google スライド）を作成しています。

  // Load the Google Slide template file
  let template = DriveApp.getFileById(slideTemplateId);

24行目では、変数 slideTemplateId に設定したテンプレートとなる「Google スライド」の「Class File」を取得しています。

  // Get all employee data from the spreadsheet and identify the headers
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let values = sheet.getDataRange().getValues();

26 ～ 28行目では、スプレッドシートのすべての内容を、変数 values に読み込んでいます。この処理は、個別に getValue() していると、処理に時間がかかってしまうため、まとめて getValues() で読み込んでいます。

  let headers = values[0];

29行目では、変数 headers に、変数 values の 1行目の内容をコピーしています。values などの配列は 1 からではなく 0 からはじまっているので、values[0] となっています。

スプレッドシートの 1行目の内容

  let empNameIndex = headers.indexOf("Employee Name");

30行目では、変数 headers の内容の中に "Employee Name" が何番目に格納されているかを確認して、変数 empNameIndex に設定しています。
今回の場合、上図のように A1 に "Employee Name" が設定されているので、0 が設定されます。　※配列は 0 からはじまるので、1列は 0 となる。

  let dateIndex = headers.indexOf("Date");
  let managerNameIndex = headers.indexOf("Manager Name");
  let titleIndex = headers.indexOf("Title");
  let compNameIndex = headers.indexOf("Company Name");
  let empEmailIndex = headers.indexOf("Employee Email");
  let empSlideIndex = headers.indexOf("Employee Slide");
  let statusIndex = headers.indexOf("Status");

31 ～ 37行目でも、同様にそれぞれの文字列が配列の何番目に入っているのかを変数に設定しています。
今回の場合は、以下のように設定されます。

• empNameIndex: 0

• dateIndex: 1

• managerNameIndex: 2

• titleIndex: 3

• compNameIndex: 4

• empEmailIndex: 5

• empSlideIndex: 6

• statusIndex: 7

  // Iterate through each row to capture individual details
  for (let i = 1; i < values.length; i++) {

39 ～ 40行目では、スプレッドシートに設定されたデータを for でくり返し処理している部分です。40行目の行末の { から、64行目の } までがくり返されます。

    let rowData = values[i];

41行目では、変数 rowData に、処理の対象となる行のデータをコピーしています。プログラムの動作としては、29行目と同じです。

    let empName = rowData[empNameIndex];
    let date = rowData[dateIndex];
    let managerName = rowData[managerNameIndex];
    let title = rowData[titleIndex];
    let compName = rowData[compNameIndex];

42 ～ 46行目では、変数 empName、date、managerName、title、compName に、それぞれ rowDate の対応する内容をコピー（代入）しています。

    // Make a copy of the Slide template and rename it with employee name
    let tempFolder = DriveApp.getFolderById(tempFolderId);     

49行目では、変数 tempFolderId に設定されたフォルダの「Class Folder」を取得しています。

    let empSlideId = template.makeCopy(tempFolder).setName(empName).getId();        

50行目では、指定されたフォルダにテンプレートとなる Google スライドのコピーを作成（ makeCopy(destination) ）しています。
そして、ファイル名を変数 empName のように変更（ setName(name) ）し、そのファイルの ID を取得（ getId() ）して、変数 empSlideId に設定しています。

    let empSlide = SlidesApp.openById(empSlideId).getSlides()[0];

51行目では、テンプレートからコピーを作成された Google スライドの 1枚目を変数 に設定しています。　※ここで設定されるのは「Class Slide」

    // Replace placeholder values with actual employee related details
    empSlide.replaceAllText("Employee Name", empName);

54行目では、replaceAllText(findText, replaceText) によって、テンプレートからコピーされたスライド内の "Employee Name" を変数 empName に置き換えています。

    empSlide.replaceAllText("Date", "Date: " + Utilities.formatDate(date, Session.getScriptTimeZone(), "MMMM dd, yyyy"));

55行目では、スライド内の文字列を変数に単純に置き換えるのではなく、スプレッドシート内の日付を指定された形式の日付に変換したもので置き換えています。
日付を設定する場合には、この辺りをカスタマイズする必要があるでしょう。

    empSlide.replaceAllText("Your Name", managerName);
    empSlide.replaceAllText("Title", title);
    empSlide.replaceAllText("Company Name", compName);

56 ～ 58行目でも、54行目と同様に文字列の置き換えを行っています。

    // Update the spreadsheet with the new Slide Id and status
    sheet.getRange(i + 1, empSlideIndex + 1).setValue(empSlideId);
    sheet.getRange(i + 1, statusIndex + 1).setValue("CREATED");

61 ～ 62行目では、列 G・列 H にそれぞれ「出力されたファイルの ID」「CREATED」を設定しています。

    SpreadsheetApp.flush();

63行目では、flush() を呼び出して、61 ～ 62行目までの処理が画面に表示されるようにしています。　※この処理は、パフォーマンス向上のために表示の更新が行われない場合があるのを避けるために行われているようです。必ずしもなければならないものではありません。

このサンプルプログラムでは、ファイルの作成と送信を別の関数に分けて行っていますが、分けずに一度に行っても構いません。このサンプルプログラムでは、送信前に生成されたファイルがチェックできるように、分割しているのだと思います。

関数 sendCertificates()

67 ～ 114行目の sendCertificates() では、createCertificates() で作成されたファイルを、メールで送信しています。

  // Get all employee data from the spreadsheet and identify the headers
  let sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let values = sheet.getDataRange().getValues();
  let headers = values[0];
  let empNameIndex = headers.indexOf("Employee Name");
  let dateIndex = headers.indexOf("Date");
  let managerNameIndex = headers.indexOf("Manager Name");
  let titleIndex = headers.indexOf("Title");
  let compNameIndex = headers.indexOf("Company Name");
  let empEmailIndex = headers.indexOf("Employee Email");
  let empSlideIndex = headers.indexOf("Employee Slide");
  let statusIndex = headers.indexOf("Status");

73 ～ 84行目の内容は、createCertificates() の内容と変わりないため省略します。

  // Iterate through each row to capture individual details
  for (let i = 1; i < values.length; i++) {
    let rowData = values[i];
    let empName = rowData[empNameIndex];
    let date = rowData[dateIndex];
    let managerName = rowData[managerNameIndex];
    let title = rowData[titleIndex];
    let compName = rowData[compNameIndex];
    let empSlideId = rowData[empSlideIndex];
    let empEmail = rowData[empEmailIndex];

86 ～ 96行目の内容も、createCertificates() の内容と変わりないため省略します。

    // Load the employee's personalized Google Slide file
    let attachment = DriveApp.getFileById(empSlideId);

98行目では、createCertificates() で作成され、列 G に設定されていたファイルの ID の「Class File」を取得し、変数 attachment に設定しています。

    // Setup the required parameters and send them the email
    let senderName = "CertBot";
    let subject = empName + ", you're awesome!";
    let body = "Please find your employee appreciation certificate attached."
    + "\n\n" + compName + " team";

100 ～ 104行目では、送信するメールの情報を設定しています。

• 変数 senderName：　送信者の名前

• 変数 subject：　メールの件名

• 変数 body：　メールの本文

送信されたメール（PDF ファイルが添付されている）

    GmailApp.sendEmail(empEmail, subject, body, {
      attachments: [attachment.getAs(MimeType.PDF)],
      name: senderName
    });

105 ～ 108行目では、Gmail でメールを送信しています。使用しているのは sendEmail(recipient, subject, body, options) ですが、4番目の引数の options で添付ファイルを指定するなどしている。

• 106行目：　attachments で、添付ファイルの設定が行える。変数 attachment を、「Interface BlobSource」で説明されている getAs(contentType) （引数に MimeType.PDF  → 'application/pdf' ）を用いて、PDF に変換して添付している。

• 107行目：　name によって、送信者の名前を変更している。手作業で送信したものではなく、機械的に送信したものであることを示すために、違う名前を設定しているのだろう。この行がなければ、通常の Gmail で送信するときと同じ名前で送信される。

    // Update the spreadsheet with email status
    sheet.getRange(i + 1, statusIndex + 1).setValue("SENT");

111行目は、列 H に「SENT」と設定しています。

    SpreadsheetApp.flush();

112行目は、63行目と同様に、flush() を呼び出して画面の表示を更新させようとしています。

まとめ

このプログラムを参考にすれば、スライドで作成したテンプレートをもとに、認定証や賞状などを生成して、メールで送信することができそうです。
こんな動作は、Google フォームのアドオン「Certify'em」で実現できるのは知っていたけど、有料のアドオンだったのでちょっと躊躇してました。

Certify'em - Google Workspace Marketplace

今回のサンプルプログラムは、同じような動作をしているもので、そんなに難しいことをしているわけでもないように感じました。このプログラムを応用できるなら、上記の有料アドオンを利用しなくても、同じようなことができそうです。

このプログラムの動作を確認してみて、Google スライドのファイルを Gmail で添付して送信すると、相手に届いたメールには PDF が添付されているという点に驚かされました。PDF への変換は、GAS のプログラム内では行っているというよりも、Gmail が行ってくれているのです。この仕組みは、違う場面でも利用できそうです。

ちなみに、このサンプルプログラムの説明の冒頭に、以下のように記載されています。まぁ、そんなに大きなボリュームでもないし、これくらいの内容が Beginner（初心者）なのね。

Coding level: Beginner
Duration: 15 minutes
Project type: Automation with a custom menu

https://developers.google.com/apps-script/samples/automations/employee-certificate

2022/03/31 追記：
ちなみに、同じようなことを Google Classroom で行うプログラムを、以下のように作ってみました。うまく使い分けてみてください。