ブログ(WP)全記事リストをスプレッドシートに作成!マクロ一切使わずにURLも表示

スポンサーリンク
スポンサーリンク
WordPress

ブログ(WordPress)の記事をリライトする時に記事数が多くなると「リライト済み」かを全記事管理するのが大変です。

Googleスプレッドシートで全記事をリスト化するとリライトした記事の管理に役立ちます。

カテゴリー1カテゴリー2記事タイトルURL更新日
親カテゴリー名
記事タイトル1記事URL8/1
子カテゴリー名
記事タイトル2記事URL

例えばこんな表を作成して更新日を付けたり「リライト済み」の記事の背景を塗りつぶしたりと管理が出来ますね。管理に必要な項目を追加しやすいです。

マクロを使用したほうが効率が良い気がしますが、プログラム的なことできない人にとってマクロ機能は使えません。

記事数が増えると作業が多くなるのですが、ある程度は機械的な流れになるので全くの手作業よりは断然楽です。特に記事タイトル横に記事URLを貼り付ける作業は面倒ではなくなります。

記事リライトに直接結びつく内容ではなりませんが、リライト初めてでも管理は大事なのでスプレッドシートに全記事のタイトルとURLのリストを作成する作業をメモとして書いていきます。

スポンサーリンク
スポンサーリンク

初めに準備するツール

Windowsソコンを前提で進めていきます。

  • WordPressプラグイン「PS Auto Sitemap」
  • エディタソフト(ここではサクラエディタ使用)
  • Googleスプレッドシート

この3つを使用します。

エディタソフトは「正規表現」が使え「一括置換」が使えるソフトを用意してください。ここでは『サクラエディタ』を使います。

WordPressプラグインの『PS Auto Sitemap』もブログにインストールしておいてください。「PS Auto Sitemap」はユーザー向けにサイトマップ(全記事一覧)のページを生成するプラグインです(検索エンジン向けXMLサイトマップとは異なります)。

「PS Auto Sitemap」で固定ページにサイトマップを作成

「PS Auto Sitemap」の設定は特にありません。公開するわけではないので固定ページは「非公開」にしてます。

公開する目的などで細か設定をする際はバズ部さんの記事が参考になります。

固定ページにサイトマップのコードを記入する

新規に固定ページを作成して、HTMLのタグが直接打てる「テキスト」モードで

<!-- SITEMAP CONTENT REPLACE POINT -->

と入力します。これで、固定ページにサイトマップが表示されます。

今回は、サイトマップを固定するわけでは無いので「非公開」にしています。

固定ページにサイトマップを表示

固定ページにサイトマップを表示

「PS Auto Sitemap」の作業はこれで完了です。

スプレッドシートに全記事をコピーする

サイトマップを作成した固定ページのカテゴリーと記事タイトルをコピーします。

サイトマップの記事タイトルをコピー

サイトマップの記事タイトルをコピー

カテゴリーも含めてコピーします。

スプレッドシートに全記事タイトルを貼り付ける

スプレッドシートに全記事タイトルを貼り付け

スプレッドシートに全記事タイトルを貼り付け

カテゴリー名や記事タイトルが1列になって貼り付けられます。記事タイトルにリンク(ハイパーリンク)も付いています。

カテゴリーと記事タイトルを整理する

カテゴリーと記事タイトル整理

カテゴリーと記事タイトル整理

記事タイトルを横のセルに移動させカテゴリーと分けて整理ます。ここは手作業になります。

記事数が多くなるほど大変な作業なので、キーボードでセルを移動させたりコピペなどショートカットキーをうまく使いましょう。

カテゴリーと記事タイトルを整理していると分かるのですが、複数のカテゴリーに登録されている記事はタイトルが重複され出てきます。

重複記事の確認に「UNIQUE関数」を使用しますが、重複チェックをやり易いように「記事タイトルの列」の「カテゴリー行」の空白に連番(1,2,3…)やアルファベット(a,b,c…)などを入れます。

UNIQUE関数で抽出された記事は重複を除外して詰めて表示させるのでカテゴリー区切りの「空白セル」も無くなります。これだと、抽出された記事タイトルがどのカテゴリーかか分かり難いので連番を入れて見やすくしてます。

