Search data in Google Spreadsheet

In Chat bot+, you can search content you entered in text form on Google Spreadsheet, and Reply back necessary information on the Chat.
Google Spreadsheet is a calculation software offered by Google, you can enter functions or create a graph like you do in Excel. The different point from Excel is that it can be edited at the same time and it can be saved automatically.
Here, it explains how to link Google Spreadsheet and Chat Plus.

Prepare Google Spreadsheet

Acquire API token

(1)Click the setting button on the upper right of the management page.

(2)Click [External Link]>[API input] on the left side of the screen.

(3) Click [Issue API token] on the left side of the screen.

(4)In the box "Password", enter Chat Plus's password and click [Issue API token].

(5)A message [Caution!] will appear, click [Issue] after confirming the contents.

(6)API token will appear in red, copy it.

　

Set Code

(1)Open Google Spreadsheet with data in it and click [Tool]>[Script Editor].

(2)An editor「function myFunction() { }」will appear, so copy the code below and paste it on the editor.

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 = "Enter search words";
  } 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 + "”could not be found"
    } else {
      message = "keyword: ”" + keyword + "” applicable address is " + lists + "n.";    //sheet[i][3]is applicable to email address.
    }
  }

  var sendToChat = {
    "to": room_id,
    "agent": agent,
    "messages": [{
    　　"type": "text",
    　　"text": message
    }],
    //APItoken
    "accessToken": "APItoken",
    "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)In the 40th row of pasted code, there is a code「"accessToken": "APItoken"」, so substitute 「APItoken」with the API token you copied in "Acquire API token" (6).
※Do not delete「""」


　

Reflect on Google Spreadsheet

(1)In the menu on Google Spreadsheet, click [Open]>[Introduce as Web application...].

(2)It will ask you to change project name, so change it to however you want it to be.

(3)The screen below will appear, from the pull down「Project version」, click 「New」, and from the pull down「Execute the app as」, click 「Me」, and from the pull down「Who has access to the app」, click 「Anyone, even anonymous」.
※After clicking 「New」from the pull down「Project version」, enter value in the box below.（Can be empty）

(4)Click [Deploy]

Depending on a situation, the screen below will show and you might need to [Confirm Authorization].

In that case, click [Confirm Authorization]>[Choose Account]>[Detail]>[Move to Add Data]>[Authorize], and that will authorize to access to Google Spreadsheet.

(5)The screen below will appear to let you know that [Deploy has been completed], so copy URL in the box 「Current web app URL」.

(6)Click [OK]

Prepare the Chatbot

(1)Click the setting button on the upper right of the management page.

(2)Click [Chat]>[Function]>[Chat bot+] on the left side of the screen.

(3)Click [Add parts]

(4)Enter "Part's name" and "Detail"
※Leave the box at the top right "ID" empty, it will automatically be filled when Chat bot+ is created.

(5)Click [Add rules]

(6)In the section "Display Rule", click 「User's remark」from the first pull down, enter 「Search」in the box below, and click 「Match」from the third pull down.

(7)In the section "Action", click [Text form]

(8) Click「postback(JSON)」from the pull down below (7), and paste 「Current web app URL」which you copied in Reflect on Google Spreadsheet(5) in the box "Sender URL"

(9)Check in the box "Include the chat information in POST data".

(10) In the box "Name" in the text form, enter valuable you set in Set Code(1), and in boxes [Labels] and [Candidate1, Candidate2,...] enter letters of things you want to put.
※In [Label], you should put a title of the content, and in [Candidates], you should put an example of the content.

(11)Click [Update]

(12) From the list of Chat bot+ rules, click [ON] the set rule.
※Click and it will switch [ON/OFF]

Action Confirmation

(1) Open the chat window and click [Search].

(2)Receiving (1)'s remark, the form to search data on Google Spreadsheet will appear in the chat.

(3) In the form appeared, enter names of what you want to know and click [Send].
※If you want to search [John Smith], you only have to put [J], [John] or [Smith] then the applicable information will appear.

(4) Please wait for a few seconds until the programming is done, 「keyword: "〇〇" applicable address is ------.」will appear as an operator's remark.
※If there are multiple applicable data, it will show them all.