今まで書いたブログ記事のタイトルを一括取得!エクセルとMacを使った方法

SEO / アドセンス / ブログ運営

書いたブログ記事のタイトルの一覧を一括で取得したい

ブログの記事が増えるほどタイトルがワンパターンになりがち。
タイトルを考えるときに昔の記事と同じようなタイトルになっている語彙力のなさ。

「どんなタイトルで書いてたっけ?」

自分のブログを検索すればいいんだけど毎回だとちょっと面倒。

そこで、ブログ記事のタイトル一覧を取得できる方法があれば……。あった!
忘れないようにブログにメモしとく。

動作環境は、iMac と エクセル2019 for Mac。

手順1 サイトマップを作成

タイトルを取得するブログ記事のURL一覧が必要。
ブログ記事のURL一覧は、サイトマップを使うのがちょうどいい。すでにサイト上にあればそれのサイトマップを使ってもいい。

ないければサイトマップ作成-自動生成ツール「sitemap.xml Editor」で生成できる。
URLを入力するとサイトマップ(sitemap.xml)を自動生成してくれるありがたいサイト。

生成されたサイトマップ(sitemap.xml)をみると
↓こんな感じでブログのURLがズラズラ書き込まれている。

<!--  created with free sitemap generation system www.sitemapxml.jp  --> 
<url>
  <loc>https://wporep.com/</loc>
  <priority>1.0</priority>
</url>
<url>
  <loc>https://wporep.com/category/aviation/</loc>
  <priority>0.8</priority>
</url>
<url>
  <loc>https://wporep.com/category/lifehack/</loc>
  <priority>0.8</priority>
</url>
<url>
  <loc>https://wporep.com/category/design-font/</loc>
  <priority>0.8</priority>
</url>

 

手順2 コマンドを生成する。

Webページのタイトルを取得するには、ページにアクセスしてタイトルタグの中の文字を取得する。
以下のコマンドの URL のとこにタイトルを取得したいアドレスを入れる。

curl -s URL | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'

エクセルのマクロを使ってサイトマップからコマンドを作流のが楽。何百ものURLを手打ちはちょっとツライ。

Sub Curlコマンドを生成()
 
Sheets(2).Cells.ClearContents
Sheets(3).Cells.ClearContents
 
Sht1最大行 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Sht2最大行 = 1
 
For 行 = 1 To Sht1最大行
 
If InStr(Sheets(1).Cells(行, 1), "<loc>") > 0 Then
 

あ = LTrim(Sheets(1).Cells(行, 1))
 Debug.Print あ
い = Len(あ)
う = Right(Sheets(1).Cells(行, 1), い - 5)
え = Len(う)
お = Left(う, え - 6)
  Debug.Print お
Sheets(2).Cells(Sht2最大行, 1) = "curl -s " & お & " | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt"
Sheets(3).Cells(Sht2最大行, 1) = お
 
Sht2最大行 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row + 1
 
End If
 
Next
 
Sheets(2).Select
End Sub

Sheet1に、手順1で生成した sitemap.xml を貼りつけてマクロを実行すると、Sheet2に、URLからタイトルを取得して Title.txt 書き込むコマンドがURLの数だけ生成される。
Sheet3には、URLだけのリストが生成される。(おまけ)

手順3 ファイルに保存

Sheet2の中身をコピーしてテキストファイルに貼り付けて好きな名前で保存する。(例:TitleGet.txt)

curl -s https://wporep.com/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/category/aviation/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/category/lifehack/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/category/design-font/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/category/ads-b/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/yokota-arrival-report/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/apple/airpodspro-fuzoku/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/ads-b/hardware/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/lifehack/covid-nite01/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt
curl -s https://wporep.com/airport/yokota/military-aircraft/ | grep -o '<title>.*</title>' | sed 's#<title>\(.*\)</title>#\1#'>> Title.txt

手順4 ターミナルから Bash コマンドを実行する。

ターミナルを起動して Bash と入力してさっき保存したtitle.txtをターミナルにD&Dする。(すでにTitle.txt があったら削除しておくこと)

コマンドが終了するとTitle.txtにブログタイトルが書き込まれている。
サイトにアクセスするのでちょっと時間がかかる

手順5 URLとブログタイトルの全リストが完成

Title.txtにURLから取得したブログ記事のタイトルが書き込まれている。

マキタのコードレス掃除機をバッチリ支えるクリーナースタンド|wpo調査報告書2.0
アロハ! ANA A380 FLYING HONU。想像以上のウミガメっぷり!!|wpo調査報告書2.0
現場色。建設現場で働く建機&重機のカラーリング|wpo調査報告書2.0
グッデー!横田基地に飛んでくるヒコーキたち。まもなく着陸の戦闘機・輸送機コレクション|wpo調査報告書2.0
NAF厚木基地日米親善春祭り2019 この身分証明証の準備を忘れないようにな!|wpo調査報告書2.0

手順6 ブログ記事のタイトル一覧が完成

Sheet3に、Title.txtの中身を貼りつけると一覧の完成!

ブログのタイトル一覧は、ブログの記事管理に役になっている。

何百を超えるURLのコマンドを手打ちで作るのは時間がかかるし、ミスっちゃうこともある。こんな機械的な仕事は、エクセルのマクロに任せるのが楽。エクセルは使い方次第で色々なことができるのでMacユーザも持っていると便利。

 

エクセル 2019(最新 永続版)オンライン版 Win10/Mac対応
マイクロソフト

MacとWinで両方使える2ライセンス。
WinのファイルをMacのエクセルでも使えてとても便利。
マクロも動いてとても快適。