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

# はじめに

## 背景

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

https://qiita.com/toro-osushi/items/490d0c12042195e2dd03

## この記事の目的

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


# 現状の整理

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

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

```mermaid
sequenceDiagram
   autonumber
   actor ぼく
   participant スプレッドシート
   participant Suumo
   participant Google Maps API

   ぼく->>スプレッドシート: 値の挿入・セルの操作
   スプレッドシート->>+Suumo: スクレイピングによる物件情報リクエスト
   Suumo-->>-スプレッドシート: 物件情報

   スプレッドシート->>+Google Maps API: 通勤時間のリクエスト
   Google Maps API-->>-スプレッドシート: 通勤時間
```

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

https://qiita.com/saten/items/4efc33d9f7a6f1b22cfa

## 掲載終了物件への対応

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

![スクリーンショット 2024-07-10 10.47.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/656439/a3fe4e02-61c5-9378-9138-5daff60be397.png)

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

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

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


# 設計

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

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

フローはこんなかんじ

```mermaid
sequenceDiagram
   autonumber
   actor ぼく
   participant m as mainシート
   participant d as dataシート
   participant S as Suumo
   participant G as Google Maps API

   ぼく->>m: トリガーの実行
   note over m,G: トリガーによる処理開始
   d->>m: Suumo URLの取得
   activate d
   d->>d: 重複したURLがないかバリデーション
   d->>+S: スクレイピングによる物件情報リクエスト
   S-->>-d: 物件情報
   d->>+G: 通勤時間のリクエスト
   G-->>-d: 通勤時間
   d->>d: 物件情報・通勤時間の書き込み
   deactivate d
   note over m,G: トリガーによる処理終了
   m->>+d: 物件情報・通勤時間の参照 (VLOOKUP)
   d-->>-m: 物件情報・通勤時間

```

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

# 実装

## スクリプト

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

```javascript:コード.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万円超えるって、やばいな)

![スクリーンショット 2024-07-10 11.45.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/656439/eaea8eaf-b917-b411-1197-124f6a92cc60.png)

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

**mainシート**

![スクリーンショット 2024-07-10 11.43.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/656439/cb0ccf7f-a4f3-61b5-37e1-db91b08118bd.png)

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

**dataシート**

![スクリーンショット 2024-07-10 11.43.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/656439/5a8dad02-29d0-edfc-5476-871f30adac04.png)

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

### 実行

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

**mainシート**

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

![スクリーンショット 2024-07-10 11.50.03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/656439/4a4dd36c-eae0-8e51-e8d4-a67a47e6449a.png)


**dataシート**

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

![スクリーンショット 2024-07-10 11.50.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/656439/03afe7c6-94de-15a2-ec09-0356d9a2ce6b.png)

# おわりに

## 感想

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

## 残課題

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

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