見出し画像

[全自動] 食べログのURLがSlackに投稿されたら店舗の基本情報やオフィスからの所要時間をスプレッドシートに記録する

石渡貴大@マインディア

なぜ作ったの？

オススメのランチ情報や会食の場所の共有などでSlackに食べログのリンクを共有することありますよね？
身近な人のオススメでかなり信頼できるリストになりますが、Slackの情報は基本的にフローで流れていってしまうので、せっかくならちゃんとストックしていきたい。そして、リスト化しても最寄り駅や住所の情報だけだとランチで行ける範囲か分かりづらいので、オフィスからの所要時間を記載しておくことで、ランチで行ける店かどうかの検索も簡単にできるといいなと思い、作りました。

書いている人

株式会社マインディア というスタートアップでBtoC・BtoB両方のマーケティングをしています。

twitterアカウント @takahirostone
ぜひフォローしてくださいー！

できること

こんなふうにSlackに食べログのリンクが投稿されたら

武蔵野市のおいしいケーキ屋さんです

自動でスプレッドシートに記録していきます。

スプレッドシートはこんな感じです

Slack投稿だけではなく、スプレッドシートに直接URLを記載して動かすこともできます。

もちろん、通常のスプレッドシートと同じで自由にフィルタをかけて使用できます。ジャンルで絞ったり、オフィスからの所要時間で絞ってランチで行ける範囲に限定して検索したりできます。

スプレッドシートの項目

URL

Slackに投稿 (スレッド内も含む) されたテキストから食べログのURLを抽出します。
下層ページ (メニューや地図のページとか) の場合は店舗トップページに正規化します。モバイル用のURL (s.tabelog.com) はPC用のURL (tabelog.com) に正規化します (AMPのURLは対象外です。そもそも食べログのAMPまだあるのか分かりませんが)。
1つの投稿に複数店舗のURLが含まれる場合は1店舗ずつに分割して行を追加します。
すでに掲載されているものは重複して掲載しないようにしています。

コメント

投稿されたテキストから食べログのURLを除いた部分です。
スプレッドシートに直接書き込んで使う場合はここは手入力 (しなくてもよい) です。

紹介者

投稿者がSlackに登録しているFull Nameです。
同じく、スプレッドシートに直接書き込みのときは手入力 (しなくてもよい) です。

店名、点数、ジャンル、最寄り駅、予算（夜）、予算（昼）

食べログに掲載されている基本情報です。
クローリングを行うのは「URLが入っていて店名が入っていないところから下」を対象としていますので、手入力で動かす場合は店名以降は入力しないでください。

オフィスからの所要時間（分）

オフィスから行った場合に公共交通機関 + 徒歩でかかる時間です。もちろん、GASに書いておく出発地を変更すればオフィス以外の場所を起点に所要時間を計算することもできます。

仕組み

  1. SlackのAppを作って「link_shared」のイベントを受け取る

  2. GASにPOSTする

  3. 食べログから基本情報をスクレイピングする

  4. NAVITIMEのAPIを利用して移動所要時間を取得する

  5. スプレッドシートに記録する

注意事項

  • 食べログの利用規約ではスクレイピングは禁止されていません (2022/10/20現在) が、利用は節度をもって自己責任でお願いします。

  • ナビタイムのAPIは無料では月間500リクエストまで利用可能です。超えた場合は勝手に課金されるわけではなく利用できなくなるだけです。

作成手順

スプレッドシートの作成

冒頭で添付した形式のカラムを持ったスプレッドシートを作成します。このスクリプトでは別シートに保存してあるSlackのMember IDとFull Nameの一覧を利用していますので、同じことをやる場合は一覧表のシートも作っておく必要があります。それもAPIで1日1回更新するようにしておくと楽です。

参考↓

Slack Appの作成

こちらからSlackのAppを作ります。設定のポイントは以下です。

  • Event Subscriptions

    • Subscribe to bot eventsでlink_sharedを選択

    • App unfurl domainsに「tabelog.com」を入力

  • OAuth & Permissions

    • Bot Token Scopesでchannels:historyを選択 (links:readも必要ですが、↑のEvent Subscriptionsの設定で勝手についてるはず)

    • Bot User OAuth Tokenをコピーしておく

  • App Home

    • App Display NameとDisplay Nameを好きな内容で設定

  • Basic Information

    • Display Informationでアイコンを設定 (しなくてもいい)

    • Install your appでワークスペースにAppをインストール