UNIQUE関数を使用して重複排除をする

UNIQUE関数は簡単に言うと重複を排除する関数です。

参考

スプレッドシート:重複チェックを簡単にできるUNIQUE関数について | ひとりで.com
スプレッドシートにしかない関数:UNIQUE関数で重複を排除。Google Spreadsheetには、Excel関数にはない関数が多数用意されています(その逆もあります)。今回紹介する関数はその中でも非常に便利で、ぜひともExcelにも導入して欲しい!と思わせる関数です。重複する行を破棄して、指定したソース範囲内の一...
元の項目UNIQUE関数を使用した項目
北海道北海道
岩手岩手
北海道秋田
秋田

北海道が1つになりました。

このように重複されてる項目は1つしか表示しません。このUNIQUE関数を重複チェックに利用します。

UNIQUE関数で記事タイトルを選択

UNIQUE関数で記事タイトルを選択

2行目の記事タイトル横のセルに「=UNIQUE」と入力して、同じ2行目から記事タイトルの列を選択します(記事タイトルと「=UNIQUE」セルの間1列空ける)。

UNIQUE関数で重複除外の記事タイトルが表示される

UNIQUE関数で重複除外の記事タイトルが表示される

重複記事が除外される

重複記事が除外されるので行が少なくなる

UNIQUE関数で重複除外された記事タイトルが抽出されます。

カテゴリー区切りの記事タイトルのセルに「連番」を入れているので抽出された記事タイトルもどのカテゴリーか一目でわかります。

UNIQUE関数を使って表示されているだけなので、抽出された記事タイトルをコピーします。この時、UNIQUE関数を入力したセルを選択しないように注意します。

隣のセルにコピー

隣のセルにコピー

隣のセルにコピーしたらUNIQUE関数を使用した列を削除します。

隣の記事タイトルと合わせる

隣の記事タイトルと合わせる

重複元の記事タイトルとセルを合わせてください。これも手作業なのでショートカットキーをうまく使います。

重複された記事タイトルは最初のカテゴリー(階層ではなくてサイトマップでリスト化した時の最初の順のカテゴリー)が優先されて表示されるので、他のカテゴリーにする場合は「UNIQUE関数」で抽出したタイトルを移動させます。

記事タイトルをコピペして「Ctrl + F」で検索すると他のカテゴリーにある同じ記事が見つけやすいです。

「UNIQUE関数」で抽出した記事タイトルが整理出来たら空白の列を削除します。

重複してる空白列を削除

重複してる空白列を削除

重複記事を除外した

重複記事を除外した

重複記事の列を削除したらカテゴリー余白に入れた連番を削除します。

連番は不要なので削除

連番は不要なので削除

先ほど付けた連番も不要なので削除します。

シート2を作成して記事タイトル列をコピペする

HTMLに出力するためにシート2に記事タイトルをコピペします。

記事タイトルを選択

記事タイトルを選択

シート2に記事タイトルをコピペ

シート2に記事タイトルをコピペ

サクラエディタで記事URLを抽出する

スプレッドシートをHTMLで保存する

[ファイル]→[形式を指定してダウンロード]→[ウェブページ]

の順に選択してダウンロードします。ファイルはZIP形式なので解凍します。

スプレッドシートから作られたHTML

スプレッドシートから作られたHTML

シート2.htmlをサクラエディタで開く

解凍したフォルダの中に「シート2.html」があるのでサクラエディタで開きます。

サクラエディタでシート2.htmlを開く

サクラエディタでシート2.htmlを開く

URL以外を置換で取り除く

URLのみを抽出するために、一括置換でタグ終わり「”>」に改行を追加します。

サクラエディタでの置換

サクラエディタでの置換

置換前:“>
置換後:“>\r\n

正規表現にチェックを入れて、【すべて置換】をクリックします。

※置換前、置換後共に半角で入力します。

改行される

改行される

続いて、URL以外を削除するためにもう一度置換します。

URL以外取り除く

