Google スプレッドシートに書かれているurlからタイトルとmeta descriptionを取得する

Google スプレッドシートに書かれているURLからタイトルとmeta descriptionを取得する。タイトルそのままです。

「Google スプレッドシートのデータの取得と書き込み」のようなタイトルの記事は先人たちがすでに大量にアップしているのでニッチなタイトルにしようかと思ったのですが、実際にはこのネタもすでに数多く公開されているんですよね。しかも思ったよりも簡単な方法で・・・。

Contents

1 Google スプレッドシートを使った簡単スクレイピング
2 Google Sheets APIでスクレイピングの結果を書き込む

Google スプレッドシートを使った簡単スクレイピング

Google スプレッドシートで簡単なスクレイピングができてしまいます。やり方は簡単。スプレッドシートに以下を書き込むだけで https://lets-csharp.com/ のタイトルを取得することができます。

=INDEX(IMPORTXML("https://lets-csharp.com/", "//title"),1,1)

1	=INDEX(IMPORTXML("https://lets-csharp.com/", "//title"),1,1)

タイトルタグはひとつのページにひとつしかないものですが、WordPressのプラグインを使っていると複数生成される場合があります。この場合、スプレッドシートの2行に出力されてしまうので最初のひとつだけを出力させるためにINDEX関数を使っています。

またA列にurlを書きこんでB列に以下のように書くとurlから簡単にタイトルを抜き出すことができます。

=INDEX(IMPORTXML(A1, "//title"),1,1)
=INDEX(IMPORTXML(A2, "//title"),1,1)
=INDEX(IMPORTXML(A3, "//title"),1,1)

=INDEX(IMPORTXML(A1, "//title"),1,1)

=INDEX(IMPORTXML(A2, "//title"),1,1)

=INDEX(IMPORTXML(A3, "//title"),1,1)

タイトル以外にも抜き出せます。SEOではタイトルと同様に重要なものとしてメタディスクリプション(meta description)があります。meta descriptionは検索順位に直接影響を与えるものではなく、そのページの要約を端的にまとめたものです。検索結果にタイトルとともに表示されるため、クリック率に大きな影響を与えます。そのmeta descriptionも簡単に抜き出すことができます。

=INDEX(IMPORTXML(A1,"//meta[@name='description']/@content"),1,1)

1	=INDEX(IMPORTXML(A1,"//meta[@name='description']/@content"),1,1)

以上で終わります。・・・とはなりません。実はこの方法で大量のurlを取得しようとすると固まってしまうという問題点があります。手軽さでいえばこの方法がベストですが、柔軟性と自由度でいえば他の方法を考えたほうがいいかもしれません。

Google Sheets APIでスクレイピングの結果を書き込む

そこでGoogle Sheets APIをつかってA列のurlを取得して対象ページをスクレイピングして結果をB列とC列に書き込むアプリケーションを作成します。

スプレッドシートの内容を取得したり書き込むためにはGoogle Cloudでプロジェクトを作成します。

Google Cloudでプロジェクトを作成する

まずはここへアクセスしましょう。Googleアカウントにログインしていない場合はログインするように表示されるのでログインします。すると[新しいプロジェクト] ページが表示されます。

Google Cloudでプロジェクトを作成する

[プロジェクト名]は表示されている名前をそのまま使用するか、別の名前を入力します。プロジェクト名はいつでも変更できます。ただしプロジェクト IDは変更できません。そのプロジェクトがなにかわかりやすい ID を設定するべきですが、機密情報は含めないようにしてください。

あとは以下の方法でGoogle Sheets APIを有効にしてサービスアカウントを作成し、Googleスプレッドシート側からサービスアカウントに編集権限を追加します。Googleスプレッドシートを自由に操作できる権限があるのであればこの方法が一番簡単だと思います。

C#でアプリをつくる

パッケージマネージャーでGoogle.Apis.Sheets.v4をインストールします。またHTMLを解析するためにAngleSharpをインストールします。

ボタンをクリックしたらスプレッドシートのA列に書かれているurlを取得してタイトルとdescriptionを取得してこれを書き込みます。

urlと取得したタイトルとdescriptionを格納するクラスを定義します。

public class Result
{
    public string Url = "";
    public string Title = "";
    public string Description = "";
}

public class Result

{

public string Url = "";

public string Title = "";

public string Description = "";

}

SheetsServiceを取得する

事前にダウンロードしておいたjsonファイルからスプレッドシートにアクセスするためのサービスを作成して取得する処理を示します。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Threading.Tasks;
using System.Net.Http;

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;

using AngleSharp.Html.Parser;
using AngleSharp.Html.Dom;

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
        label1.Text = ""; // ここに処理の状態を表示したい
    }

    SheetsService GetSheetsService()
    {
        string[] scopes = { SheetsService.Scope.Spreadsheets };
        string jsonPath = "secret.json";

        // JSONファイルから認証情報を作成
        GoogleCredential credential = GoogleCredential.FromFile(jsonPath).CreateScoped(scopes);
        // 認証情報からスプレッドシートにアクセスするためのサービスを作成
        return new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential });
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Windows.Forms;

