【業務効率化】Google Formの回答結果を自動的にPDFで個別返信をやってみた!!

やろうとしたこと

今まで、Google　Formから集計した結果をシートにすることはありますが、分析結果は個別でまた送信することが多く、今回は回答者が記入した回答に対して、分析結果を自動送信する仕組みをやってみました。

なんでやり始めたのか？

今までの作業：
Google Formから自動転記した答えを手動で転記
→転記したデータをPDFにし
→メールで返信

一連の流れ、一人のお客さんに対して、5分ほどかかります。
手作業を減らすことによって、ヒューマンエラーをなくし、より分析の方に力を入れると予想。

具体的にやったこと

Google Formから Apps Script経由で、回答と分析結果をあらかじめspreadsheetに記載し、
その結果を自動的にPDFにして、ドライブ保存した後に、メールに添付して自動送信。

1. スプレットシートの型を設定

回答ごとに分析結果を右側に記載します。

今回は TIMESTAMPを表示させないように、
headerRowとresponseRowを[1]からスタートしました。
（GOOGLE FORMのデフォルトでは、TIMESTAMPが[0]番目です。）

function createIndividualAnalysisSheet(email, responseRow, headerRow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetName = "分析結果_" + email;
  var individualSheet = ss.getSheetByName(sheetName);

  if (!individualSheet) {
    individualSheet = ss.insertSheet(sheetName);
  }

  // 固定コンテンツを設定
  var fixedContent1 = "質問１の分析結果";
  var fixedContent2 = "質問２の分析結果";

  // 回答者名を転記
  individualSheet.appendRow(["回答者", email]);

  // 質問1とその回答、固定コンテンツ1を転記
  individualSheet.appendRow([headerRow[1], responseRow[1], fixedContent1]);

  // 質問2とその回答、固定コンテンツ2を転記
  individualSheet.appendRow([headerRow[2], responseRow[2], fixedContent2]);

  // 質問3以降とその回答を転記
  // for (var i = 3; i < headerRow.length; i++) {
  //   individualSheet.appendRow([headerRow[i], responseRow[i]]);
  // }

  // 空行を追加
  individualSheet.appendRow(["", ""]);

  return individualSheet;
}

2 回答結果をPDFに作成

回答結果をPDFに出力して、GOOGLEドライブに保存。
folderId を指定すれば、指定フォルダにPDFファイルを保存できます。

function createPdfFromSheet(sheet, fileName) {
  var ss = sheet.getParent();

//出力フォーマット設定
  var url = 'https://docs.google.com/spreadsheets/d/' + ss.getId() + '/export?';
  var exportOptions = 'exportFormat=pdf&format=pdf' + // pdf format
    '&size=letter' + // paper size
    '&portrait=true' + // orientation, false for landscape
    '&fitw=true' + // fit to width, false for actual size
    '&sheetnames=false&printtitle=false' + // hide optional headers and footers
    '&pagenumbers=false&gridlines=false' + // hide page numbers and gridlines
    '&fzr=false' + // do not repeat row headers (frozen rows) on each page
    '&gid=' + sheet.getSheetId(); // the sheet's Id

  var token = ScriptApp.getOAuthToken();
  var response = UrlFetchApp.fetch(url + exportOptions, {
    headers: {
      'Authorization': 'Bearer ' + token
    }
  });

  var pdfBlob = response.getBlob().setName(fileName);

  // フォルダーのIDを指定
  var folderId = "ドライブのフォルダID";
  var folder = DriveApp.getFolderById(folderId);

  var pdfFile = folder.createFile(pdfBlob);

  return pdfFile;
}

3.回答結果をPDFファイルで添付して、メールから送信

・ドライブに保存したPDFファイルを添付して、メール送信

function sendEmailToRespondent(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responseSheet = e.range.getSheet();
  var formResponses = responseSheet.getDataRange().getValues();
  var headerRow = formResponses.shift();
  var responseRow = formResponses[e.range.getRow() - 2];

  // メールアドレスと回答内容を取得
  var emailAddressIndex = headerRow.indexOf("メールアドレスを記入してください"); // メールアドレスの質問の列を指定（今回は1番目の質問です。）
  var emailAddress = responseRow[emailAddressIndex];


  // PDFファイルを作成
  var pdfFile = createPdfFromSheet(individualAnalysisSheet, "回答結果_" + emailAddress + ".pdf");

  // メールの件名と本文を作成
  var subject = "アンケートの回答と分析シート";
  var message = "以下はあなたが提出したアンケートの回答です。\n\n";

  // 回答内容を本文に追加
  for (var i = 0; i < headerRow.length; i++) {
    message += headerRow[i] + ": " + responseRow[i] + "\n";
  }

  // // 分析シートのURLを本文に追加
  // var analysisSheetUrl = ss.getUrl() + "#gid=" + individualAnalysisSheet.getSheetId();
  // message += analysisSheetUrl;

  // PDFファイルを添付してメールを送信
  var attachments = [pdfFile.getAs(MimeType.PDF)];
  MailApp.sendEmail(emailAddress, subject, message, { attachments: attachments });
}

4 トリガーの設置

Apps Scriptを動いてくれるためのトリガーです。

function setUpTrigger() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger("sendEmailToRespondent")
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();
}

5. scriptを起動

送信ボタンを押すと、上記のscriptを起動させる仕組みです。

function onFormSubmit(e) {
  var email = e.values[1]; // メールアドレスが格納されている列を指定
  var individualSheet = createIndividualAnalysisSheet(email, e.values, e.namedValues);

  // PDFを生成し、Google Driveに保存
  var fileName = "分析結果_" + email;
  var pdfFile = createPdfFromSheet(individualSheet, fileName);

  // メールでPDFを送信
  var subject = "アンケート分析結果";
  var body = "こちらがあなたのアンケート分析結果です。";
  var attachments = [pdfFile.getAs(MimeType.PDF)];
  MailApp.sendEmail(email, subject, body, { attachments: attachments });
}

6. 結果

GOOGLEフォームを回答したら、ちゃんとメールとPDFが来ました(歓喜！！！)

まとめ

ファイルを添付して、送信する手間がなくなりましたので、効率よく他の業務に集中できました。処理時間を減らすことによって、お客さんも返信への待ち時間を減らせます。喜んでくれると嬉しいです ^^

細かいことはまだ書けてませんので、これからブラッシュアップしていきたいと思いますので、ご意見、コメント、お待ちしておいます。