【VBA】Google ChromeとSeleniumでスクレイピング【検索順位をEXCELへ】

作品集

カワッターが作った作品集です。 都度追加していく予定です。 事前にプライバシーポリシーの「当ブログの作品について」を確認ください。 また、ミス等コメントありましたらお問い合わせフォームからお願いします。

kawattawatta.com

2023.04.23

会話にビックリマークを多くしがちな熱いあなたのために、VBAプログラムを作りました。

キーワードに対するGoogle検索結果で、1ページ目のトップURL(ドメインまで)をひっぱります。

私事で恐縮ですが、html・CSSを勉強しています。

同時に、これからデータがもっともっと世を闊歩すると考えWebスクレイピングも同時に勉強することにしました。

今回は私にとって、Webスクレイピングが初めての経験であったためまとめました。

Webスクレイピングは接続する全ての相手に迷惑をかけないようにお願いします。

最悪とんでもないことになります。
といっても許されます。
実際に実例あるからです。
岡崎市立中央図書館事件(参照元：Wikipedia様)

紆余曲折ありますが、偽計業務妨害容疑で男性が逮捕されたという事実ができあがってしまっています。

詳細は記事の最後の「スクレイピングの注意事項」を確認してください。
スクレイピングをする方は必ず一度目を通してほしいです。

本当は一番はじめに載せたかったのですが、内容が長くなったため最後にしました。

私を含め明日は我が身かもしれません。
そうならないためにも必ずよんでほしいです。

ではどうぞ！

Googleは検索結果のスクレイピングを認めてないので注意

今回、私はこのような記事を作成しましたがGoogleは検索結果のスクレイピングを認めておりません。

注意してください。

しかしながらサーバーに高負荷がかからなければ黙認されている、という実態があります。

今回の私のプログラムはGoogle検索ボタンを押した処理の後、2秒またせています。

スクレイピングはあくまで自己責任で行ってください。

この記事のメリット(対象者)

動作内容

それではこちらの動画をご覧ください(約1分です)。
①EXCELにある検索ワードでGoogle検索
②Googleの検索結果表示
③1ページ目の順位のトップページURLをひっぱります。
　広告や順位途中のマップは避けます。

私のPCがショボいのか、キャプチャソフトの使い方を分かっていないのか、動画の最後がカクカクになってしまいました。
申し訳ありません。

ざ～～～っくりいうと、スクレイピングとはブラウザで自分が狙った場所のデータを入手することです。

つまり、Webで行う手作業のコピペをすご～く早くしたもの。
そんな解釈です。

Seleniumの使用準備

事前準備としてSeleniumをPCにインストールをしてください。

インストールは以下のHPを参考にさせて頂きました。
VBAからchromeスクレイピング(Qiita：400800mkouyou様)

インストール方法→VBAの設定まで説明頂いています。

準備に必要な手順は大きく2つです。

Selenium Basicは、Seleniumの本体です。
Chrome、EdgeやIEなどを、VBAなど外部プログラムから制御することができます。
”VBAなど”ですので、VBA以外の言語でもできます。

Chromeドライバは、自分のChromeにあったバージョンをインストールしましょう。

Selenium Basicをインストールするとインストールフォルダに既にChromeドライバがあるのですが、自分の使用しているChromeのバージョンに対応していないはずです。

参考に私のChromeのバージョンをのせます。
バージョンの確認方法としてつかってください。

Seleniumは、Google Chromeを操作する専用の道具箱(IT系ではライブラリと言います)です。
ものづくり系では、治具(じぐ)の言葉がシックリくるかと思います。
※道具箱もライブラリ・フレームワークと違いがありますが…話が発散するため割愛します

また、新規PCにSeleniumBasicをインストールする場合は.Net Fremework 3.5もあわせてインストールします。

プログラム

必要な知識

今回のプログラムを読むために最低限理解しないといけない項目があります。

