バンビの初心者ブログ教室

私がブログ運営で月間10万円稼いだ方法を教えます!

【はてなブログを一括整理したい方へ】サイトマップsitemap.xmlから記事タイトルやURLを抽出してGoogleスプレッドシートに記事一覧を書き出す方法

※当ブログ内では商品広告やPRを行っております。

はてなブログを一括整理

 

(ほぼ自分用メモです)
タイトルも中身もマニアックですが、こんな人におすすめ!
 ↓↓

ブログの記事数がたくさんあり過ぎて整理できない!

自分のブログの全記事のURLやタイトルをリストにしたい!

HTMLで見やすいサイトマップを作りたい!

 

だけど、いちいちブログの全部の記事を開いてURLをコピペして...なんて面倒臭い。
なんか一括して出来る方法ないの?
という方のための問題解決方法です。


難しいことはないのですが、ちょっと面倒臭いです。
ぜひ、お時間のある時に♡

 

 

 

※当ブログは、はてなブログで運営しており、基本的には「はてなブログ」ユーザー向けの記事となっておりますが、他のブログサービスでも【sitemap.xml】がある場合は同じようにできると思います♪♪

 

サイトマップ【sitemap.xml】とは?

 

「はてなブログ」では、自動的にサイトマップを作ってくれます。
↓↓こちら

https://自分のURL/sitemap.xml

試しに上記のURLを入力してみてください。
「自分のURL」のところには、自分のURLを入れてね^-^
(http://の方はhttp://で!)

例えば、私の場合はこうなります。
↓↓

 URL



そうすると、こんなのが出てくると思います。
これがサイトマップです!
↓↓

サイトマップ
むむ?
ワケが分からないですね〜

これは「サイトマップ」と言っても私たちのための「サイトマップ」ではなくて、Googleなどの検索エンジン(bot)に分かりやすく示すためのもの。
人間用ではないんです。だから良くわからないんですね。

今回はこの「sitemap.xml」を使って、記事一覧を作成する方法を説明します!

 

 Googleスプレッドシートを使います!

 

Googleスプレッドシートとは、オンラインで使えるGoogle版のExcelみたいなものです。
ダウンロードも必要なく、Googleのアカウント持っていれば誰でも使えます。ファイルを共有したりも簡単にできるので、私は良く使っています。

Google スプレッドシート↓↓

Google Sheets: Free Online Spreadsheets for Personal Use

 

使い方などは、ここでは解説しませんが、分からなくても、これから説明することをやれば誰でもできます。


スプレッドシートには、importxml( ) 関数unique( )関数というものがあって、今回はそれを使うのですが、別に意味がわからなくても大丈夫です。
私もわかってません、笑。
(詳しく知りたい方は自分で調べてね)

 

この後は面倒臭いので、お時間とヤル気のある時に(^-^)

 

 

 

全ての作業は100記事ずつしかできない!


まず最初に!

これは「はてなブログ」だけかも知れませんが(すみません。詳しく知らない)はてなブログのサイトマップ sitemap.xml は、100個ずつの区切りになっているので、作業は100個単位でしか出来ないということを先にお伝えしておきま〜す!

なので、記事が100記事以下の場合は、作業は1回。200記事の場合は2回。
1000記事の場合は10回、10000記事(そんなヒトは居ないと思うけど居たら尊敬です)の場合は100回...の作業が必要になります。


サイトマップは次のようになっています。

100記事以内の場合↓↓

サイトマップ

 100記事以上の場合↓↓

サイトマップ

このように ?page=1, ?page=2...となっていて、この1個の塊ごとに100記事が入っている仕様になっています。

なので記事のURLのリストを取得する作業はこの?pageの分だけやることになります!
それでも、一つ一つウィンドウを開いてコピペするよりは全然ラクだと思います♪♪


それでは、やり方を説明します。

 

 

【1】まずは sitemap.xml を html に変換する

 

1)先ほどの https://自分のURL/sitemap.xml?page=1の部分をコピぺ。

サイトマップ

 

2)↓↓ブラウザのアドレスバーにペーストします。

(画像はChromeです。SafariやFirefoxでも大丈夫!)

ブラウザ

