今までの記事一覧

前提

この記事は、フォーム回答を保存しているスプレッドシート側のGAS を前提にしています。
トリガーは以下を設定しています。

スプレッドシートから
フォーム送信時

おさらい

前回は、同時多発的にフォームを送信されても順番整理をしてくれる門番LockServiceを使い、スプレッドシートへの書き込みが完了するまで次の処理を待ってもらう方法、そして、万が一途中でエラーになってもエラーになったことを通知してもらい、門のカギを確実に返すtry catch finallyを使いました。

並んだ順ではなく、門前に並んだ走者がポーリング形式で鍵の確認をし、タイミングが合った走者が鍵を受け取る方式のため、順番は保証されないという、残念な現実もわかりました。

LockServiceの順番待ち　エラー・タイムアウト・鍵の返し忘れで暴かれた鍵取りゲームの実態

そこで今回は門番の気まぐれで順番が狂ってしまったとしても、どれが先だったかを後から確実に証明できる、揺るぎない「受付番号」を付与する方法を考えてみましょう。

簡単な方法：e.range.getRow()

フォームから「フォームの回答」シートに転記するときの行番号がe.range.getRow()です。
これはシステムが勝手に割り振っています。つまり、フォームからスプレッドシートに伝送された順に、システムが上から行番号を振り分けているということです。
これを受付番号として使います。

コードはとっても簡単。

