自動化厨のプログラミングメモブログ│CODE-LIFE

Python/ExcelVBA/JavaScript/Raspberry Piなどで色んなことを自動化

【ExcelVBAでスクレイピング入門】Googleで検索順位を自動取得してみる② ~キーワードを検索して結果をシートに書き込む~

第一回ではGoogleを表示するところまででしたが、今回はスクレイピングの肝となるデータを抜き出す部分です。

もうちょっと間空けて投稿しようかと思ったけど勢いで公開。ここらへんまでやらないと面白くないもんね。

code-life.hatenablog.com

前回までのおさらい

前回のプログラムの処理フローを再確認します。

  1. IEを準備する
  2. IEを表示する
  3. Googleを開く
  4. 3秒まつ
  5. IEを閉じる

これらの動きをVBAで書いたものが以下のプログラムです。

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()

    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    oIE.Navigate2 "https://www.google.co.jp/" 'URLにアクセス
    
    slepp 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

これに「Googleでキーワードを検索する」「検索結果が表示されるのを待つ」「検索結果から情報を取得」「取得した情報をシートに書き込む」という処理を加えていきます。

 

キーワードを検索する

これ以外と簡単です。

実はGoogleやYahooなどの検索エンジンはURLでキーワードを受け取ってその結果を出力しています。

試しに「VBA」と検索したときにページのURLがどうなっているか見てみます。

f:id:maru0014:20190518112450p:plain
キーワード「VBA」で検索してみる

こんなURLになりました。

https://www.google.com/search?source=hp&ei=l2zfXK_4LoaKr7wPhMWhwAU&q=VBA&oq=VBA&gs_l=psy-ab.12..0l2j0i131j0j0i131j0l3.1600.1913..93813...0.0..0.66.194.3......0....1..gws-wiz.....0..0i4j0i131i4.SZrjO8fnyNQ

この中にキーワードが含まれているのですが必要な部分はsearch?q=VBAです。searchというページに対してクエリ=「VBA」というパラメータ情報を渡しています。

なので単純にキーワードを検索するという動作だけ実現するには以下のようなURLを作ってアクセスすればOK。

https://www.google.com/search?q=VBA

その他のパラメータが気になる方は以下が参考になりそうです。Google以外の検索エンジンのキーワードパラメータもまとまっているので助かります。またこれはこれで記事にしようかな。

www13.plala.or.jp

 

プログラムにキーワード検索する部分を追加する

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()
    
    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    oIE.Navigate2 "https://www.google.com/search?q=" & "VBA" 'URLにアクセス
    
    Sleep 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

oIE.Navigate2 "https://www.google.com/search?q=VBA" のようにくっつけて書いても良かったのですが、複数のキーワードを入れ替えながら処理したい。つまり変数にしたいので分けてから&で文字列を結合する形にします。

てか、じゃあ変数にすればいいね。

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()
    
    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    
    Dim keyword As String
        keyword = "VBA"
        
    oIE.Navigate2 "https://www.google.com/search?q=" & keyword 'URLにアクセス
    
    Sleep 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

keywordという変数に「VBA」という文字列を入れて、検索用URLの後ろにくっつけてからアクセスする。

というプログラムになりました。一旦ここまでで実行してみましょう。

検索結果が表示されていれば成功です。

f:id:maru0014:20190518114421g:plain
Googleで「VBA」を検索
   

検索結果から情報を取得する

いよいよ表示されているページからデータの抽出。

ページのデータ(HTML)から特定の要素を抽出する方法はいくつかあります。

  • getElementByID("id")・・・要素のIDを指定して単一の要素を取得
  • getElementByName("name")・・・要素のNameを指定して単一の要素を取得
  • getElementsByTagName("タグ名")・・・要素のタグ名を指定して複数要素を取得
  • getElementsByTagName("タグ名")(n)・・・要素のタグ名を指定して複数要素の中からn番目の要素を取得
  • getElementsByClassName("クラス名")・・・要素のクラス名を指定して複数要素を取得
  • getElementsByClassName("クラス名")(n)・・・要素のクラス名を指定して複数要素の中からn番目の要素を取得
  • querySelector("CSSセレクタ")・・・CSSセレクタで要素を指定して複数要素の中から1番目の要素を取得
  • querySelectorAll("CSSセレクタ")・・・CSSセレクタで要素を指定して複数要素を取得

これらの中から適切なものを使って抽出しますが、ぶっちゃけquerySelectorquerySelectorAllが万能だと思います。(多少実行速度は遅いみたいな情報をどこかで見かけたが気にするほどではない)

 

Googleの検索結果から情報を取得するには

Googleの検索結果の各リンク先情報はrというクラス名のdiv要素で囲まれています。この中に含まれる文字を取得してみましょう。

f:id:maru0014:20190518123412p:plain
Chromeの要素の検証(F12)を使うと簡単に調べられる

これをどのように指定して使うかというとoIE.Document.querySelector(".r").innerTextみたいな書き方。もう少し細かく説明すると。