3)↓↓ sitemap.xml?page=1の内容がでてきます。
ここに100記事分入っている!

サイトマップ

 

4)画面上で右クリックをして、このページのソースを表示。

サイトマップ



5)ソースはこんな感じ
(別ウィンドウで出てくると思います)
htmlコードが出てくるのでアレルギー反応が出る場合は我慢!(笑)

htmlソース

 

【2】htmlコードで仮のページを作る


1)htmlコードを全部コピーする(ctrl + a , ctrl + c)

htmlソース


2)はてなブログの「記事を書く」を開き、
①HTML編集
②ペースト
(↓↓画像は間違えてコピペと書いてしまったけど、さっきコピーしたhtmlコードをここにペーストします!)
③下書き保存

HTML編集

 

3)プレビューページのURLを取得します
はてなブログの「記事の管理」の画面で、編集の横の∞(←このマークみたいなやつ)をクリックすると「下書きプレビュー」のURLが出てくる。
↓↓このURLを使います!
仮ページ

 

少し休憩!

疲れてきちゃったので、ちょっと休憩(笑)
早く先に進みたい方は自分でスクロールしてくださいね♡

 

〜ちなみにメモ〜
せっかく調べたので...

  
↑このマークは「カンジャニエイト」と入れると出てくるよん!

 

 


私が「xml」から「URL」を抽出しようと思った、そもそものキッカケ。

これまでは、自分のブログの中で人気記事は何だろう?って気になってアナリティクスを見ることはあったんだけど、ブログ運営も1年が経ち、そー言えば

人気のない記事は?

という疑問を持ったのです。

そこでアナリティクスやサーチコンソールを調べたんだけど、実は人気がなくてアクセスが「0」ゼロのページって調べられないの!
あくまでも統計が取れるのはアクセスがあったページだけ。

だから、例えば1ヶ月に1回しか需要がないページは分かるけど、1ヶ月間全く検索も閲覧もされてないページは見つけられないわけ。

でも、アクセスがあったページのURLはアナリティクスからデータのエクスポートができるので、ブログの全URLと比べればアクセスがゼロのページが割り出せるじゃん♪
楽勝〜♡

と簡単に考えていたものの、ブログの全URLをゲットするのが意外にも結構大変だったのです。

一個ずつコピペしていっても良いけど→面倒臭い。

やり方を説明しているブログやサイトをたくさん見つけたんだけど、どれも難しくて、しかも何かをインストールしないと出来ないことが多かったので、それも→面倒臭い。

で、考えた末のこのやり方です。
この記事では、一覧取得ですが、続けてアクセス「0」ゼロのページを探す方法の記事も書く予定ですので知りたい方は待っててね♪♪

では、休憩は終わりにして説明に戻りますよー


 

ここからはGoogle スプレッドシートでの作業

この後はGoogleスプレッドシート上で作業をします。
これね。
↓↓
Google Sheets: Free Online Spreadsheets for Personal Use

 

 

 

【3】IMPORTXML関数で記事のURLを取得する

 

1)新しいシートを開いてA1に下の関数を入力します
下記よりコピペどうぞ(^-^)

=UNIQUE(IMPORTXML("URL", "//a/@href"))

 

スプレッドシート


2)"URL"の部分に先ほど取得した下書きプレビューのURLを入れます

↓↓これです。このページに戻ってコピーする。

仮ページ

ペースとして→エンター!!
スプレッドシート

 

じゃーん!!
↓↓
勝手にサイトマップからURLを抽出して表示してくれます!
でも、まだこれで満足しちゃダメ!(今作業の半分くらい終わったかな...)

スプレッドシート


【4】フィルタ機能で「記事」を表示させる

 

ここに出てくるURLは、記事のものだけではなく勝手に作られるカテゴリーリンクやや元々デフォルトで表示されている「はてなブログを始める」みたいなヤツのリンクまで拾って来てくれちゃいます。

今回はフィルタ機能を利用して、純粋な「記事」だけのリンクを表示させます。
※必要に応じて、セルの幅を広げた方が作業がやりやすいです♪♪


1)フィルタを作成

①左上の何もないセルをクリック(全てが選択された状態になる)
②データ
③フィルタを作成

フィルタを作成

