Google フォーム → Google スプレッドシート → PDF で出力君を作った

想定外に時間ができた2022年5月

今は経理のお仕事メインでやってるのですが、5月は月初の連休のため月次の処理が後ろ倒しになります。

しかし、親会社への報告とか後ろ倒しにできないものもあり、気合を入れてゴールデンウィーク前から準備し、ゴールデンウィーク明けから前のめりでやっておりました・・・が、気合い入りすぎて普通の月よりも早く手が空いてしまいました。

そんな時、人事の方から「Google フォーム → Google スプレッドシート → PDFで出力」する的なのがあると、定型の書類処理が楽なんだけどなぁ、と話があり、空いてる時間でやってみました。

使うのはもちろん GAS、Google Apps Script です。

戦略

Google フォームを作成し、Google スプレッドシートと関連つける
Google フォームに入力し送信
「フォームの回答1」シートに反映
トリガーをきっかけとして、「フォームの回答1」シートの一番下に入力された行（最新の行）の内容を取得
前項で取得した内容を元に、シート雛形に値を埋める
シート雛形をPDF化する
指定のフォルダに保存

下準備その1 Google フォームから Google スプレッドシートへ

まずは Google Form で普通にアンケートフォーム的なのを作ります。

Google Drive 開いて、左上の「新規」から

Google フォームを選択。

初期状態だとなんなので、選択肢を1つ増やして一旦完成とします。

この Google フォームだけでも回答の集計などができるのですが、 Google スプレッドシートに一旦出力するよう設定します。

上にある「回答」から、右側にあるスプレッドシートのアイコンをクリックします。

今回は「新しいスプレッドシートを作成」を選択し、右下の「作成」ボタンを押します。

すると、スプレッドシートが出来上がります。

このスプレッドシートに、GASを書いていきます。

その前に一回くらいアンケートやってみますかね。

右上にある「送信」から

リンクのマークを選び、そこで表示されるURLにアクセスします。

アンケートフォームが出てくるので、回答して「送信」ボタンを押します。

無事、スプレッドシートに回答が反映されました。

下準備その2 定型フォーマット準備

と言っても大したことではなく、適当にフォーマットを用意します。

シート名は「シート雛形」としておきます。

今回は項目名を左に、回答が入るところを右にしておきました。

この回答が入るセルには「名前付き範囲」で名前をつけておきます。

回答が入るセルを選択して、上のメニューから、「データ→名前付き範囲」と進み、名前をつけます。

名前は、質問項目と同じにしておきます。

この「名前付き範囲」が今回の肝です。

コード

コードを貼り付けてから、main 関数を実行します。上にある「実行」ボタンで実行してください。

すると権限の確認メッセージが出てくると思うんですが、これを承認しておいてください。

数秒で Google ドライブのルートにファイルが出来上がります。

function main() {
  // Google フォームの結果が集計されるシート
  const totalingSheetName = "フォームの回答 1";
  // 雛形シート
  const templateSheetName = "シート雛形";

  // 集計シートの1行目をkeyに、最終行をvalueにしたMapを作る
  const hash = getMapFromAnswerListTitlesAndLastValues(totalingSheetName);

  // Mapを元に、雛形シートに値を埋める
  fillTemplateSheetFromHash({
    sheetName: templateSheetName,
    hash: hash,
  });

  // 雛形シートをPDFにして、保存先フォルダの中に格納する
  transformSheetToPdf(templateSheetName);
}

/**
 * Google Formの集計スプレッドシートから、1行目の項目名をkeyに、最終行の値をvalueにセットしたMapを返す
 * @module getMapFromAnswerListTitlesAndLastValues
 * @param {string} sheetName - Google Form の集計スプレッドシート名
 * @return {Map.<string, string>} - 集計スプレッドシートの1行目をkeyに、最終行をvalueとするMap
 */