以下のように少しずつ階層を辿っていっているイメージ。

f:id:maru0014:20190518122317p:plain
IE→ページHTML→要素指定→要素の中のテキスト抽出

 

取得した情報をMsgBoxで表示するよう書き加える

きちんと取得できているか確認するためメッセージボックスとして文字列を表示させます。

以下のプログラムをコピペして実行してみましょう。

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()
    
    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    
    Dim keyword As String
        keyword = "VBA"
        
    oIE.Navigate2 "https://www.google.com/search?q=" & keyword 'URLにアクセス
    
    MsgBox oIE.Document.querySelector(".r").innerText
    
    Sleep 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

 

要素を探す前にページの読み込みを待たないとエラーになる 

おそらくこんなエラーがでたのではないでしょうか。

f:id:maru0014:20190518123804p:plain
オートメーション エラーです。エラーを特定できません。

これは「oIE.Documentから要素取ってこい言われたけどなんかIE忙しいらしくて断られたよ」みたいなエラー。または「ページがちゃんと表示される前に取ろうとしてもそんな要素無いやで」みたいなエラーもありえます。

ようはちゃんとページ読み込まれて落ち着くまで待ってあげればOK。

ページを移動するたびに待ちが発生するので、使い回しやすくするため別のプロシージャとしてbrowser_waitを作りました。

Navigate2で検索結果ページにアクセスした直後にCall browser_wait(oIE)で待機させます。

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()
    
    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    
    Dim keyword As String
        keyword = "VBA"
        
    oIE.Navigate2 "https://www.google.com/search?q=" & keyword 'URLにアクセス
    Call browser_wait(oIE)
    
    MsgBox oIE.Document.querySelector(".r").innerText
    
    Sleep 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

Sub browser_wait(oIE)
    
    '読み込みが完了するまで待つ
    While oIE.ReadyState <> READYSTATE_COMPLETE Or oIE.Busy = True
    
        DoEvents
        Sleep 100
        
    Wend
    
    '読み込み完了後の安定化待ち
    Sleep 200

End Sub

これで改めて実行してみましょう。

f:id:maru0014:20190518125121g:plain
IE開く→VBAを検索→1個目の検索結果文字列をMsgBoxで表示→3秒待って閉じる

無事にメッセージに1つ目の検索結果情報が表示されていれば成功です。

 

Excelのシートに連続で入力していく

ここらへんからようやくスクレイピングツールらしくなってきます。

入力用のシートを用意。取得したデータを入力している「データ」シートと検索対象を入れておく「キーワード」シートを作りました。※キーワードシートは後ほど使います。

f:id:maru0014:20190518134454p:plain
A列:キーワード、B列:検索順位、C列:タイトル

 

処理の流れを確認

  1. 2行目から開始
  2. A列に変数keywordの値を入力
  3. B列に検索順位(1から1行ごとにカウントアップ)を入力
  4. C列に検索結果(1つ目から1行ごとに次のリンクへ移動)のタイトル文字列を入力
  5. 2~4を検索結果の数だけ繰り返し処理

なんか微妙に違う気もするけどまぁこんなイメージです。

これをVBAで書きます。何はともあれコピペで実行してみましょう。

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub web_scraping()
    
    Dim oIE As InternetExplorer 'oIEという変数はInternet Explorerが入ると宣言
    Set oIE = New InternetExplorer 'oIEに新しいIEを起動して定義
    
    oIE.Visible = True 'IEを表示させる
    
    Dim keyword As String
        keyword = "VBA"
        
    oIE.Navigate2 "https://www.google.com/search?q=" & keyword 'URLにアクセス
    Call browser_wait(oIE)
    
    'search_result に検索結果部分をページごとにリストとして格納
    Dim search_result_list
    Set search_result_list = oIE.Document.querySelectorAll(".srg .rc")
    
    'データシートの入力開始行番号を取得
    Dim writing_start_row As Long
        writing_start_row = Sheets("データ").UsedRange.Rows.Count + 1
    
    'search_result の個数分繰り返し処理
    Dim i As Integer
    For i = 0 To search_result_list.Length - 1
    
        '検索結果一つひとつを順番に参照してデータシートに格納
        With Sheets("データ")
            
            .Range("A" & i + writing_start_row) = keyword 'キーワード
            .Range("B" & i + writing_start_row) = i + 1 '検索順位
            .Range("C" & i + writing_start_row) = search_result_list.Item(i).querySelector("h3").innerText 'タイトル
            
        End With
    
    Next
    
    Sleep 3000 '3秒待機
    
    oIE.Quit 'IEを閉じる
    
End Sub

Sub browser_wait(oIE)
    
    '読み込みが完了するまで待つ
    While oIE.ReadyState <> READYSTATE_COMPLETE Or oIE.Busy = True
    
        DoEvents
        Sleep 100
        
    Wend
    
    '読み込み完了後の安定化待ち
    Sleep 200

End Sub

件数が少ないので一瞬でした。