↓↓こんな逆三角の矢印みたいのが表示されていればOK!

フィルタ

 

2)「次を含むテキスト」でフィルタ

緑の四角をクリックするとプルダウンが出てくるので「条件でフィルタ」→「次を含むテキスト」を選択。
※はてなブログの場合は、記事ページの始まりは「自分のURL/entry/」になっているので、その文字列を含むテキストでフィルタをかけます。

フィルタ

以下の順序で
①条件でフィルタ
②次を含むテキスト
「自分のURL/entry」

これでカテゴリーやその他の関係ないURLが非表示になり、記事だけのURLになりました♡
次に記事のタイトル名を取得していきます!

 

 

 

【5】IMPORTXML関数で「記事のタイトル」を取得する

  

1)B1に下↓の関数を入力します

=IMPORTXML(A1, "//title")

 

タイトル


B1のセルには「N/A」と出てエラーになりますが、それでOK!

 

2)端っこをダブルクリック

タイトル
B1のセルの右下に「+」のポインターを合わせてダブルクリックする。
(↑ちなみに、下の行に同じ設定を反映させるExcelとかでも使える技です!)

 

タイトルが出てくるまでに結構時間がかかります。

タイトル

 

3)N/A(空白)のセルを非表示にする

表示されたタイトルの中に、下記のように「N/A」と書かれている記事は、存在しない(NOT FOUND)の可能性があります。これらも炙り出せます。

タイトル


先ほどのフィルタを使って「空白ではないセル」を設定して「N/A」のセルを非表示にします。

タイトル

 

4)テキストを列に分割(やりたい方だけ)

はてなブログの場合は、自動的にタイトルの後ろにブログ名が表示されてしまいます。一覧にした時に全てにブログ名が付いているのがウザいのでブログ名も消します。

気にならない方はやらなくて大丈夫です。
(関数を使ったり、文字を置き換えとか試してみましたが、私は下記の方法が一番楽でした)

4-1)テキストを列に分割
①B列の上のバーをクリック
②データ
③テキストを列に分割

分割

 

4-2)カスタムで区切る
右下に小さく↓↓こんなのが出てくる

区切り文字

(見逃しやすい)

「カスタム」を選択し、区切り文字を入れます。
区切り文字は、自分のブログのタイトルを入れれば良いと思いますが、はてなブログの場合はブログ名の前にこんなの→「 - 」(半角のハイフン)が入っているのでこれをいれる。(タイトル内で半角ハイフンを使っている人はブログ名などを入れる)

タイトル分割

区切り文字↓↓ 「 - 」と入れる。
その文字から後ろが別の列(C列)へ移動します。

区切り文字

4-3)C列は必要ないので削除しちゃいましょう〜^-^

C列を選択して右クリック。列を削除

タイトル分割

 

これで、URLとタイトルのリストが出来上がりました。
ここまで来て、まだ余力がある方は次にカテゴリー名を取得しましょう♪♪
(カテゴリー設定などをされてない方はこれで終わりです♪お疲れ様でしたー♡)

 

 

 

【6】IMPORTXML関数でカテゴリー名を取得する

ココが今回一番苦労したところです!
カテゴリー名を取得すると整理がしやすくなるかな?と思ってどうしてもやりたくて、色々と検索したけど全然資料なくて、なんとか独自で編み出しました(笑)

ここまでやった皆様なら、この先の作業は簡単です。

 

1)セルC1に次の関数を入力します↓↓

=IMPORTXML(A1, "//div[@class='entry-categories categories']")

 カテゴリ

※ちょっとだけ解説すると、カテゴリー名が入っているclassが「はてなブログ」の通常の場合は上記の「entry-categories categories」なので、そこからカテゴリー名を引っ張ってきます。
はてなブログ以外やはてなのテーマによって違うclass名になっている場合は、Chromeのデベロッパーツールなどでclass名を調べることができます。(やり方はぜひ自分で調べてね)

2)列全体に設定を反映させる
先ほどと同じようにエラーになるので、C1のセルの右下にポインター(+)を合わせてダブルクリック!これはもう覚えましたね^-^

カテゴリ

カテゴリ取得も結構時間がかかります...
このように自動的に横並びでカテゴリーが出てきます!

