Googleフォームで回答編集用URLを取得する方法

Googleフォームは利用されてますか?
制限はいろいろとありますが、シンプルでなんといっても無料で利用できるいいサービスですよね。
私も活用しています。
さて、この記事ではGoogleフォームを活用するために私が利用している方法、スクリプトを共有したいと思います。

背景

私は民泊を運用しておりますが、民泊ではルール上宿泊者名簿を登録・保管しなければいけません。
この宿泊者名簿ホテルなんかでも整備されているものですが、今のホテルは一般の宿泊者が目にすることはあまりないと思います。
しかし、日本の古い民宿や外国映画にでてくるモーテルなどで紙ベースの宿泊者名簿があり、宿泊者が自ら記入したりしていたものです。
これも民泊でも整備する必要があり、私はGoogleフォームを活用して電子化しているわけです。
予約が入った後に事前に宿泊者（ゲスト）にフォームを送信して記入してもらうようにしています。

便利に利用できるGoolgeフォームですが、無料であるかわりにいくつか工夫が必要な点があったりします。
その一つが、ゲストが一度記入した回答内容を再度編集したい場合の対応です。
Googleフォームで回答すると回答データを再編集するためのリンクがゲストにも返信されますが、そのメールを消してしまうなどリンクがわからなくなってしまった場合に困ることになります。
このリンクは回答したゲストにはわかりますが、こちら側にはリンクを確認するすべがないんですね。
ということで、この記事ではその回答データを編集するためのリンクを取得するために私が利用している方法、Goole Apps Script(GAS)といわれるExcelのマクロのようなものをご紹介します。

前提およびスクリプト編集準備

まず、前提ですがGoogleフォームには回答データを保持するスプレッドシートをリンク、作成しておく必要があります。
これは、Googleフォームの基本機能、簡単な設定でできます。
フォームを編集モードで開き、回答タブに表示される「スプレッドシートで表示」をクリックするだけです。

そのスプレッドシートを開いた状態で 拡張機能 > Apps Scriptでスクリプトを編集できる画面が表示されます。

Apps Scriptを初めて開いた場合には、ディフォルトで MyFunction というセクションがでてきますが、不要なので削除して問題ありません。
準備事項としてもう一点、このスクリプトでは編集用のURLをこのスプレッドシートに書き込みます。
上記の画像の1列目に「Edit_URL」というものがありますが、これと同じように先頭列として上記と同じ列を作成しておいてください。
スクリプトを実行すると、この列に回答ごとの編集URLが書き込まれることになります。

私が利用しているGASスクリプト

で、本題ですが、以下が私が実際に利用しているスクリプトで2か所だけIDを変更してもらうだけでご利用いただけると思います。

/**
 * フォームの回答用の編集URLを取得して、回答データ一覧シートに挿入する。
 * 
 * 準備作業: このスクリプトを実行する前に確認、準備しておく
 *  1) フォームに回答データのスプレッドシートを紐づけ、作成しておく
 *  2) 上記のスプレッドシートの一列目(A列)を手動で作成し項目名を 'Edit_URL'とする。(項目名は何でも可)
 *  3) このスクリプトの sheetId, formIdを下の記載例に従い更新する
 */
function updateEditResponseUrls() {

  // フォームにリンクされたスプレッドシートの指定
  // sheetId : スプレッドシートを開いた際に表示されるURLの以下の XXXXXXXXXXXXXX 部分。
  //           https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXX/edit
  // sheetName : シート名で、特に変更していなければ下のままで問題なし
  //
  const sheetId = '11gYCuJnGphz9k-e4L7KBkXrlNc3Wz2CDAnzNqgf4awM';
  const sheetName = 'フォームの回答 1';

  // フォームの指定
  // formId : フォームを開いた際に表示されるURLの以下の　YYYYYYYYYYYYY 部分。
  //          https://docs.google.com/forms/d/YYYYYYYYYYYYYYYY/edit
  const formId = '13fIzcNzESwYfUYNyFMivKHeSe2s4_7YnkbxZPq_Ccrs';

  // 回答データ一覧スプレッドシートの取得
  const sheet = SpreadsheetApp.openById(sheetId).getSheetByName(sheetName);
  const sheetData = sheet.getDataRange();

  // フォームおよび回答データの取得
  const form = FormApp.openById(formId);
  const responses = form.getResponses();

  // フォームの回答データを一つずつ処理し、
  //   回答編集用URLを取得、シートの一列目に挿入する
    responses.forEach((response, index) => {

    // もし回答データ一覧スプレッドシートの1列目にすでにデータがある場合は後続処理をスキップ
    // すでにデータがあるということは過去、編集用URLを取得済み
    if (sheetData.getCell(index + 2, 1).getValue() !== '') {
      return;
    }

    // 回答データよりの回答編集URLの取得
    const editableUrl = response.getEditResponseUrl();
    const editableStr = `=HYPERLINK("${editableUrl}", "回答編集用URL.${index + 1}")`;

    // 回答編集用URLを回答データ一覧スプレッドシートの1列目に挿入する
    sheetData.getCell(index + 2, 1).setValue(editableStr);
  });
}

スクリプトの内容は理解いただかなくて結構ですが、以下の太字の部分はご自分のフォームの情報で更新いただく必要があります。
sheetIdとformIdに続く長ーい英数字の文字列部分ですね。

---

// フォームにリンクされたスプレッドシートの指定
// sheetId : スプレッドシートを開いた際に表示されるURLの以下の XXXXXXXXXXXXXX 部分。
// https://docs.google.com/spreadsheets/d/XXXXXXXXXXXXXX/edit
// sheetName : シート名で、特に変更していなければ下のままで問題なし
//
const sheetId = '11gYCuJnGphz9k-e4L7KBkXrlNc3Wz2CDAnzNqgf4awM';
const sheetName = 'フォームの回答 1';

// フォームの指定
// formId : フォームを開いた際に表示されるURLの以下の　YYYYYYYYYYYYY 部分。
// https://docs.google.com/forms/d/YYYYYYYYYYYYYYYY/edit
const formId = '13fIzcNzESwYfUYNyFMivKHeSe2s4_7YnkbxZPq_Ccrs';

---

編集が終わったら、実行ボタンを押せばスクリプトが動き出します。

問題なく実行されたら以下のとおりのメッセージが表示されます。

実行後、スプレッドシートを確認すると一列目にURLが更新されていると思います。

このリンクを開けば、回答データの再編集が可能です。

上記で実際に利用したスプレッドシートを共有いたします。
スクリプトの内容も確認いただけまので参照ください。

宿泊者名簿/Guest Registration サンプル（回答）

スクリプトをメニューに追加

上記のスクリプトは拡張機能からApps Script画面を開き実行できますが、使い勝手をよりよくするためにスプレッドシート画面にカスタムメニューを追加し、実行できるようにしました。
そのためのコードはこちらです。

/**
 * スプレッドシートを開いた際に、回答編集用URLを作成するFunctionを実行するメニューを追加する
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("カスタム処理")
    .addItem("回答データ編集URL作成", "updateEditResponseUrls")
    .addToUi()
}

このコードを追加し、スプレッドシートを開きなおすと以下のイメージのとおり、メニューからの実行ができます。

最後に

この記事はお気に入りいただけましたでしょうか?
内容お役にたちましたらうれしく思います。
また、サポートなど応援いただけましたら幸いです。