HTML(タグの知識)

世にあるHPはHTMLという言語でできています。
これはHPの骨組みをつくるための言語です。

枠組みを作っていくために、タグと言うもので表現したい内容を挟み込んでいきます。

今回は、自分の欲しいデータがどこあるかを知るために事前にタグをたどっていく必要があります。

たどり方の具体的な手段は次のXPathを参照してください。

XPath(とClass)

今回は、スクレイピングでデータを取得するひとつの手段であるXPathを使用します。

こちらのHP様にXPath(とClass)についてシンプルな図と一緒にたいへん分かりやすくまとめられています。
クローラ作成に必須！XPATHの記法まとめ(Qiita：rllllho様)

私が説明すると逆に長くなる&ややこしくなるため、この記事ではXPathの取得方法だけ説明します。
次の画像の通りです。

今回の場合、調べたところGoogleの検索順位のXPathは次の通りでした(2020/5/23現在)。

//[@id="rso"]/div[1]/div/div[1]/a …1位
　　　　：
//[@id="rso"]/div[n]/div/div[1]/a …n位(MAX10位)

div[n]と記載した個所のみ数字が増えていくため、その部分だけ繰り返し処理すればよさそうだ、という事が分かりました。

また、今回は私の勉強目的に強調スニペットの部分はClassを使ってタグをとっています。

"Copy Selector"でパスをコピーしています。

基本方針はこれで完了です。

ライブラリの使い方

GoogleのHPを開く→検索ボックス入力→ボタンクリックまでは、このHP様を参照しました。
これをもとに今回の検索入力部分を作成しています。
SeleniumとVBAでググってみる(引用元：LogicalCreation様)

その後の処理は、都度こちらのページを参照しました。
同じHP様です。
XPathとClassのパスを使うためです。
Excel-VBAとSeleniumでChrome操作 【コントロールを取得】(引用元：LogicalCreation様)
書かれていない内容で追加したものは、

driver.Start

これぐらいです。

ソースコード【2020/9/9更新-関数化しました】