カテゴリ


3)フィルタを使ってカテゴリを整理する

ここまで出来たら、あとは先ほどのフィルタの機能でカテゴリーごとに表示をさせたりして整理しましょう♪♪

緑の逆三角からフィルタをかけます。
値でフィルタは、予めカテゴリーが表示されていているので「クリア」でチェックを外し、表示させたいカテゴリーにチェックを入れます。

カテゴリー整理

↓↓こんな風にチェックを入れたカテゴリだけを表示することができます♪♪

カテゴリー整理


※ちなみに上の画像で表示している「ブログ・アクセスアップ方法」をご覧になりたい方はこちらです!
アクセスアップのご参考になるかと思います(^-^)
↓↓

月間10万PV越え!これまでに試したアクセスアップの方法 - Bambi's BLOG @ hatena

 

★ついでに宣伝!★

当ブログで使用している「はてなブログのテーマ」は私がデザインしたテーマです!
お好きな3色を選ぶだけでイイ感じに配色できてカスタマイズが簡単です♪
いつも1ページ目に表示されてて結構人気のテーマだったりするんですよ^-^

はてなブログ テーマ「Palette(パレット)」について

はてなのテーマストアからインストールできます♪
ご興味のある方はぜひ♡

 


以上、今回はかなりマニアックな

【はてなブログを一括整理したい方へ】

サイトマップsitemap.xmlから記事タイトルやURLを抽出してGoogleスプレッドシートに記事一覧を書き出す方法

をお送りしました♡

 

★今後のアップ予定★

せっかくココまでリストが出来たので、次回からこのリストを活用して出来ることをお送りしたいと思います♪♪

一括して書き出した一覧からHTMLのサイトマップを作る方法

書き出した自分のブログのURLからアクセス状況を調べる方法

 

 

 

参考にしたサイト一覧

以下のサイトを参考にさせていただきました。ありがとうございます(^-^)
また、WordPressの方や上記の説明で上手く行かなかった方は、ぜひ下記からご自身にあったやり方を探してみてくださいね!

IMPORTXML関数に関して---

IMPORTXML - ドキュメント エディタ ヘルプ

webサイトのリンクURLを抽出する方法 │ デザインのまとめ

How to Import Data from Any Web Page into Google Sheets with ImportXML

Use Google Sheets’ ImportXML function to display data in Geckoboard – Geckoboard Help Center

Web Scraping Using ImportXML in Google Spreadsheets // Link To Sheets

スプレッドシートの使い方-IMPORTXML関数でWebサイトの情報を取得(スクレイピング)する方法 | タニポ

ImportXML関数の使い方!スプレッドシート上でサジェストを取得しよう!

XPath チートシート---
http://aoproj.web.fc2.com/xpath/XPath_cheatsheets_v2.pdf#search=%27Xpath+%E4%B8%80%E8%A6%A7%27

IMPORTXML関数で、XPathを使ってデータ抽出ができる - Bye Bye Moore

 


その他のスプレッドシート関連

【スプレッドシート】Query関数のcontainsで指定文字列を含む行を表示 | スプレッドシートの使い方はWebマーケ戦略会議

SUBSTITUTE|文字を検索し置換する【スプレッドシート関数】

Googleスプレッドシートだって空白セルのある行の削除方法を知りたいの! - Macに囲まれながら余生を過ごす

知っていたらドヤれる!スプレッドシート独自の関数を10個まとめてみた | たぬハック

ハイパーリンクを指定した文字列を作成する【Googleスプレッドシート】 | スプレッドシート道場

スプレッドシートの値だけ貼り付けのショートカット | アップスイズム

 

サイトマップなどに関して紹介しているページ

はてなブログに読者用のサイトマップを作ってみよう - つなろっく

GoogleはXMLサイトマップの「URL」と「最終更新日」を気にする|SEOラボ

はてなブログで記事URL一覧を取得する方法

 

アクセス数ゼロのページを調べる方法

Google Analyticsで検索流入ゼロの低品質コンテンツを洗い出す方法 – SimilarWeb日本公式ブログ

 

ついでにWordPressの人用の記事

WordPressで、アクセス数(検索流入数)がゼロの記事を探す方法 | エテルナコラム