NAVITIME APIの利用申請

こちらからアカウントを作成しNAVITIMEのAPIを利用できるようにします。必要なのは「NAVITIME Geocoding」と「NAVITIME Route(totalnavi)」の2つです。利用できるようになったら「X-RapidAPI-Key」をコピーしておいてください。
ちなみに、Geocodingで店舗住所から緯度経度の取得、Routeで出発地 (予め緯度経度で指定) からの所要時間の取得を行います。僕が使っている範囲だと正しそうですが、もしかしたら一部食べログに記載の形式の住所でNAVITIMEで検索すると違う緯度経度になってしまうとかはあるかもしれませんのでご注意ください。また、Routeの検索にはオプションがいろいろあります (使わない交通機関とか、時間と乗り換え回数のどちらを優先するかとか)。このあたりを変更したい方は↑の公式ドキュメントを読んでください。

GASの作成

スクリプト（後述）をコピペしてGASを作成します。前の手順でコピーしたSlackのBot User OAuth TokenやNAVITIMEのX-RapidAPI-Keyが必要です。

できたらウェブアプリとしてデプロイします。「アクセスできるユーザー」は「全員」にしてください。そこで発行されたURLを記録しておきます。

Request URLの登録

Slack Appに戻りEvent SubscriptionsのRequest URLに先程のGASのウェブアプリのURLを入力します。

SlackのchannelにAppを追加

Appが追加されたchannelでしか動きません。randomやグルメな人のtimesなど、よく食べログのURLが投稿されるところにAppを追加しておくのがオススメです。もちろん複数channelに入れても大丈夫です。ちなみに、試してないのですが、private channelでもAppが追加されていたら動くと思います (オススメはしません)。

記録したい店舗をSlackに投稿

あとは自由に投稿するだけで勝手に記録されていきます。

スクリプト

基本的にはコピペで使えますが、トークンや経路検索の出発地などは修正が必要です。修正すべき箇所は「*******」にしていますので、どこを修正するか分からなくなったら検索してください。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('********');  //シートの名前

function doPost(e) {
  const paramsString = e.postData.getDataAsString();
  const params = JSON.parse(paramsString);

  if (params.type == 'url_verification') {  //Slackとの通信の確認用
    return ContentService.createTextOutput(params.challenge);
  } else if(params.event.source == 'conversations_history') {  //conversations_historyを指定しないと投稿されていない下書きの段階で反応してしまう
    const ts = (params.event.thread_ts) ? params.event.thread_ts : params.event.message_ts;
    const channel = params.event.channel;
    const arr = getMessage(ts, channel);

    if(arr.length) {
      let data = [];
      const lastRow = sheet.getLastRow();
      if(lastRow != 1) {
        const urlRange = sheet.getRange(2, 1, lastRow - 1, 1);
        const urlValues = urlRange.getValues().flat().flat();
        for(i = 0; i < arr.length; i++) {
          if(urlValues.indexOf(arr[i][0]) == -1) {
            data.push(arr[i]);
          }
        }
      } else {
        data = arr;
      }

      if(data.length) {
        const range = sheet.getRange(lastRow + 1, 1, data.length, 3);
        range.setValues(data);
        fillSheet();
      }
    }
  }
}


function getMessage(ts, channel){
  const url = 'https://slack.com/api/conversations.replies';
  const slack_app_token = '*******';  //SlackのBot User OAuth Token
  const options = {
    "method" : "get",
    "contentType": "application/x-www-form-urlencoded",
    "payload" : { 
      "token": slack_app_token,
      "channel": channel,
      "ts": ts
    }
  };
  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response);

  if(json.ok == true) {
    const message = json.messages[json.messages.length - 1];
    const user = message.user;
    const text = message.text;
    
    const regEx = /https:\/\/(s\.|)tabelog\.com\/[a-z]+\/A\d{4}\/A\d{6}\/\d{8}/g;
    const tabeUrls = text.match(regEx);

    const repRegEx = /\n*?<https:\/\/(s\.|)tabelog\.com\/[a-z]+\/A\d{4}\/A\d{6}\/\d{8}.*>/g;
    const noUrlText = text.replace(repRegEx ,'');

    const repTabeUrls = [];
    for(tabeUrl of tabeUrls) {
      if(tabeUrl.match(/s\./)) {
        const repUrl = tabeUrl.replace('s.', '');
        repTabeUrls.push([repUrl, noUrlText, user]);
      } else {
        repTabeUrls.push([tabeUrl, noUrlText, user]);
      }
    }
    return repTabeUrls;
  } else {
    return [];
  }
}