'◆注意　本プログラムで発生した万一のトラブル、問題や損害が発生しましても、一切の責任を負いません◆
'スクレイピングは良識の範囲で行い、かつ接続先の規約の有無の確認、およびスクレイピングの実施可否を確認してください
'特にdriver.Waitの値は短い時間を入力しないでください
'(DOS攻撃と勘違いされます)
'
'Google検索の全てのレイアウトパターンに対応していません。
'上手くXpathが読み込めないと無限ループから抜け出せずフリーズします。
'そのためXPathは必要に応じて変更してください。
'また、1ページに10位までが表示されないと、こちらも無限ループから抜け出せません。
'まだまだ改良要ですが、お好きにご使用ください。
'
'キーワードの入力セルはCells(3, 2)としていますが、自由に変更してください。
'「集計結果」「Google」シートがありますが、シート名は何でもOKです。
Sub 検索結果順位タイトル取得()
  '変数定義
  Dim driver As New Selenium.ChromeDriver
  Dim element_SupeUnit As Selenium.WebElements
  
  Dim wsResult As Worksheet
  Set wsResult = Worksheets("集計結果")
  Dim wsSearch As Worksheet
  Set wsSearch = Worksheets("Google")
  
  Dim elements As Selenium.WebElements
  
  Dim strURL As String
  Dim ranking As Long
  
  
  If wsSearch.Cells(3, 2).Value = "" Then
    MsgBox ("黄色の塗りつぶし部分に" & vbCrLf & "検索ワードを入れてください")
  Else
    'Googleアクセス
    driver.Start
    driver.Get "https://www.google.com"
    driver.Wait (5000) '待機(2秒)
    'キーワード入力
    wsSearch.Activate 'EXCELにGoogleシートを認識させる(1004エラー対策)
    
    driver.FindElementByName("q").SendKeys(wsSearch.Cells(3, 2)).Value  '検索ワードはセルから読み取り
    driver.Wait (1500) '待機(1.5秒)キーボード入力完了待ち
    
    '検索ボタンエンター(検索ボタンのXPathクリックでは文字長さの状態によってクリックできない場合があった)
    SendKeys "{ENTER}"
    driver.Wait (2000) 'サジェスト表示分を加味して待機(2秒)
    
    '処理開始
    flagSnippet = False '強調スぺニットをカウントしたフラグ
    ranking = 1 '順位
    
    'ドメインまでリンクを取得(トップページ)
    '10位を取得するまで続ける
    Do Until ranking > 10
      tmpStrNum = Replace(Str(ranking), " ", "") '数字をStr関数で文字にするとスペースができるため、スペースを削除
      
      If flagSnippet = False Then
        '一番目に強調スぺニットがあるか確認
        
        tmpStrXPath = "//*[@id=""rso""]/div[" & tmpStrNum & "]/div[1]/div/div[1]/div/div[2]/div/div[1]/a"
        Call GetURLWithXPath(tmpStrXPath, driver, elements, strURL)
        If elements.Count = 1 Then
          'トップページのURLを入れる
          Call InsertTopPageURL(strURL, wsResult, ranking)
        End If
        '通常の1位を取得
        tmpStrXPath = "//*[@id=""rso""]/div[" & tmpStrNum & "]/div/div[1]/a"
        Call GetURLWithXPath(tmpStrXPath, driver, elements, strURL)
        If elements.Count = 1 Then
          'トップページのURLを入れる
          Call InsertTopPageURL(strURL, wsResult, ranking)
        End If
        
        flagSnippet = True '強調スぺニットフラグをON
      Else '強調スぺニットではないとき
        tmpStrXPath = "//*[@id=""rso""]/div[" & tmpStrNum & "]/div/div[1]/a"
        Call GetURLWithXPath(tmpStrXPath, driver, elements, strURL)
        If elements.Count = 1 Then
          '通常の順位を取得
          Call InsertTopPageURL(strURL, wsResult, ranking)
        End If
      End If
    Loop
    
    MsgBox "順位収集完了しました"
  
    '終了処理
    driver.Close
    driver.Quit
    Set driver = Nothing
  End If
End Sub


'''''''''''''''''''''''
'機能：Xpathの場所のURLを取得する。
'      事前にXpathのチェックを行った後、URLを取得する。
'引数：tmpStrXPath...探したいXpath(値渡し)
'     driver...ChromeDriver変数(値渡し)
'     elements...WebElements変数(参照渡し)
'     strURL...Xpathの示すURL(参照渡し)
'返り値：strURL...Xpathの示すURL
'
Function GetURLWithXPath(ByVal tmpStrXPath As String, ByVal driver As Selenium.ChromeDriver, _
                            ByRef elements As Selenium.WebElements, ByRef strURL As String)
  
  Set elements = driver.FindElementsByXPath(tmpStrXPath)
  If elements.Count = 1 Then
    strURL = driver.FindElementByXPath(tmpStrXPath).Attribute("href")
  End If

End Function


'''''''''''''''''''''''
'機能：XpathのトップURLを取得する
'引数：tmpStr...ページURL(値渡し)
'     wsResult...結果シート(値渡し)
'     ranking...検索順位(参照渡し)
'返り値：ranking...検索順位(参照渡し)
'
'
Function InsertTopPageURL(ByVal tmpStr As String, ByVal wsResult As Worksheet, ByRef ranking As Long)
    'トップページのURLを入れる
    strAddress = Split(tmpStr, "/") '/で文字を分解
    strAddress = strAddress(0) & "//" & strAddress(2) 'http://～～～.comを作成
    'EXCELに値を入れる
    wsResult.Activate
    wsResult.Range(Cells(ranking + 2, 2), Cells(ranking + 2, 2)).Value = strAddress 'セルにURLを入力
    ranking = ranking + 1