using System.Threading.Tasks;

using System.Net.Http;

using Google.Apis.Auth.OAuth2;

using Google.Apis.Sheets.v4;

using Google.Apis.Sheets.v4.Data;

using Google.Apis.Services;

using AngleSharp.Html.Parser;

using AngleSharp.Html.Dom;

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

label1.Text = ""; // ここに処理の状態を表示したい

}

SheetsService GetSheetsService()

{

string[] scopes = { SheetsService.Scope.Spreadsheets };

string jsonPath = "secret.json";

// JSONファイルから認証情報を作成

GoogleCredential credential = GoogleCredential.FromFile(jsonPath).CreateScoped(scopes);

// 認証情報からスプレッドシートにアクセスするためのサービスを作成

return new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential });

}

Sheetからデータを取得する

スプレッドシートの内容を取得する処理を示します。IList<IList<Object>>型で返されるので、これを文字列の配列のリストに変換します。

public partial class Form1 : Form
{
    List<string[]> GetData(SheetsService service, string spreadsheetId, string range)
    {
        SpreadsheetsResource.ValuesResource.GetRequest request =
            service.Spreadsheets.Values.Get(spreadsheetId, range);

        ValueRange response = request.Execute();
        IList<IList<Object>> values = response.Values;
        List<string[]> vs = new List<string[]>();
        if (values != null && values.Count > 0)
        {
            foreach (var row in values)
                vs.Add(row.Select(_ => _.ToString()).ToArray());
        }
        return vs;
    }
}

public partial class Form1 : Form

{

List<string[]> GetData(SheetsService service, string spreadsheetId, string range)

{

SpreadsheetsResource.ValuesResource.GetRequest request =

service.Spreadsheets.Values.Get(spreadsheetId, range);

ValueRange response = request.Execute();

IList<IList<Object>> values = response.Values;

List<string[]> vs = new List<string[]>();

if (values != null && values.Count > 0)

{

foreach (var row in values)

vs.Add(row.Select(_ => _.ToString()).ToArray());

}

return vs;

}

URLからタイトルとdescriptionを取得する

URLからタイトルとdescriptionを取得する処理を示します。このとき引数として渡された文字列が空文字列であったりurlではなかったり、アクセスしても403や404などのエラーステータスコードが返される場合があります。そのため例外処理をしています。

public partial class Form1 : Form
{
    HttpClient HttpClient = new HttpClient();