function fillSheet() {
  const lastRow = sheet.getLastRow();
  const nameRange = sheet.getRange(1, 4, lastRow, 1);
  const nameValues = nameRange.getValues();
  let filledNum = 0;
  for(i = 0; i < nameValues.length; i++) {
    if(nameValues[i] != '') {
      filledNum++;
    }
  }
  const unfilledNum = lastRow - filledNum;
  const urlRange = sheet.getRange(filledNum + 1, 1, unfilledNum, 1);
  const urlValues = urlRange.getValues().flat();
  const commentRange = sheet.getRange(filledNum + 1, 2, unfilledNum, 1);
  const commentValues = commentRange.getValues().flat();
  const userRange = sheet.getRange(filledNum + 1, 3, unfilledNum, 1);
  const userValues = userRange.getValues().flat();
  const userNameSheet = ss.getSheetByName('*******');  //Member IDとFull Nameが記載されたシート名
  const userNameLast = userNameSheet.getLastRow();
  const userNameRange = userNameSheet.getRange(1, 1, userNameLast, 1);
  const userNameValues = userNameRange.getValues().flat();
  const userIdRange = userNameSheet.getRange(1, 2, userNameLast, 1);
  const userIdValues = userIdRange.getValues().flat();

  const arr = [];

  for(i = 0; i < unfilledNum; i++) {
    if(i != 0) {
      Utilities.sleep(3 * 1000);  //スクレイピングの間隔を空ける
    }
    const rawUrl = urlValues[i];
    const regEx = /https:\/\/(s\.|)tabelog\.com\/[a-z]+\/A\d{4}\/A\d{6}\/\d{8}/;
    const url = rawUrl.match(regEx)[0].replace(/s\./, '');
    const comment = commentValues[i];
    const user = userValues[i];
    const userNameNum = userIdValues.indexOf(user);
    const userName = (userNameNum == -1) ? user : userNameValues[userNameNum];
    const html = UrlFetchApp.fetch(url);
    const text = html.getContentText("utf-8");
    const name = text.match(/<h2 class="display-name">\n\s*<span>\n\s*(.+)\n\s*<\/span>/)[1];
    const score = text.match(/<span class="rdheader-rating__score-val-dtl">(\d\.\d{2})<\/span>/)[1];
    const genre = text.match(/<th>ジャンル<\/th>\n\s*<td>\n\s*<span>(.+)<\/span>/)[1];
    const station = text.match(/<span class="linktree__parent-target-text">(.+)<\/span>/)[1];
    const dinnerBudget = (text.match(/<i>\[夜\]<\/i><em class="gly-b-dinner">(.+)<\/em>/)) ? text.match(/<i>\[夜\]<\/i><em class="gly-b-dinner">(.+)<\/em>/)[1] : '-';
    const lunchBudget = (text.match(/<i>\[昼\]<\/i><em class="gly-b-lunch">(.+)<\/em>/)) ? text.match(/<i>\[昼\]<\/i><em class="gly-b-lunch">(.+)<\/em>/)[1] : '-';
    const address = encodeURIComponent(text.match(/<p\sclass="rstinfo-table__address">(.|\s)*?<\/span>\s<span>/)[0].replace(/<\/span>\s<span>/, '').replace(/<(.|\s).*?>/g, ''));
    const time = fetchTime(address);
    arr.push([url, comment, userName, name, score, genre, station, dinnerBudget, lunchBudget, time]);
  }

  const fillRange = sheet.getRange(filledNum + 1, 1, unfilledNum, 10);
  fillRange.setValues(arr);
}

