Googleスプレッドシートサイトマップ作成便利関数まとめ #spreadsheet

サイトマップを自動で作ってくれるサイトは色々ありますけど、自分で簡単にできないものかと思ってスプレッドシートを駆使した時のメモ。JavaScriptは使わず関数だけで。

指定したURLのタイトルを取得

IMPORTXML関数で簡単に取得できます。Excelにはない関数でとても便利。

	A	B
1	http://qiita.com/Qiita/items/c686397e4a0f4f11683d

B1セルに以下の式を記入します。

=IMPORTXML(A1,"//title")

すると、

	A	B
1	http://qiita.com/Qiita/items/c686397e4a0f4f11683d	Qiita - Markdown記法チートシート - Qiita

タイトル取れた！＼(^o^)／

問題は取得先のファイルがUTF-8でないといけないこと。
IISサーバーで、Shift_JIS使っていて、日本語タイトルが文字化けする罠にハマった。

これも同じくIMPORTXML関数で取得します。

	A	B
1	http://qiita.com/Qiita/items/c686397e4a0f4f11683d	Qiita - Markdown記法チートシート - Qiita
2

要はソースコード内のa要素のhref属性の値を取得したいので、A2セルに以下の式を記入します。

=IMPORTXML(A1, "//a/@href")

	A	B
1	http://qiita.com/Qiita/items/c686397e4a0f4f11683d	Qiita - Markdown記法チートシート - Qiita
2	/
3	http://shop.qiita.com/
4	/login?redirect_to=%2FQiita%2Fitems%2Fc686397e4a0f4f11683d
5	/signup?redirect_to=%2FQiita%2Fitems%2Fc686397e4a0f4f11683d
6	/tags/qiita
7	※以下省略

取れた！＼(^o^)／
後はルートパスからの値は、先頭にドメインを結合するなど別セルで文字列にしてあげれば、指定したページからのリンクが確認できます。そのパスに対してタイトルを取得するようにすれば、リンク先が何のページかも確認できて便利です。

サイトマップを作る際にディレクトリの深度を知りたくて、「/（スラッシュ）」区切りでセルの分けることがしたかったので、以下の関数を用いて分割しました。
C1セルに以下の式を記入します。

=split(A1,"/")

	A	B	C	D	E	F	G
1	http://qiita.com/Qiita/items/c686397e4a0f4f11683d	Qiita - Markdown記法チートシート - Qiita	http:	qiita.com	Qiita	items	c686397e4a0f4f11683d

分割された〜！＼(^o^)／
あとはセルの数を数えて深度を計算して、その深度によって色を変えたりと色々工夫できます。

今回はサイトマップ作成だったので使いませんでしたが、テーブルやリストの取得には、IMPORTHTML関数を用いるようです。こちらもかな〜り便利。