【GASで平等】問い合わせに対する担当者を自動アサインする

問い合わせフォームが送信されたときに、案件担当者を自動的にアサイン、通知するGoogle Apps Scriptです。人間が割り振るとエコヒイキ等の問題が出たり出なかったりしますので、コードに任せてしまいたいときに。

こんな悩みを解決します

• 問い合わせに対する案件担当者を自動的にアサインしたい！
• 文句を言われないように公平に配分したい！

用意するもの

特にありません。スプレッドシートとフォームで実現できます。

このスクリプトで出来ること

• お問合せフォームが送信されると、担当者を自動的にアサインしてくれます。
• 担当者が選ばれる基準は、前回のアサインから最も時間が経過している人です。
• アサインされた担当者名はシートに追記され、メールも送信されます。

使い方

1.事前準備

まずはフォームを作成します。
件名、問い合わせ内容、名前、メールアドレスのみのシンプルなフォームです。

次に担当者情報を登録します。
氏名、出欠状況、担当者メールアドレス、前回アサイン日時の4項目が必要です。
当日お休みの人は出社フラグを「FALSE」にしてください。アサインされなくなります。
このサンプルデータではBさんの最終アサイン日時が最も古くなっていますが、出社フラグがFALSEなので、次点のAさんがアサインされるはずです。

フォームから送信された問い合わせ内容を記録するシートも用意します。担当者情報と同じスプレッドシートに作成してください。
B列の担当者はGASにより選出された担当者名が入ります。

続いてコードの「事前設定エリア」を書き換えます。
担当者情報と問い合わせ内容を記録するスプレッドシートのURLと、それぞれのシート名をセットしてください。

コードを変更して保存したら、フォーム送信時にGASが実行されるようにトリガーをセットしましょう。

• 実行する関数: onSubmit
• デプロイ時に実行: HEAD
• イベントのソース: フォームから
• イベントの種類: フォーム送信時

事前準備が長かったですね・・おつかれさまでした！

2.スクリプトの実行

さっそく動作確認してみましょう。
適当な内容でフォームを送信します。

3.実行結果の確認

「問い合わせリスト」シートを確認すると、フォームの内容が反映されています。
Aさんが担当者として選ばれましたね！

担当者情報シートでは今回アサインされたAさんの最終アサイン日時が更新されました。

アサインされたAさんのメールアドレスには、このような通知メールが送信されます。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00126/index.html
 */

function onSubmit(e) {

  // ########## 事前設定エリア ここから ##########

  const ss_url = 'https://docs.google.com/spreadsheets/d/*********************';

  const sheet_name_member = 'メンバーリスト';
  const sheet_name_inquiry = '問い合わせリスト';

  // ########## 事前設定エリア ここまで ##########


  // フォームの項目や回答を取得する
  const form_responses = e.response.getItemResponses();

  // フォームの回答だけを格納する配列を用意
  const answers = [];

  // フォームの項目数だけループする
  for (var i = 0; i < form_responses.length; i++) {

    // 回答を取得し、配列に追加
    let answer = form_responses[i].getResponse();
    answers.push(answer);

  }

  // メンバーおよび問い合わせ一覧のスプレッドシートオブジェクトを取得する
  const ss = SpreadsheetApp.openByUrl(ss_url);

  // メンバーシートのオブジェクトを取得
  const sheet_member = ss.getSheetByName(sheet_name_member);

  // メンバー一覧を取得する
  const values_member = sheet_member.getRange(1, 1, sheet_member.getLastRow(), sheet_member.getLastColumn()).getValues();

  // 最終アサイン日時が最も古いメンバーをアサインする。日付でソートするため配列を用意。
  const array = [];

  // 担当者アサイン処理
  for (let i = 1; i < values_member.length; i++) { // 1行目は項目名なのでiの開始値を1にして飛ばす

    /* シートには下記列が存在する
    A列 担当者
    B列 出社フラグ(Boolean)
    C列 メールアドレス
    D列 最終アサイン日時
    */

    // 欠勤の人は候補から外す
    if (values_member[i][1] == false) {
      continue;
    }

    // ソートするため、i（=行番号）と日付を配列に追加する
    array.push([i, values_member[i][3]]);
  }

  // 日付順で昇順ソートする（ソート後の配列の先頭が最も古い日付になる）
  array.sort(
    function (a, b) {
      if (a[1] > b[1]) {
        return 1;
      } else {
        return -1;
      }
    }
  );

  // 担当者の最終アサイン日時を更新
  sheet_member.getRange(array[0][0] + 1, 4).setValue(new Date());

  // わかりやすさのためアサインされた担当者の行を変数に入れ直す（pic = person in charge）
  const row_pic = values_member[array[0][0]];

  // 問い合わせ一覧に追記する内容を準備（日付、担当者、フォームの内容）
  let row = [new Date(), values_member[array[0][0]][0]];
  row = row.concat(answers); // フォームの内容を追加する

  // 問い合わせリストシートに行を追加する
  const sheet_inquiry = ss.getSheetByName(sheet_name_inquiry);
  sheet_inquiry.appendRow(row);

  // 担当者へメールを送信する。メール本文の作成。
  const mail_body = `
  以下の問い合わせがありました。
  あなたが担当者としてアサインされました。対応をお願いします。

  件名: ${answers[0]}
  内容: ${answers[1]}
  名前: ${answers[2]}
  メールアドレス: ${answers[3]}
  `

  // メール送信処理
  // 件名にはフォームで送信された件名、本文にはフォームの内容を記載する
  GmailApp.sendEmail(row_pic[2], '案件アサイン:' + answers[0], mail_body);

}

関連するアプリ