function getMapFromAnswerListTitlesAndLastValues(sheetName) {
  // sheetNameを元にシートを特定し、データの入っている最終行と最終列を取得する
  const sourceSheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sourceSheet.getLastRow();
  const lastColumn = sourceSheet.getLastColumn();

  // 最初の行の要素（タイムスタンプ, 無題の質問, ...）を配列で取得する
  const keys = sourceSheet.getRange(1, 1, 1, lastColumn).getValues()[0];

  // 最新の行（最終行）の要素（2022/5/26, 無題の質問, ...）を配列で取得する
  const values = sourceSheet
    .getRange(lastRow, 1, lastRow, lastColumn)
    .getValues()[0];

  // Map型の構造体に
  // タイムスタンプ => 2022/5/26 ,無題の質問 => オプション2, ...
  // と言った形でデータを入れていく
  const hash = new Map();
  keys.forEach((key, index) => {
    value = values[index];
    hash.set(key, value);
  });

  return hash;
}

/**
 * hashのkey名に対応した名前付きセルに、hashのvalueを入力する
 * @module fillTemplateSheetFromHash
 * @param {object} obj - sheetName, hash が入った構造体
 * @return {undefined} - なし
 */
function fillTemplateSheetFromHash(obj) {
  // 分割代入で引数を取得
  const { sheetName, hash } = obj;

  const templateSheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // 名前付き範囲を集める
  const namedCells = templateSheet.getNamedRanges();

  // 名前付き範囲をのセルに、その名前と同じフォームの回答を入力する
  namedCells.forEach((namedCell) => {
    const name = namedCell.getName().toString();
    namedCell.getRange().setValue(hash.get(name));
  });
}

/**
 * 指定したシートをPDFにして、シートとともにフォルダに格納する
 * @module transformSheetToPdf
 * @param {string} sheetName - 雛形シート名
 * @return {undefined} - なし
 */
function transformSheetToPdf(sheetName) {
  // 雛形シート
  const sourceSheet =
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  // ファイル名
  const fileName = "Googleフォームからファイル出力";

  //スプレッドシートの個別シートをPDF化するために新規のスプレッドシートを作成
  const temporarySheet = SpreadsheetApp.create(fileName);

  //PDF化したい個別シートを新規作成したスプレッドシートにコピー
  sourceSheet.copyTo(temporarySheet);

  //スプレッドシート新規作成でデフォルト作成されるシートを削除
  temporarySheet.deleteSheet(temporarySheet.getSheets()[0]);

  //PDFとしてgetAsメソッドでblob形式で取得
  const pdf = temporarySheet.getAs("application/pdf");

  //pdfファイルの名前を設定
  pdf.setName(fileName);

  //GoogleドライブにPDFに変換したデータを保存
  DriveApp.createFile(pdf);
}

実際のコードは、

出力先のフォルダを指定できるようにしたり
- const folder = DriveApp.getFolderById(folderId);
そのフォルダに出力したり
- folder.createFile(pdf);
フォームの内容からファイル名を持ってきたり
- const fileName = `${fileNamePrifix}_${fileNameBody}`;
ファイルのオーナー権限を任意の人に変えたり
- file.setOwner(fileOwner);

ということをしています。

工夫したのは Google シートの項目を拾って、同名の名前付きセルに当てはめることで、Google フォーム側で項目が増えても、それに対応する名前付きセルを作るだけでよくしたこと。

つまり、コード側の改修なしで反映できるようにしたことです。

困ったのは

メソッドを () をつけずに呼んできて意図しない値が出てエラーになったり（ありがち）
作成した Google スプレッドシートを Google Driveでどう扱えばいいかわからなかったり
- const file = DriveApp.getFileById(spreadSheet.getId());
main サブルーチンに引数を設定したら、そこにGoogle フォームから構造体が入ってきて上書きされてびっくりしたり
Google フォームや、Google スプレッドシートのオーナーを変えたらコードが吹っ飛んだり（謎）