End Function

'''''''''''''''''''''''
'★本コードでは使っていません★
'機能：Xpathの場所の有無を確認する
'引数：tmpStrXPath...探したいXpath(値渡し)
'     driver...ChromeDriver変数(値渡し)
'     elements...WebElements情報(参照渡し)
'返り値：JudgeSuggestWithXPath...bool値
'       True：Xpathの要素あり False：Xpathの要素なし
'
Function JudgeSuggestWithXPath(ByVal tmpStrXPath As String, ByVal driver As Selenium.ChromeDriver, _
                            ByVal elements As Selenium.WebElements) As Boolean
  
  Set elements = driver.FindElementsByXPath(tmpStrXPath)
  If elements.Count = 1 Then
     JudgeSuggestWithXPath = True
  End If

End Function

プログラムについて

強調スニペットの対処

1つ目は、強調スニペットがあればそれを1位にしました。

今回の手段として、”Copy Selector"で強調スニペットが出てきた時に”Copy Selector”でパスを取得しています。
(もちろんXPathでもOK)

プログラムでは、「FindElementsByCss」を使って実際に強調スニペットがあるかを確認しています。

ちなみに、強調スニペットは次の画像の赤枠部分のことです。

強調スニペットの正確な説明はGoogle本家の説明を参照ください。

一部の検索では、関連するウェブサイトのコンテンツ スニペットを使用して、簡単な回答やサマリーが提供されます。このような強調スニペットが表示される傾向が最も高いのは、検索が質問の形式で入力された場合です。

引用元：Google の強調スニペットの仕組み Google

順位の間にでてくる関連ページ

検索結果によっては、ときどき上の画像のような順位と順位の間に関連ページ的な意味合いで、マップやら動画やらが挟まります。

そうなると、順位結果のXPathである、

//[@id="rso"]/div[n]/div/div[1]/a

これがループを10回まわしただけでは、途中の順位までしか表示されません。

対策は関連ページを飛ばし、10位までカウントできるようにしています。

具体的には、「For i = 1 To 20」でループ回数を20回まわすことで実装しています。
ループを20回せば十分に10位までたどり着くだろう、
と言う考えです。

これもかなり適当です。

その他

ソースのコメントを参照ください。
ブログ内で説明を割愛するためにいろいろ記述しました。

ただ、split()でつまずいたためここで補足します。

今回、検索結果のアドレスを加工してドメインまでを入手するようにしています。

手段としてsplit()で"/"ごとに文字を配列という要素にわけています。
今回つまずいたのは

https://～～～.comの、"//"です。

言葉だと分かりずらいため、結果をご覧ください。

https://qiita.com/○○/□□/～～の例の結果です。
"/"より前の文字をSplit()で取得します。
"/"自体は取得しません。

• 

• 

• 

この結果から言えることは、split()実行後取り出さないといけない配列は0番目と2番目であるということ。

その後で"//"でつなげてあげる。

ということでした。
(VBAは順番が0からスタートする考え方です)

私は0番目と1番目を取り出そうとして”あれ？？”となっていました。

色々と改善できます【2020/9/9更新】

今回、★マークの部分またはまとまりで改善の余地がありそうな箇所を示しました。

カワッターいったいお前は何をいっているんだ、といいますと、今回作ったプログラムは非常～に汚いです。

カワッターお前…
本当にC言語3年、VBA5年の経験者なのか。
と、疑われそうです。

考えられる内容としては次のものがあげられます。

今回で、基本的な流れが大体わかってきたため機会があれば改善しようと思っています。

テストPCのスペック

エラー集

今回考えられるエラーを起こしてみました。
まだまだ沢山あると思いますが、やりがちであろうものを並べました。

【必読】スクレイピングの注意事項

本来なら冒頭にもってこなければいけないのですが、いかんせん長くなってしまいましたので記事の終わりにうつしました。

サーバー側のルールを守る

スクレイピングのルールってなによ？
と考えている方、残念ながらスクレイピングする前の段階です。
相手のサーバーに迷惑をかける可能性があります。

・一度アクセスしたデータはマシン上に保管し、何度もアクセスしないようにする
・沢山のページを読み込む場合は1秒に1度程度の緩やかな頻度でアクセスすること
・サイトの利用規約をよく読む、せめてあるかないかだけでも探すこと

スクレイピングのやり方をQ&Aサイトで質問するな(Qiita：@miyabisun様)

スクレイピングで通信リクエストを並列で飛ばしてはいけません。
人の手作業と同じ手順で行ってください。

具体的には、あくまでも作業はひとつずつ、です。
どんなに作業が早いからと言って、一つずつの作業でアクセスの間隔を短くする、
複数キーワードを同時にアクセス(検索)、といったことはしてはいけません。

また、スクレイピングの逮捕事例として冒頭で話した岡崎市立中央図書館事件(参照元：Wikipedia様)があります。
アクセス頻度は緩やかといわれる、1秒に1度程度でも逮捕という事態に繋がりました。
当時の技術的な状況があったにせよ、です。

「Librahack ： 容疑者から見た岡崎図書館事件」のHPに、岡崎図書館事件の内容もまとめられています。

リンクを貼り付けなかったのは、HP様がhttpsに対応していなかったからです。

検索エンジンでも検索すればトップにでてくるため、確認可能です。
逮捕という事実に至るまでの経緯がわかります。

IT屋として、私も色々と考えさせられています。
正直、なぜ？？という気持ちです。

明日は我が身…。
そうならないためにも、アクセス頻度はこれでもかと言うぐらいに間隔をあけましょう。

私を含め、気を付けなければいけません。

スクレイピングは保守メンテが発生する

シンプルな話になります。

サイトの構成が変わってしまうと、HTMLタグの順番が変わってしまいます。
データのパスを再取得しないといけなくなります。

最悪、今回のプログラムはまったく使えなくなります。

収集目的を明確にする

スクレイピングしたデータの使用目的が、情報解析であることが必須になります。

今回、冒頭で話していた熱血営業エンジニアさんの目的は、情報解析なので「情報監視サービス・機能をつくる」に該当します。

スクレイピングについての考え方は、こちらのHP様が分かりやすくまとめられています。
スクレイピングは違法？これだけ読めばわかる、違法と例外を解説！

こちらもどうぞ
【スクレイピング】違法にならないサービスパターン5選

今回のパターンです。

情報監視サービス・機能をつくる
競合他社の情報を随時チェックする、株価の監視、など
（著作権法第47条の7)

【スクレイピング】違法にならないサービスパターン5選(引用元：株式会社SMSデータテック様)

まとめ

VBAおすすめ本(新入社員の教育用、中級者用)

このページに訪問頂いた方はVBA中級者の方を想定しています。

通常の業務に加えて後輩の教育、プログラムの作成ルールの制定や改善を任される方が多く、どういった手順で教育をしようか、悩まれていると思います。

そこでVBAのおすすめ本を3冊選びました。
実際に私は私の選んだ本の内容でプログラミングと縁のない、ものづくりの現場の方(設計職)に対してVBAを教え、理解頂くことができました。

また、プログラムの作成ルールの制定や改善については中級者向けの方を想定した本で確認頂けます。

ぜひ一度目を通してみてください。

【現場でVBA】ものづくり＆ITエンジニアが選ぶ参考書【この3冊でOK】

あなたにおすすめするVBAのおすすめの参考書は？ITだけではなく、ものづくりエンジニアとしての観点をふまえて選んでいます。

kawattawatta.com

2022.05.14

紹介している本はこちらです。

3000円～4000円(脱初心者～中級者)

長い内容になりました。
ご覧いただきありがとうございました。