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

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

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

code-life.hatenablog.com

前回までのおさらい
キーワードを検索する
- プログラムにキーワード検索する部分を追加する
検索結果から情報を取得する
- Googleの検索結果から情報を取得するには
取得した情報をMsgBoxで表示するよう書き加える
要素を探す前にページの読み込みを待たないとエラーになる　
Excelのシートに連続で入力していく
まとめ

前回までのおさらい

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

IEを準備する
IEを表示する
Googleを開く
3秒まつ
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セレクタで要素を指定して複数要素を取得

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

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列：タイトル

処理の流れを確認

2行目から開始
A列に変数keywordの値を入力
B列に検索順位（1から1行ごとにカウントアップ）を入力
C列に検索結果（1つ目から1行ごとに次のリンクへ移動）のタイトル文字列を入力
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 'タイトル