好きなモノは酒と女とラーメン、LIFULLのてぃば(@rechiba3)です。

はじめに

この記事はLIFULL Advent Calendar2019 その2の最終日の記事です。

準備するもの

Googleドライブ: スプレッドシート
Googleドライブ: GASプロジェクト
- GASをウェブアプリケーション化して生成するWebhook URL
LINEアカウント: Developer登録しておく
LINE Bot: Messenger APIのチャンネル
LINE Bot: Messenger APIのChannelアクセストークン

1. LINEのポストをスプレッドシートへ反映させる

1-1. スプレッドシートの準備

Googleスプレッドシートを作成して、記入日、行きたい時期、行きたい場所、内容の4列に見出しをつけます。

つまり、値が入るのは2行目からということになります。

1-2. GASファイルの作成

ツール > スクリプトエディタよりスクリプトエディタを開きます。

CHANNEL_ACCESS_TOKENは空白のまま、
SPREADSHEET_IDには、先程作成したスプレッドシートのd/から/editまでの間の乱数をコピーして貼り付けます。

getSheetByNameには、任意のシート名を入力しても大丈夫です。
変更していない場合はそのままデフォルトと同じ名称を記入します。

GASコード

var CHANNEL_ACCESS_TOKEN = '【MessengerAPIのチャンネルアクセストークンが入ります】'; 
var SPREADSHEET_ID = '【スプレッドシートのIDが入ります】';

//ポストで送られてくるので、送られてきたJSONをパース
function doPost(e) {
  var json = JSON.parse(e.postData.contents);

  //返信するためのトークン取得
  var reply_token= json.events[0].replyToken;
  if (typeof reply_token === 'undefined') {
    return;
  }

  //送られたメッセージ内容を取得
  var message = json.events[0].message.text;  
  //LINEメッセージを「改行」で分割
  var messageParameter = message.split(/\r\n|\n/);

  //対象のスプレッドシートを取得して更新する
  var targetSs = SpreadsheetApp.openById(SPREADSHEET_ID);
  var targetSht = targetSs.getSheetByName('シート1');
  var lastRow = targetSht.getLastRow();
  //タイムスタンプ
  var date = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd');

  //各セルに書き込み
  targetSht.getRange('A' + (lastRow + 1)).setValue(date);
  targetSht.getRange('B' + (lastRow + 1)).setValue(messageParameter[0]);
  targetSht.getRange('C' + (lastRow + 1)).setValue(messageParameter[1]);
  targetSht.getRange('D' + (lastRow + 1)).setValue(messageParameter[2]);

  return ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON);
  }

1-3. LINEチャンネルの作成

https://developers.line.biz/console/を開き、Providerから任意のRoleを選択します。

チャンネル一覧が開くので、一番左のコンテナをクリックして、新しいチャンネルを作成します。

Messaging APIを選択して必要な情報を入力して進めていきます。
今回、私はチャンネル名を「倉持家の行きたいところリスト」にしました！

作成した後、またチャンネルリストに戻り、先程作成したチャンネルをクリック。
チャンネル名下部にタブメニューが現れるので、Messaging APIを選びます。

Messaging API settings画面から、
Auto-reply messages、Greeting messages を無効にできるLINE オフィシャルアカウント画面へ遷移できるので、これらを無効にしたい人は設定してもどります。

Messaging API settings画面に戻りまして、最下部にある、
Channel access tokenの乱数をコピーします。

この乱数を、先程のスクリプトエディタへ戻って
CHANNEL_ACCESS_TOKEN へ貼り付けます。

1-4. Webアプリケーションとして導入する

Googleスクリプトエディタへ戻ります。
公開 > ウェブアプリケーションとして導入をクリック。

Deploy as web app の設定を以下にします。
Execute the app as: me
Who has access to the app: Anyone, even anonymous

更新を押下。
諸々権限を求められるので許可します。

この画面が出たらアプリケーション化が完了です。
Current web app URLをコピーします。

コピーしたURLは、再度LINE Developer画面を開いて、
Webhook settings > Edit > 貼り付け > Verify で完了です。
Use webhookがONになっていなければONに変えます。

挙動が安定しないことがあったので、試しにページをリロードしてください。
リロードしても、Use webhookがONになっていないこともあるのでONになっているか確認します。

Messaging API settingsに記載されてるQRコードをスマートフォンのLINEアプリで読み込むと、友だちに追加できます。

1-5. スプレッドシートにLINEの発言を溜める

でで〜〜ん！
無事に表示されました👏

1-6. 応答メッセージを無効にする

再びhttps://developers.line.biz/console/を開き、Messaging APIを開きます。
LINE Official Account features > Auto-reply messages のEditを選び、LINE Official Account Managerを開きます。
応答設定 > 詳細設定 > 「応答メッセージ」をオフにすると、行きたいところを登録するたびにBotから返答されなくなります。

あえて、応答メッセージを設定する場合は「応答メッセージ」を選択して任意のメッセージを登録してください。

2. 行きたい場所から座標と住所を取得する

このままじゃ味気ないので、いつでもマッピングできるように座標と住所を取得したいと思います。

スプレッドシートの下部にある+アイコンの「シートを追加」を押下して新しいシートを作成します。
そのシートの名称をmap_exportにしてください。

map_exportは手順1では、"シート1"としていた部分です。
「シート1」で行きたいところを入るようにしていた、C列のセルに入力していた値を引っ張れるようにします。

あとは、A列に施設名、B、C列に座標、D列に住所が入るつもりで見出しをつけます。

2-1. GASファイルの作成

ファイル > New > スクリプトファイルから、新しいエディタを開きます。

CALは列番号なので、ABC…ではなく左から123…と番号が振られています。
何列目にどの値を入れるかを定義しています。

GAS

function geocoder() {
 const START_LOW = 1;
 const FACILITY_CAL = 1;
 const LAT_CAL = 2;
 const LNG_CAL = 3;
 const ADDRESS_CAL = 4;

 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('map_export');
 var lastLow = spreadsheet.getLastRow();

 for(var i=START_LOW; i<=lastLow; i++){

   var facility = spreadsheet.getRange(i,FACILITY_CAL).getValue();

   var geocoder = Maps.newGeocoder();
   geocoder.setLanguage('ja');//'en'にすれば結果データが英語になる

   var response = geocoder.geocode(facility);

   if(response['results'][0] != null){
     spreadsheet.getRange(i,LAT_CAL).setValue(response['results'][0]['geometry']['location']['lat']);
     spreadsheet.getRange(i,LNG_CAL).setValue(response['results'][0]['geometry']['location']['lng']);
     spreadsheet.getRange(i,ADDRESS_CAL).setValue(response['results'][0]['formatted_address']);

     /** 住所取得が複雑になるので未使用
     for(var j=0; j<=7; j++) {
       if(response['results'][0]['address_components'][j] != null){
         spreadsheet.getRange(i,ADDRESS_CAL + j).setValue(response['results'][0]['address_components'][j]['long_name']);
       }
     }
     **/
   }
 }
}