URL以外取り除く

  • 置換前:^((?!https://[\w\d/%#$&?()~_.=+-]+).)*
  • 置換後:
  • 正規表現:チェック入れる☑

※「置換後」は空白です。

置換前の「https」は非SSLサイトの場合「http」に変えてください。

参考(置換でURL一致させる)

正規表現サンプル(URLを検索する)
正規表現のサンプル集を検索と置換に分けて説明つきで詳しく紹介します。〜 検索編 〜

参考(置換にURL含まない)

<サクラエディタ正規表現⑤>否定、含まない ~文字列を含まない行の抽出~ - WEB-zarashi
文字列を含まない行の抽出(否定条件)それでは文字列を含まない行の検索・置換の手順を紹介します。
URL以外取り除いた

URL以外取り除いた

URLの末尾に「”>」が残っていますが、このまま作業を進みます。

改行があるので次に改行を取り除きます。

  • 置換前:\r\n
  • 置換後:
  • 正規表現:チェック入れる☑

改行を取り除いても下矢印「↓」が表示され改行が残っていることがあります。その場合はもう一度一括置換で改行を削除します。

  • 置換前:\r
  • 置換後:
  • 正規表現:チェック入れる☑
URL末尾を取り除き改行

URL末尾を取り除き改行

改行されずURLがびっしり表示してますね。

今度はこれをURL末尾の記号「”>」の末尾を改行します。次いでに「”>」も削除します。

  • 置換前:“>
  • 置換後:\r\n
  • 正規表現:チェック入れる☑
サクラエディタで記事URL抽出

サクラエディタで記事URL抽出

URLが改行され抽出できました。

これをスプレッドシートにコピペします。

再びスプレッドシートに戻ってサクラエディタで抽出したURLを貼り付けます。

スプレッドシートの記事タイトルとURLを合わせる

サクラエディタで抽出したURLをスプレッドシートの記事タイトル横のセルに貼り付けます。

URLを貼り付ける

URLを貼り付け

URLを貼り付け

URLを貼り付けて、記事タイトル最下部に揃えます。最下部に揃えることで記事タイトルとURLを関連付ける作業がやり易くなります。

記事タイトルとURLを関連付ける(横のセルに合わせる)

URLを記事タイトルに関連付ける

URLを記事タイトルに関連付ける

記事タイトルの空白行からURLの列上部を選択します。

記事タイトルにURL合わせる

記事タイトルにURL合わせる

URL1セル分上に移動させます。

キーボードの「shift+↑」を選択して、次の記事タイトル余白と同じ行に合わせて作業を繰り返します。キーボードで「選択範囲」を移動させると「選択下部」が動くので記事タイトルとURLを下のセルに合わせたほうが作業がしやすいのです。

記事タイトルのURLと一致してるか抜き打ちチェック

記事タイトルのURLと一致してるか抜き打ちチェック

記事タイトルにマウスカーソルを合わせるとURLが表示されます。このURLと隣セルのサクラエディタで貼り付けた「URL」が一致してるかチェックします。適当に抜き打ちチェックだけでも大丈夫かと思います。

記事タイトル隣セルにURL貼り付ける

URLをシート1の記事タイトル横に貼り付ける

URLをシート1の記事タイトル横に貼り付ける

シート2のURLをシート1の「記事タイトル」右横のセルに貼り付けます。

これで作業は完了です。

URLと記事タイトルを合わせる作業をシート2で行ってますが、シート1だけでも作業できましね(^^;

サイトマップ作成とスプレッドシートで記事リスト作成できる

WordPressのサイトマッププラグイン「PS Auto Sitemap」とGoogleスプレッドシートのUNIQUE関数を利用することで全記事リストが作成できます。これに、エディタソフトも使う事でURLもリスト化できます。

記事数が増えると作業も多くなりますが、全て手作業で行うよりも早いですし何よりマクロを知らなくても簡単にできます。

記事タイトルとURLをリスト化、後はリライトに必要な項目を追加すると良いと思いますよ。

作業手順に無駄な部分があり、特に正規表現によるURL抽出はもう少し一発で出来そうな感じもありますが、メモとして参考までに・・・(^^

スポンサーリンク
スポンサーリンク
WordPress
RSS
スポンサーリンク
コロロング