[Googleスプレッドシート][GAS]引越し候補物件をスマートに整理しよう Sprint2

はじめに

背景

• Sprint1で浮上したUrgent課題である「毎回計算が走る」問題に対応する必要がある...

この記事の目的

• 「毎回計算が走る」問題を解消する
• 並行して、作業断面でのSuumoデータをスプレッドシートに保存・蓄積する仕様に変更する

現状の整理

毎回計算走る問題への対応

フローは以下のような状況。問題は　【①値の挿入・セルの操作】　ごとに後続処理が走ってしまうため、なにかトリガーのようなものを設定する必要がありますね。

スプレッドシートの図形トリガー機能を使えば、難なく解決できそうなので、サクッとやってしまいましょう！

掲載終了物件への対応

また、Suumoの物件情報の掲載が終了すると、過去にアクセスできたURLでも以下のようなページに変更される。

別にこんなページをスクレイピングしたいわけではないので、以下の仕様に変更しましょう。

• AsIs: 計算式による処理によって、常に最新断面のデータを取得する仕様
• ToBe: URLを入力した断面のデータを保持し、比較表に表示するデータはスプレッドシートに保持されたデータを参照する仕様

要は「この物件ええやんけ！」と思った時点での掲載情報を蓄積していくイメージ。

設計

(本当ならいくつか実装案を出して、メリデメ比較すべきですが、雑に済ませちゃいたいので1案だけ)

• スプレッドシート内に、mainシートとdataシートを作成する
  • mainシート: 人間がURLを貼ったり、物件情報を比較するシート。mainシート内の物件情報は、dataシートを参照する。
  • dataシート: mainシート内のURL情報をもとに、物件情報を蓄積するシート。
• mainシート内に「今貼ってるSuumo URLをもとに、物件情報収集してね」と処理を走らせるためのトリガーを設置する

フローはこんなかんじ

mainシートでどんな処理を行おうと、トリガーをキックしなければスクレイピング処理は走らないので、「毎回計算が走る」問題は解消されるはずです。

実装

スクリプト

App Scriptは以下となります。コード汚いのはご容赦を...

コード.gs

function getCommuteTime(src, dest) {
 const target_date = '2024/YY/MM HH:mm:ss' // 想定する通勤日時で到着したい時刻を入力

 let datetime = new Date(target_date); 
 let arrival = new Date(datetime.getTime());

 let finder = Maps.newDirectionFinder()
 .setOrigin(src)
 .setDestination(dest)
 .setLanguage("ja")
 .setArrive(arrival)
 .setMode(Maps.DirectionFinder.Mode.TRANSIT); // 公共交通機関

 let route = finder.getDirections().routes[0];
 let value = route.legs[0].duration.value/60; //minutes

 return value;
}

function fetchInfo() {
 const office_a = '東京都xxx区xxxxx';
 const office_b = '東京都yyy区yyyyy';
 const date = new Date();

 const working_sheet=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("main");
 const data_sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
 const last_working_row = working_sheet.getLastRow();
 const last_data_row = data_sheet.getLastRow();

 const urls = working_sheet.getRange(2,1,last_working_row).getValues();
 const stored_urls = data_sheet.getRange(2,2,last_data_row).getValues().flat();

 const count=urls.length;
 let records = [];

 for(let i=0;i<=count-1;i++)
  {
    let url = urls[i][0];
    
    // お気に入り物件URL形式である AND 重複レコードがない場合 -> 処理を実行
    if (url.includes('https://suumo.jp/chintai/bc') && stored_urls.includes(url)==false){
      try{
        let response = UrlFetchApp.fetch(url);
        let content = response.getContentText("utf-8");

        // 物件名
        let name_raw = Parser.data(content).from('<h1 class="section_h1-header-title">').to('</h1>').iterate();
        let name = name_raw.pop().trim();
        name = name.slice(0, name.indexOf("-"));

        // 最寄り駅へのアクセス
        let access_raw = Parser.data(content).from('<div class="property_view_detail-text">').to('</div>').iterate();
        let access = access_raw.shift().trim();

        // 賃料
        /// 家賃 ///
        let rent_raw = Parser.data(content).from('<div class="property_view_main-emphasis">').to('</div>').iterate();
        let rent_str = rent_raw[0].trim().replace('万円', '');
        let rent = Number(rent_str) * 10000;
        /// 管理費 ///
        let management_fee_raw = Parser.data(content).from('<div class="property_data-body">').to('</div>').iterate();
        let management_fee_str = management_fee_raw[0].trim().replace('円', '');
        //// 管理費が0円の場合 ////
        if (management_fee_str=="-"){ 
        management_fee_str="0"
        };
        let management_fee = Number(management_fee_str);

        let total_rent = (rent + management_fee)/10000;

        // 築年数
        let age_raw = Parser.data(content).from('<div class="property_data-body">').to('</div>').iterate();
        let age = age_raw[8].trim();

        // 面積
        let area_raw = Parser.data(content).from('<div class="property_data-body">').to('</div>').iterate();
        let area = area_raw[5].trim();
        area = Number(area.slice(0, area.indexOf("m")));

        // 住所
        let address_raw = Parser.data(content).from('<div class="property_view_detail-text">').to('</div>').iterate();
        let address = address_raw.pop().trim();

        // 通勤時間
        let office_a_commute = getCommuteTime(address, office_a);
        let office_b_commute = getCommuteTime(address, office_b);

        // 配列への書き込み
        let record = [];
        record[0] = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyy-MM-dd HH:mm:ss');
        record[1] = url;
        record[2] = name;
        record[3] = access;
        record[4] = total_rent;
        record[5] = area;
        record[6] = age
        record[7] = address;
        record[8] = office_a_commute;
        record[9]= office_b_commute;
        records.push(record);

      }catch(e){
        console.log('Invalid URL');
        const response1 = Browser.msgBox('Invalid URL: \n' + url);
      }
    }
  }
  // シートへの書き込み
  // レコードが0件の場合は何もしない
  if(records.length==0){
    const response2 = Browser.msgBox('No record was inserted.');
  }else{
    try{
      
  data_sheet.getRange(last_data_row+1,1,records.length,10).setValues(records);
    }catch(e){
      const response3 = Browser.msgBox('Insert Error');
    }
  }
}

コードができたので、動かしてみましょう〜

動作検証

mainとdataシートを作成し、適当に物件URLを貼ります。また、図形にfetchInfo()の関数も紐づけておきましょう。

初期状態

物件として、東京駅徒歩3分の1KのURLを入力しておきます。ここに住む気は絶対起こらないので、あくまで検証用としての利用 (1K 20m^2で10万円超えるって、やばいな)

各シートはこんなかんじ。

mainシート

現在 #N/A になっている理由は、dataシートをVLOOKUPで参照しているものの、データが収集されていないため、エラーになっています。

dataシート

当然、なんのレコードも蓄積・収集されていないですね。

実行

トリガーである 【🔍検索】 ボタンをクリック！

mainシート

正しくデータが反映されました〜

dataシート

収集・蓄積も正しくされているようです！

おわりに

感想

• 「動くものを作って、体験して、良い方向にリファクタして」って動き方はやっぱり楽しいなあ
• Mermaid記法でシーケンス図を取り扱うの、仕事を含め初めてだったのだけど、AsIs/ToBeをクリアに表現して「ほら解決できそうでしょ」を明示的に示すのは難しいな...
• GASコード、きたないので、整理したい...

残課題

• Suumoでお気に入りしていない状態のURLを入力すると、エラーになる (優先度: Medium)

→ 別に優先度は高くないですが、気持ち悪いのでそのうち対応しましょう