function fetchTime(address) {
  const coordOptions = {
    method: 'GET',
    headers: {
      'X-RapidAPI-Key': '*******',  //NAVITIME APIのX-RapidAPI-Key
      'X-RapidAPI-Host': 'navitime-geocoding.p.rapidapi.com'
    }
  };
  const coordRes = UrlFetchApp.fetch('https://navitime-geocoding.p.rapidapi.com/address?coord_unit=degree&datum=wgs84&limit=1&level_from=1&level_to=7&word=' + address + '&sort=code_asc&offset=0', coordOptions);
  const coordResText = coordRes.getContentText();
  const coordResJson = JSON.parse(coordResText);
  if(!coordResJson.items.length) {
    return '-';
  }
  const lat = coordResJson.items[0].coord.lat;
  const lon = coordResJson.items[0].coord.lon;
  const coord = lat +'%2C' + lon;

  const timeOptions = {
    method: 'GET',
    headers: {
      'X-RapidAPI-Key': '*******',  //NAVITIME APIのX-RapidAPI-Key
      'X-RapidAPI-Host': 'navitime-route-totalnavi.p.rapidapi.com'
    }
  };
  const timeRes = UrlFetchApp.fetch('https://navitime-route-totalnavi.p.rapidapi.com/route_transit?start=*******%2C*******&goal=' + coord + '&start_time=2022-10-17T10%3A00%3A00&datum=wgs84&term=1440&limit=1&coord_unit=degree', timeOptions);  //startには出発地にしたい地点の緯度と経度が入ります
  const timeResText = timeRes.getContentText();
  const timeResJson = JSON.parse(timeResText);
  const time = timeResJson.items[0].summary.move.time;

  return time;
}

次のコードはスプレッドシートにメニューを追加するためのスクリプトです。Slack投稿ではなくスプレッドシートに直接記載されたURLから情報を収集するために使います。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('*******');  //好きな名前をつけてください
  menu.addItem('*******', 'fillSheet');  //好きな名前をつけてくださ
  menu.addToUi();
}

できないこと

  • 食べログのURLが含まれていたら自動的に収集します。「この店美味しくなかった」みたいな投稿でもそれも収集してしまいます。

  • 食べログには海外の店舗も掲載されています。基本情報は海外店舗でも問題なく収集できますが、NAVITIME APIでは海外の住所の検索ができませんでした。

    • Google MAPのAPIが使えたら良かったのですが、日本では乗り換え案内に対応しておらず徒歩と車のみでした。(そもそも海外店舗への移動時間を計算して意味あるのかという話はありますね)

  • App経由の投稿 (例えばCollaとか) の場合は収集されるケースとされないケースがあります。

    • Appによって構造が違うので全てのパターンに対応するのはむずそうです。Collaだけとかなら作れます。

  • 一度記載された情報は食べログで変更があってもスプレッドシート上では変わりません。

    • 特に点数はリアルタイムで同期してほしいケースもあるかもしれません。その場合は値を直接書くのではなくIMPORTXML関数にしておくことで対応できそうですが、IMPORTXMLのセルが増えると「Loading…」となることもあるので逆に見づらくなってしまい、トレードオフです。

  • GASは実行時間の制限が6分までです (実際には6分超えても動いていることがあって謎なのですが)。食べログのスクレイピングに3秒間のsleepを設定しているのと、NAVITIMEのAPIの往復に若干時間がかかるので、おそらく1回に動かせるのは50店舗分くらいまでかなと思います。基本的には社内のSlackから記録されることを想定しているので、そこまで大量の店舗を同時にクローリングすることを想定していなくて、タイムアウトしたときに途中でデータを保存しておくようなこともしていませんので、タイムアウトした場合は店舗数を減らしてやり直してください。

応用

  • 食べログに限らず、特定のドメインのURLが投稿されたらスプレッドシートに記録するという使い方ができます。

  • twitterのAPIを使って検索すれば、特定の人がtwitterに投稿した食べログのURLを使うこともできます。

最後に

今回の記事が参考になった方は、ぜひnoteのスキ、twitterのフォローをしていただけると嬉しいです！！

Meetyも開けてますので、ご興味ある方いらっしゃればぜひお話しましょう！

よろしければサポートお願いします！そのうちオリジナルドメインにしたいなと思っているのでその資金にさせていただきます！