    async Task<Result> GetResult(string url)
    {
        Result result = new Result();
        result.Url = url;
        result.Title = "error";
        result.Description = "error";
        try
        {
            string html = await HttpClient.GetStringAsync(url);
            IHtmlDocument document = (new HtmlParser()).ParseDocument(html);
            result.Title = document.Title;
            result.Description = document.QuerySelector("meta[name='description']")?.GetAttribute("content");
            // ページにはタイトルが設定されていないかもしれない
            if (result.Title == null || result.Title == "")
                result.Title = "no title";
            // ページにはmeta descriptionタグが存在しないしれない
            if (result.Description == null || result.Description == "")
                result.Description = "no description";
        }
        catch(InvalidOperationException e) // 引数はurlではないかもしれない
        {
            result.Title = "not url";
            result.Description = "not url";
        }
        catch (UriFormatException e)
        {
            result.Title = "not url";
            result.Description = "not url";
        }
        catch // 引数はurlだったとしてもエラーステータスコードが返されるかもしれない
        {
            result.Title = "request failed";
            result.Description = "request failed";
        }
        if (url == "") // 引数が空文字列だった場合
        {
            result.Title = "blank";
            result.Description = "blank";
        }
        return result;
    }
}

public partial class Form1 : Form

{

HttpClient HttpClient = new HttpClient();

async Task<Result> GetResult(string url)

{

Result result = new Result();

result.Url = url;

result.Title = "error";

result.Description = "error";

try

{

string html = await HttpClient.GetStringAsync(url);

IHtmlDocument document = (new HtmlParser()).ParseDocument(html);

result.Title = document.Title;

result.Description = document.QuerySelector("meta[name='description']")?.GetAttribute("content");

// ページにはタイトルが設定されていないかもしれない

if (result.Title == null || result.Title == "")

result.Title = "no title";

// ページにはmeta descriptionタグが存在しないしれない

if (result.Description == null || result.Description == "")

result.Description = "no description";

}

catch(InvalidOperationException e) // 引数はurlではないかもしれない

{

result.Title = "not url";

result.Description = "not url";

}

catch (UriFormatException e)

{

result.Title = "not url";

result.Description = "not url";

}

catch // 引数はurlだったとしてもエラーステータスコードが返されるかもしれない

{

result.Title = "request failed";

result.Description = "request failed";

}

if (url == "") // 引数が空文字列だった場合

{

result.Title = "blank";

result.Description = "blank";

}

return result;

}

Sheetにデータを書き込む

スプレッドシートの内容を書き込む処理を示します。request.ValueInputOptionを指定しないと例外が発生するので注意が必要です。ValueInputOptionをUSERENTEREDにすると関数の書き込みも可能になります。RAWだと指定された文字列がそのまま入ります。

public partial class Form1 : Form
{
    void SetData(SheetsService service, ValueRange body, string spreadsheetId, string range)
    {
        var request =
            service.Spreadsheets.Values.Update(body, spreadsheetId, range);
        request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
        request.Execute();
    }
}

public partial class Form1 : Form

{

void SetData(SheetsService service, ValueRange body, string spreadsheetId, string range)

{

var request =

service.Spreadsheets.Values.Update(body, spreadsheetId, range);

request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;

request.Execute();

}

完成させる

ボタンをクリックしたときの処理を示します。ここでは対象とするシートのIDと列の範囲を指定しなければなりません。アクセスしようとしているurlのスプレッドシートは https://docs.google.com/spreadsheets/d/XXXXXXX/edit#gid のようになっていますが、XXXXXXXの部分がIDになります。範囲は[シート名!A2:C]のように指定します。

[シート名!A2:C10]だとA列からC列の2行目から10列目までがデータの取得と書き込みの対象となります。A列が何列あるのかわからない場合は[シート名!A2:C]とすることでA列からC列の2行目から最後までが対象となります。

public partial class Form1 : Form
{
    private async void button1_Click(object sender, EventArgs e)
    {
        label1.Text = "処理中";

        // アクセスしようとしているのは以下のurlのスプレッドシート
        // https://docs.google.com/spreadsheets/d/1PCAzttMI2r_Cwy9w9iTwLAKY6xiNcpGMrxItCvnlmUY/edit#gid=1507031793
        // "C#でサイト情報を取得"というシートのA列からC列の2行目から最後までを取得する
        string spreadsheetId = "1PCAzttMI2r_Cwy9w9iTwLAKY6xiNcpGMrxItCvnlmUY";
        string range = "C#でサイト情報を取得!A2:C";

        SheetsService service = GetSheetsService();
        List<string[]> data =  GetData(service, spreadsheetId, range);

        label1.Text = "URLを取得しました";

        IList<IList<object>> values = new List<IList<object>>();
        foreach (string[] arr in data)
        {
            List<object> list = new List<object>(){ "", "blank", "blank", };
            if (arr.Length > 0)
            {
                Result result = await GetResult(arr[0]);
                list[0] = result.Url;
                list[1] = result.Title;
                list[2] = result.Description;
            }
            values.Add(list);
        }
        label1.Text = "結果を書き込みます";

        ValueRange body = new ValueRange() { Values = values };
        SetData(service, body, spreadsheetId, range);
        label1.Text = "完了";
    }
}

public partial class Form1 : Form

{

private async void button1_Click(object sender, EventArgs e)

{

label1.Text = "処理中";

// アクセスしようとしているのは以下のurlのスプレッドシート

// https://docs.google.com/spreadsheets/d/1PCAzttMI2r_Cwy9w9iTwLAKY6xiNcpGMrxItCvnlmUY/edit#gid=1507031793

// "C#でサイト情報を取得"というシートのA列からC列の2行目から最後までを取得する

string spreadsheetId = "1PCAzttMI2r_Cwy9w9iTwLAKY6xiNcpGMrxItCvnlmUY";

string range = "C#でサイト情報を取得!A2:C";

SheetsService service = GetSheetsService();

List<string[]> data = GetData(service, spreadsheetId, range);

label1.Text = "URLを取得しました";

IList<IList<object>> values = new List<IList<object>>();

foreach (string[] arr in data)

{

List<object> list = new List<object>(){ "", "blank", "blank", };

if (arr.Length > 0)

{

Result result = await GetResult(arr[0]);

list[0] = result.Url;

list[1] = result.Title;

list[2] = result.Description;

}

values.Add(list);

}

label1.Text = "結果を書き込みます";

ValueRange body = new ValueRange() { Values = values };

SetData(service, body, spreadsheetId, range);

label1.Text = "完了";

}

Google スプレッドシートに書かれているurlからタイトルとmeta descriptionを取得する

Google スプレッドシートを使った簡単スクレイピング

Google Sheets APIでスクレイピングの結果を書き込む

Google Cloudでプロジェクトを作成する

C#でアプリをつくる

SheetsServiceを取得する

Sheetからデータを取得する

URLからタイトルとdescriptionを取得する

Sheetにデータを書き込む

完成させる

鳩でも分かるC#管理人からのお願い

コメントについて

管理人のモチベーションアップのために

コメントをどうぞ