🎵

サントラCD頒布時のDLカード印字ミス対応をGoogleフォームとApps Scriptで効率化した体験

に公開
Google Forms
Google Apps Script
Google Sheets
tech

この記事はAnthrotech Advent Calendar 2025の8日目の記事です！

昨日の記事は、 fidさんの 2025年末 AIコーディングエージェントとの付き合い方 でした。
自分もここまでAIを使いこなしたいな・・・🫠

簡単な記事の説明

同人即売会でゲームのサウンドトラックCDを頒布し、CDが再生できない人向けにデジタル音源のダウンロードカードも付けました。
しかし、DLカードに本来印刷されるはずのユニークコードが、印刷所の手違いで1割ほど抜けてしまうトラブルが発生。
どれが未印字か判別できないため、もしコードなしのカードが手元に届いた場合はGoogleフォームで申請できるようにしました。

申請があった際は新しいコードをメールで返信する運用とし、その返信メールの自動生成をGoogleスプレッドシートとGoogle Apps Scriptで仕組み化した…という話です。

Googleフォームの準備

Googleフォームをサクッと準備します。

今回必要な情報
・名前（ハンドルネーム可）
・コードが印字されていないDLカードの写真
・返信先のメールアドレス

名前と写真はフォームで項目を追加、メールアドレスは設定から「メールアドレスを収集する」を「確認済み」にするだけでOK

これでフォームの準備はOKです。
申請する側からはこう見えます。

Googleフォームとスプレッドシートの連携

Googleフォームとスプレッドシートを連携することによって、送信された回答をスプレッドシート上で管理することが可能です。

連携するとスプレッドシートにこのような感じで回答が記録されていきます。便利～！

アップロードされた画像もURLの上でマウスホバーするとポップアップでプレビューが表示されます。

画像は自身のGoogleドライブにアップロードされるので、直接見に行くことも可能です。

回答者と新たなコードの紐づけ

連携したスプレッドシートにシートを追加します。
追加したシートにコードを入れます。

送信先セルに=INDIRECT("フォームの回答 1!B"&ROW())を、
送信先ハンドルネームセルに=INDIRECT("フォームの回答 1!C"&ROW())を入れてコピペします。

すると「フォームの回答」シートの内容がコピーされます。

送信するメールのテンプレート

スプレッドシートにシートを追加します。
こんな感じでメールのタイトルと内容を入れておきます。

GAS側で{{name}}を送信先の名前、{{code}}を新たなDLコードに置換します。

自動化スクリプトの用意

スプレッドシートの拡張機能->App Scriptでスクリプトを新規作成します。

以下のコードをペッと貼ります。

function mainProcess() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コード");
  var data = sheet.getRange(2, 1, 200, 4).getValues();

  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var code = row[0];
    var address = row[1];
    var name = row[2];
    var status = row[3];

    if (address && name && !status) {
      createGmailDraftFromSheet(code, address, name);
      sheet.getRange(i + 2, 4).setValue("Drafted");
    }
  }
}

function createGmailDraftFromSheet(code, address, name) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = ss.getSheetByName('メールテンプレ');

  var subject = templateSheet.getRange(2, 1).getValue();
  var body = templateSheet.getRange(11, 1).getValue();

  var replacedBody = body.replace('{{code}}', code);
  var replacedBody = replacedBody.replace('{{name}}', name);

  GmailApp.createDraft(address, subject, replacedBody);
}

function replaceDraftedWithSent() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("コード");
  var range = sheet.getRange(2, 4, 200, 1);
  var values = range.getValues();

  for (var i = 0; i < values.length; i++) {
    if (values[i][0] === "Drafted") {
      values[i][0] = "送信済み";
    }
  }
  range.setValues(values);
}

保存をしたらmainProcessになっているのを確認してから実行してみます。

権限云々出てくると思いますが、そのまま進めてください。
途中「このアプリは Google で確認されていません」なんて出てきますが「詳細」をクリックすると進めることができます。

権限は出てきたのも全て許可してください。

スクリプトの動作が完了するとGmailの下書きにメールができています。

下書きを作成するとスプレッドシートの「送信済み」に「Drafted」が追記されます。

あとは送信するだけ！

送信後はGASでreplaceDraftedWithSentを実行するとステータスが「送信済み」に変わります。
プログラム上「Drafted」になっていれば問題ないのですが、管理上変えておくと良いかも。

下書きスクリプトの自動実行

下書きを生成するスクリプト、毎回実行するのも面倒なので自動化しちゃいましょう。
「トリガー」で簡単にできちゃいます。

トリガーの設定はこんな感じ。
・実行する関数を選択：mainProcess
・実行するデプロイを選択：Head
・イベントソース：スプレッドシートから
・イベントの種類を選択：フォーム送信時

これでフォームから送信されると、自動で下書きを作ってくれます。

通知メールの設定

今回の自動化はメールの下書き生成までで、送信は手動で行わないといけません。
フォームから新たに送信されたことに気づかないと、コードを送れません・・・。
なのでメールで通知するように設定します。

スプレッドシートの「ツール」->「通知設定」->「編集通知」を開きます。

通知ルールは以下のように設定
・ユーザーがフォームを送信したとき
・送信頻度：1日1回

新しい回答が来ると、1日1回このようなメールが来ます。

あとはスプレッドシートから画像を確認して、問題なければ下書きを送信すればOK!

あとがき

最初、話を聞いたときは「めんどくさそ～～～～～」って思ってたんですが、思いのほか簡単に効率化できて楽でした。
ちなみにcreateGmailDraftFromSheetのGmailApp.createDraftをGmailApp.sendEmailにすれば送信まで自動化できるそうです。（試してないからわからんけど）

Discussion