Googleスプレッドシートのデータを検索する方法

QGoogleスプレッドシートのデータを検索する方法

チャットボット+では、テキストフォームに入力した内容を、Googleスプレッドシートに検索をかけ、必要な情報をチャット上に返すことができます。
GoogleスプレッドシートとはGoogle社が提供している表計算ソフトで、Excelの様に関数を入力したり、グラフを作成できます。またExcelと異なる点としては、同時編集が可能なことや自動保存機能が備わっていることです。大変便利なこのGoogleスプレッドシートとChatPlusを連携させる方法を、今回ご案内します。

このページでは、Googleスプレッドシートに保有している会社名、担当者、電話番号、メールアドレスのリストに、ChatPlusからキーワードを送信し、該当するワードを含む会社情報を返答する手順をご案内します。
App Scriptの記述方法はサンプルとしてご確認ください。（お客様のご要望に沿った記述方法は通常サポート外となります。）

　
※分かりやすくするために、以下の条件で設定します。
・キーワードを入力できるテキストフォームは1つ
・送信されたキーワードを会社名、担当者、電話番号、メールアドレス全ての要素で検索する
・少しでも一致する箇所が存在した場合、その会社のメールアドレスを担当者発言として返答する

１．Googleスプレッドシートの準備

１－１．APIトークンの取得

(1) 管理画面右上の設定ボタンをクリックします。

(2) 画面左「外部連携」＞「API入力」をクリックします。

(3) 画面左「APIトークン発行」をクリックします。

(4) パスワード欄に管理画面ログイン時のパスワードを入力し、「APIトークン発行」ボタンをクリックします。

※APIトークンは再発行できるので、もし不正利用の可能性がある場合は再発行してください。
再発行を実行した場合は、以前のAPIトークンはすべて無効となります。

１－２．コードの設置

(1) 内容を記録するGoogleスプレッドシートを開き、メニューから「拡張機能」＞「Apps Script」をクリックします。
※Googleスプレッドシートがない場合は、新しく作成してください。

(2)「function myFunction() { }」と書かれたエディタが表示されるので、以下のコードをコピーし、エディタに上書きしてペーストします。

function doPost(e) {
  var jsonString = e.postData.getDataAsString();
  var data = JSON.parse(jsonString);

  var keyword = data.word;
  var room_id = data.room_id;
  var agent = data.agent;
  var site_id = data.siteId;

  var ss = SpreadsheetApp.getActiveSheet();
  var sheet = ss.getDataRange().getValues();

  var message;
  if (keyword == "") {
    message = "検索ワードを入力してください。";
  } else {
    var lists = [];
    for (var i = 0; i < sheet.length; i++) {
      var word = new RegExp(keyword, 'g');
      var search = word.test(sheet[i]);
      if (search != false) {
        lists.push("n" + sheet[i][3]);
      }
    }
    if (lists.length == false) {
      message = "”" + keyword + "”に該当する項目はありません。"
    } else {
      message = "キーワード: ”" + keyword + "” に該当する連絡先は" + lists + "nです。";    //sheet[i][3]がメアドに該当する
    }
  }

  var sendToChat = {
    "to": room_id,
    "agent": agent,
    "messages": [{
    　　"type": "text",
    　　"text": message
    }],
    //APIトークン
    "accessToken": "APIトークン",
    "siteId": site_id
  };
  
  var options = {
    'method': 'POST',
    'contentType': 'application/json',
    'payload': JSON.stringify(sendToChat)
  };
  
  UrlFetchApp.fetch('https://app.chatplus.jp/api/v1/send', options);
}

(3) ペーストしたコードの40行目に「"accessToken": "APIトークン"」と書かれたコードがあるので、「APIトークン」という文字を、手順「１－１．APIトークンの取得」(6)でコピーしたAPIトークンで上書きします。
※「""」は削除せず、そのまま残します。

１－３．Googleスプレッドシートに反映

(1) Googleスプレッドシートのメニューから、「デプロイ」＞「新しいデプロイ」をクリックします。

(2) 設定ボタンをクリックし、展開されたメニューから「ウェブアプリ」をクリックします。

(3) 以下の画面が表示されるので、
「新しい説明文」枠内に任意の文字列を入力し、
「次のユーザーとして実行」のプルダウンから「自分」を、
「アクセスできるユーザー」のプルダウンから「全員」を、クリックして選択します。

(4)「デプロイ」ボタンをクリックします。

■下図の画面が表示され、アクセス承認を求められた場合

①「アクセスを承認」ボタンをクリックします。

② アクセスを承認するアカウントをクリックします。

③「高度な」をクリックします。

④「無題のプロジェクトへ（危険）」をクリックします。

⑤「許可する」ボタンをクリックします。

(5) デプロイが完了したことを伝える画面が表示されるので、ウェブアプリの「コピー」をクリックして、URLをコピーします。

(6)「完了」ボタンをクリックします。

これでGoogleスプレッドシートでの準備は終了です。
続いて、ChatPlusでの設定に移ります。

２．チャットボット＋の設定

(1) 管理画面右上の設定ボタンをクリックします。

(2) 画面左「チャットボット」＞「チャットボット＋」をクリックします。

(3)「パーツ追加」をクリックします。

(4)「パーツ名」「概要」を入力します。
※画面右上のIDの欄は空欄で構いません。チャットボットプラス作成時に自動でIDが割り振られます。

(5)「ルールを追加」ボタンをクリックします。

(6) 表示ルールのプルダウンから「訪問者の発言」をクリックして選択し、2つ目の枠内に「検索」と入力、3つ目のプルダウンから「一致する」をクリックして選択します。

(7)アクションのプルダウンから「テキストフォーム」をクリックして選択します。

(8)「送信時の動作」のプルダウンから、「postback(JSON)」をクリックして選択し、「送信先URL」枠内に、上記手順「１－３．Googleスプレッドシートに反映」(5)でコピーしたURLを入力します。

※「POSTデータにチャット情報を含める」にチェックを入れると、POSTデータにチャット情報を含めます。

(10) 手順(7)で「テキストフォーム」をクリックすると、下に「フォーム」という要素が表示されます。
この「フォーム」に入力されたデータを、ペーストしたURLに送信して、Googleスプレッドシート内を検索します。
1つ目の「訪問者属性名」枠内に、「word」と入力します。
2つ目の「ラベル」枠内に、入力する内容を表すタイトルを入力します。
3つ目の「候補1, 候補2, ...」枠内に、入力する内容の例を書いて、送信者が分かりやすくします。