電子回路シミュレータLTspiceのモデルの販売を開始しました(現在ソーラーパネルのみ) クリックするとgumroadに移動します

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

タイトル画像(スクレイピング) IT
この記事は約23分で読めます。
【2020/9/9追記 プログラムを関数化&改善しました】
同じ処理を繰り返しているので、関数化しました。
また、問題だらけの部分をちょっとだけ良くしました。

ある程度使えるレベルまでいったのでGithubで公開しました。
この記事にはないスクレイピングのひな形もあります。
使ってみてください。

kawattawatta/VbaScrapingWithGoogleChromeAndSelenium
Contribute to kawattawatta/VbaScrapingWithGoogleChromeAndSelenium development by creating an account on GitHub.
【2020/7/16追記 作品集にこの記事のマクロを公開しました】
zipファイルにしています。
ダウンロードして使ってみてください。
問題だらけですので、改変は自由です。
この記事の「色々と改善できます」の節に改良点をのせていますので参考にしてください。

問題は少し改善しました。【2020/9/9】
熱血営業<br>エンジニア
熱血営業
エンジニア

Googleの検索順位ごとにサイトのトップページのURLを
EXCELに出せないのか!?
マーケティングの分析で使いたい!
競合の順位を知りたい!

今はサイトにアクセス→コピペだが…
キーワードごとだと全部コピペは辛すぎる!!

範囲は最初の1ページ、ブラウザはGoogle Chrome!

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

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

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

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

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

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

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

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

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

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

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

ではどうぞ!

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

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

注意してください。

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

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

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

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

  • VBAでGoogle Chromeのスクレイピングができます
  • スクレイピングを行うための最低限の技術項目を知ることができます
  • スクレイピングのルールが何かを知ることができます(技術云々の前の話)
  • このプログラムは汚いプログラムであることが分かります

動作内容

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

【説明用】VBA Google検索順位1ページ目スクレイピング
Google検索結果1ページ目 スクレイピング

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

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

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

Seleniumの使用準備

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

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

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

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

  • Selenium Basicのインストール
  • Selenium Basicにある
    Chromeドライバの最新版の入手

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

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

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

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

Chromeバージョン確認
Chromeバージョン確認方法

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

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

プログラム

必要な知識

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

HTML(タグの知識)

html説明
htmlのざっくり説明(タグで挟む) 引用元:Google

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

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

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

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

XPath(とClass)

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

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

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

XPath取得手順
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

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

順位の間にマップ等
順位の間にマップ等
(出力結果は地図やYoutubeコンテンツが出てきたので、
念のため隠しました)

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

そうなると、順位結果の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年の経験者なのか。
と、疑われそうです。

2020/5/26 いいわけです
プログラムは
いきなり100点満点を つくることをせず、 40点でよいので 全体を完成させることを,
優先してほしいです。
経験則になって恐縮ですが、 プログラミングは生産性が命と考えます。
そのためまずは、プログラムを早くつくる=とりあえず作っちゃう→あとで少しずつちゃんとした形にする。
以上のように進めて頂ければと思います。

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

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

  • 同じ処理をまとめていない
    関数化できていない
    →関数化完了
  • データのたどり方が統一されていない
    XPathとClassに分ける必要がないのでは
    →XPathに統一
  • ループの考え方が適当すぎる
    20回の根拠が弱い
    →10位になったらループを脱出するようにした
  • そもそもこのプログラムは
    手順的に最も少ない手順で動いているのか
    根本的な問題!
    →ご意見を求めます!!
  • 検索ワードがないとエラーで止まる(凡ミス!)
    →検索ワード入力セルに何もないときはメッセージボックス表示、プログラム終了
  • Googleでサジェストが表示されないと検索できない
    →Clickではなく、Enterキー入力にした

テストPCのスペック

  • Office:EXCEL2007
  • OS:windows8.1 64bit x64ベースプロセッサ
  • CPU:Intel Celeron CPU1000M 1.80GHz,1.80GHz
  • メモリ:4GB

エラー集

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

id_fake-boxはスクレイピング用のIdじゃない
Selenium:NoSuchElementError
fake-boxはスクレイピング用のIDじゃなかった

そんなIdはないです、というエラーです。
手作業でGoogle Chromeを起動した時のものを使用すると左図のようになります。
対策はVBAプログラム経由で開いたChromeからid(他の要素も)を取得します。
全くIdが違います。
私はGoogleの検索BOXのキーボード入力の処理でハマりました。
取得方法はXPath(とClass)を確認してください。

VBA1004エラー(処理したいシートアクティブじゃない)
VBA1004エラー Rangeメソッドは失敗しました

処理したいシートがアクティブになっていないため起こります。
○○.Activateで、ねらったシートをアクティブにしてください。

XPath処理失敗
FindElementByXPath()でXPathの場所がないときに表示

こちらはXPathがない、というエラーです。

FindElementsByXPath()で事前にデータの有無を確認せずに、FindElementByXPath()で直接XPathを指定すると表示されます。

事前にFindElementsByXPath()でデータの有無を確認しましょう。

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

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

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

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

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

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

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

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

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

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

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

【2020/7/11追記 リンク追加しました】
一般的なブラウザはhttp接続で警告表示で閲覧するか選択できるようになってきているため、リンクを追加することにしました。

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

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

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

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

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

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

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

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

収集目的を明確にする

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

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

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

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

今回のパターンです。

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

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

まとめ

  • VBAでGoogle Chromeを行うには
    Seleniumというライブラリを使用します
  • スクレイピングは
    HTMLの知識とデータの場所をたどるための知識が必要
  • Googleは検索結果のスクレイピングを
    認めてないので注意、実施は自己責任で
  • サーバー側のルールを守る(利用規約)
  • プログラムはサイトの内容変更で保守メンテが発生する
  • 収集目的を明確にする
  • 今回のプログラムは改善する箇所がたくさんある

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

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

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

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

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

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

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

コメント

タイトルとURLをコピーしました