function onFormSubmit(e) {

  //★フォーム回答が入力された行を取得
  const RecNo = e.range.getRow();

最初に e.range.getRow() を変数にいれておく。そして最後、スプレッドシートに転記するときに

  //★受付番号を入れたいところに”RecNo”を入れる（このコードではA列に受付番号が入る）
  sheet.appendRow([RecNo,timestamp,formData.mail,formData.name,formData.inq]);

このように、受付番号を入れたい列（このコードだと1列目（A列））に、e.range.getRow()が入っている変数を入れるだけ。

では動かしてみましょう

全体のコードは今回はこちら。
せっかくなので、前回使ったLockServiceやtry-catch-finallyも使います。

function onFormSubmit(e) {

  //★フォーム回答が入力された行を取得
  const RecNo = e.range.getRow();

  //ここで鍵をかける
  const lock = LockService.getScriptLock();
  
  try {     //★★　tryこの中でエラーが起きたら「catch」に入る
    lock.waitLock(30000);   //鍵をかける最大時間。1秒は1000   

    const formData = cleanFormData(e); 

    // 日付データ（スプレッドシート書き込み用）
    const timestamp = new Date(formData["タイムスタンプ"]);

      // 問い合わせ先部署によってシートを換える分岐処理
    const sheetMap = {
      "総務部": "総務部シート",
      "経理部": "経理部シート",
      "営業部": "営業部シート",
      "製造部": "製造部シート"
    };
    const targetSheetName = sheetMap[formData.dept] || "その他のシート";
    
    // スプレッドシートへの書き込み
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(targetSheetName);

    //★受付番号を入れたいところに”RecNo”を入れる（このコードではA列に受付番号が入る）
    sheet.appendRow([RecNo,timestamp,formData.mail,formData.name,formData.inq]);

   } catch (err) {   //「try」の中でエラーが起きたらここに入る
    
    const respondent = e.namedValues ? e.namedValues['名前'][0] : "不明なユーザー";
    const errorText = respondent + " さんの処理に失敗しました。";
    console.error(errorText + err);

    GmailApp.sendEmail("自分のメールアドレス",
      "【至急】フォーム仕分け失敗",
      errorText + "\n\nエラー内容: " + err);
    
  } finally {   //エラーが起きても起きなくてもこれをやる
    lock.releaseLock();
  }
  
}
//フォームの回答を扱いやすいオブジェクト形式に整形する自作関数
function cleanFormData(e) {
  const namedValues = e.namedValues;
  const data = {};

  // 全ての質問項目をループ
  for (let key in namedValues) {
    const value = namedValues[key][0];
    
    // もし質問の中に|mail|が入ってたら、一律で mail に入れる
    if (key.includes("|mail|")) {
      data.mail = value;   

    // もし|name|が入ってたら name に入れる
    } else if (key.includes("|name|")) {
      data.name = value;
    
    // もし|dept|が入ってたら dept に入れる
    } else if (key.includes("|dept|")) {
      data.dept = value;
    
    // もし|inq|が入ってたら inq に入れる
    } else if (key.includes("|inq|")) {
      data.inq = value;
    }

    // 質問文そのままでも呼び出せるように保存
    data[key] = value;
  }

  return data;
}

このフォームを送信

「フォームの回答」シートにはこのようにデータが入り

部署別シートではこのように受付番号「6」がA列に入りました！

※appendRowは、同時に処理が走ると順番が入れ替わることがあります。安全に使うために、LockServiceとセットで使うのがおすすめです。

行番号の弱点

最初が「１」じゃない：見出し行があるので、最初は「２」から始まります。テスト送信をたくさんやると、本番の受付番号がいきなり「54」とかになるかも。本番での受信数を数えたいニーズもあるなら「RecNo - 54」みたいにしたほうがいいかもしれません。
行削除に弱い：「フォームの回答」シートで行削除すると、その次からの送信では削除後の行番号が受付番号になってしまいます。昨日の送信は「105」だったのに、50行削除したあとの今日の送信は「55」に戻ってしまうということです。
（Googleフォームは常に「データがある最終行の次」へ書き込むため、行を詰めると番号も詰まってしまうのです）
これを防ぐには、後述する「ミリ秒」と組み合わせるか、「これまでの最大番号を調べて+1する」など、もう1段階複雑な処理が必要になります。

やや簡単：実行時刻（ミリ秒）を受付番号にする

ミリ秒とは？

Unixタイムスタンプのことで、1970年1月1日から数えた合計ミリ秒数。こんな感じの1744167876000という数字として表現されます。
つまり、1000分の1秒単位で同時送信しない限り同じ番号になることはないのです。

GASの冒頭にこれを入れる。これによって、このGASにたどり着いた日時がミリ秒単位で取得できる。

// ★実行時間をミリ秒（数値）に変換
const RecNo = new Date().getTime();

スプレッドシートに転記するコード（ここは先ほどの行番号式と同じ）

  //★受付番号を入れたいところに”RecNo”を入れる（このコードではA列に受付番号が入る）
  sheet.appendRow([RecNo,timestamp,formData.mail,formData.name,formData.inq]);

万が一順番が入れ替わった場合に正しい順番に並べ替えるコードもついでに入れておきましょう。
これをsheet.appendRowの下に入れます。

    // ★一度スプレッドシートに変更を「確定」させる
    SpreadsheetApp.flush(); 

    // ★並べ替え範囲を取得（2行目から、データがある最終行まで）
    var lastRow = sheet.getLastRow();
    if (lastRow > 2) { // データが2件以上ある場合のみ実行
      var data = sheet.getRange(2, 1, lastRow - 1, 5); // 範囲の「高さ」は lastRow - 1
      data.sort({column: 1, ascending: true}); 
    }

全体のコードはこちら（自作関数cleanFormDataは省略）

function onFormSubmit(e) {
  //★処理が始まった日時をミリ秒（数値）に変換
  const RecNo = new Date().getTime();   

  //ここで鍵をかける
  const lock = LockService.getScriptLock();
  
  try {     //tryこの中でエラーが起きたら「catch」に入る
    lock.waitLock(30000);   //鍵をかける最大時間。1秒は1000   

    const formData = cleanFormData(e); 

    // 日付データ（スプレッドシート書き込み用）
    const timestamp = new Date(formData["タイムスタンプ"]);

    // 問い合わせ先部署によってシートを換える分岐処理
    const sheetMap = {
      "総務部": "総務部シート",
      "経理部": "経理部シート",
      "営業部": "営業部シート",
      "製造部": "製造部シート"
    };
    const targetSheetName = sheetMap[formData.dept] || "その他のシート";
    
    // スプレッドシートへの書き込み
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const sheet = ss.getSheetByName(targetSheetName);

    //★受付番号を入れたいところに”RecNo”を入れる（このコードではA列に受付番号が入る）
    sheet.appendRow([RecNo,timestamp,formData.mail,formData.name,formData.inq]);
    
    // ★一度スプレッドシート上で変更を確定させる
    SpreadsheetApp.flush(); 

    // ★並べ替え範囲を取得（2行目から、データがある最終行まで）
    var lastRow = sheet.getLastRow();
    if (lastRow > 2) { // データが2件以上ある場合のみ実行
      var data = sheet.getRange(2, 1, lastRow - 1, 5); // 範囲の「高さ」は lastRow - 1
      data.sort({column: 1, ascending: true}); 
    }

   } catch (err) {   //「try」の中でエラーが起きたらここに入る
    
    const respondent = e.namedValues ? e.namedValues['名前'][0] : "不明なユーザー";
    const errorText = respondent + " さんの処理に失敗しました。";
    console.error(errorText + err);

    GmailApp.sendEmail("自分のメールアドレス",
      "【至急】フォーム仕分け失敗",
      errorText + "\n\nエラー内容: " + err);
    
  } finally {   //エラーが起きても起きなくてもこれをやる
      lock.releaseLock();//鍵を返す
  }
}

シートにはこのように表示されます。
単純な数字ではなく、なんとなく暗号っぽい番号にしたい場合にもこの方法は良いかもしれませんね。

※受付番号の数字が1.74E+12のようになってしまう場合は、表示形式を「数値」にするか、
sheet.appendRow([RecNo,timestamp,formData.mail,formData.name,formData.inq]);これを
sheet.appendRow(["'" + RecNo, ...])のようにシングルクォーテーションを付けて文字列として書き込むと回避できます。

ミリ秒式の弱点

送信時間ではない：上記のコードでは、フォームの送信時間ではなくGASが処理を始めた時刻を取得しています。フォームの「タイムスタンプ」は秒精度のため、これを使った場合、同時送信があると同じ値になる可能性があるためです。
ミリ秒単位で同時送信の可能性：可能性は限りなく低いですが、ミリ秒単位で同時に処理が開始された場合、番号が同じになります。

まとめ

行番号式

メリット：

とにかくシンプルで見た目にわかりやすい。

デメリット：

最初を「1」にするには少し工夫が必要。
「フォームの回答」シートを行削除したり並べ替えたりすると壊れる。シートをロックしたり非表示にしたりなどの保護をしたほうがいいかも。

ミリ秒式

メリット：

受付タイムが確実なシリアル値として残る。
単純な数字ではなく、シリアルナンバーっぽく見せたいときに使える。

デメリット：

ミリ秒単位で同時処理になると同じ数値になる。

というわけで、実務では、ミリ秒と行番号を組み合わせた「ハイブリッド方式」がもっとも安全と言えるでしょう。
ミリ秒がカブっても「行番号」が仕分けてくれ、行削除で番号がズレても「ミリ秒」が時間を証明してくれます。

それでも行削除に負けない連番が欲しい！という方　「行を消しても番号が飛ばない」「最初は必ず1から始まる」というちょっと上級者な方法もあります。せっかくなので次回、お話ししますね。（近日公開予定）

この記事が皆さまのお役に立てたら幸いです。