f:id:maru0014:20190518145511g:plain
VBA の検索結果をデータシートに転記

ひとつずつ解説していきます。

 

16-17行目 querySelectorAll

    Dim search_result_list
    Set search_result_list = oIE.Document.querySelectorAll(".srg .rc")

querySelectorAllはCSSセレクタにマッチする要素をリストとして取得します。

しかし今回の検索結果では一つ邪魔な要素が・・・

f:id:maru0014:20190518135815p:plain
CSSセレクタを工夫することで純粋な検索結果だけを抽出可能

スニペットは「これが欲していた答えでしょ!」という情報を抜粋して表示してくれる便利機能ではあるのですが、その下の検索結果と被っていますので、検索順位を正確に取得するためには邪魔になります。

そのためスニペットではない純粋な検索結果のみを取得するためにセレクタをより細かく指定します。

oIE.Document.querySelectorAll(".srg .rc") これはsrgクラス配下のrcクラス要素をリストで取得という意味。スニペットはsrgクラス配下ではないのでこれで上手いこと取得できそう。

f:id:maru0014:20190518141842p:plain
確認したいセレクタでbackground;red;などを試すと視覚的でわかりやすくチェック可能

 

23-25行目 For~Next文

動作仕様としては表示された検索結果の数だけ繰り返し処理 でしたね。

つまりquerySelectorAllで取得した検索結果リストsearch_result_listの数をカウントしてあげれば良いわけです。

    'search_result の個数分繰り返し処理
    Dim i As Integer
    For i = 0 To search_result_list.Length - 1

search_result_list.Length - 1のようにマイナス1しているのは個数カウント基点の違いへの対策です。

後ほどでてくる.ItemメソッドはリストのIndex番号を指定して個別の要素を取得するものですが、こちらは0からカウントされるのに対して、lengthメソッドは1からカウントして個数を返します。

このズレを修正するためにマイナス1しているわけです。

 

19-21行目 シートの記入開始行番号

データシートにはすでに1行目に項目名が入力されています。

じゃあ2行目からスタートすればいいだけでしょ。まぁ最初はそれでもいいですが、次のキーワードで検索し直したときに再び2行目からシートに書き込んでしまうと上書きされてしまいます。

そのため最終行の次の行から開始 させたいのです。

f:id:maru0014:20190518143259p:plain
2行から ではなく 最終行の次の行から と指定したい

そこで以下のように.UsedRange.Rows.Count + 1とすることで「シートですでにデータが入っている範囲 の最終行 の 次の行番号」という数値を得ることができます。

    'データシートの入力開始行番号を取得
    Dim writing_start_row As Long
        writing_start_row = Sheets("データ").UsedRange.Rows.Count + 1

 

27-34行目 シートへの記入

        '検索結果一つひとつを順番に参照してデータシートに格納
        With Sheets("データ")
            
            .Range("A" & i + writing_start_row) = keyword 'キーワード
            .Range("B" & i + writing_start_row) = i + 1 '検索順位
            .Range("C" & i + writing_start_row) = search_result_list.Item(i).querySelector("h3").innerText 'タイトル
            
        End With

 

.Range("A" & i + writing_start_row) = keyword 'キーワード

データシートの各列にデータを入力していきますが、1回目の処理で変数 i に格納されている数字は0。

しかし、シートにはA0なんてセル番地は存在しません。そこで i + writing_start_row とすることで前述した「シートの最終行の次の行番号」を足してやることで1回目の処理時にはRange("A2")と解釈され、2回目の処理時には 変数 i が1加算されているためRange("A3")と解釈されることで順番に各行へのデータ入力が実現します。

  .Range("B" & i + writing_start_row) = i + 1 '検索順位

検索順位の算出についても同じような考え方で1回目の処理時(つまり検索結果の1番目)のときに 変数 iは0。検索順位0位 なんてことはないので +1 することで 検索順位1位とし、以降加算されながら 2位、3位・・・と続きます。

  .Range("C" & i + writing_start_row) = search_result_list.Item(i).querySelector("h3").innerText 'タイトル

f:id:maru0014:20190518144724p:plain
search_result_listに対してItemメソッドで順番に結果を抽出している

このように階層を辿りながら要素を分解していくことで 検索順位n位のh3要素の中に含まれるテキスト つまりページタイトルを取得しています。

Withは同じオブジェクトを何度も参照する処理を簡略化して書いたものです。

参考:Office TANAKA - 今さら聞けないVBA[Withって何ですか?]

 

まとめ

  • Googleの検索はURLにキーワードを含めれば簡単にできる
  • ページを開く・移動するときは読み込み待ちしないとエラーになる
  • querySelectorAll超便利
  • iterator/index番号とlengthは数カウントの基点が異なる

ここまでできた人はこの先のイメージもなんとなく分かってきたのではないでしょうか。

第三回では、URLとディスクリプションの取得、複数キーワードの検索→取得、2ページ目3ページ目に移動しながらの取得までを出来たらいいなと思います。