C#からGoogleスプレッドシートを操作してみる

はじめに

本文書はC#からGoogleスプレッドシートを操作するための手順をまとめることを目的としています。

Googleスプレッドシートを操作してみる

やりたいこと

以下のようなウェブページの閲覧ログデータにおいて、ページごとのユニークユーザー数を算出するプログラムをC#で作成していきます。

以降で必要な手順を記載していきます。

C#コードからGoogleスプレッドシートにアクセスするための準備

GoogleスプレッドシートにアクセスするためにはGoogleが提供するAPIを利用する必要があります。
ただ、このAPIアクセスを有効にするために事前にいくつが設定が必要です。
事前準備の詳細はこちらを参考にしてください。
以下では、実際に筆者が実施した手順を記載しています。

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

まずはこちらからGoogle Cloud上にプロジェクトを作成します。
APIを利用するためにはプロジェクトを作成し、必要なAPIへのアクセスを有効にする必要があるため、この作業が必要になります。
以下の赤枠内でプロジェクト名を指定して、プロジェクトを作成します。

Googleスプレッドシート用のAPIアクセスを有効にする

プロジェクト作成後は画面左上のナビゲーションメニューから[APIとサービス]->[有効なAPIとサービス]をクリックします。

[APIとサービスの有効化]をクリックし、APIライブラリの検索画面を表示します。

APIの検索テキストボックスに「Google Sheets」と入力し、検索し、「Google Sheets API」をクリックします。

「有効にする」をクリックします。

画面左上のナビゲーションメニューから[APIとサービス]->[有効なAPIとサービス]し、API一覧から「Google Sheets API」を選択します。

[認証情報]タブをクリックし、[認証情報を作成]->[サービスアカウント]を選択します。

[サービスアカウント名]、[サービスアカウントID]、[サービスアカウントの説明]を入力し、[作成して続行]をクリックします。

ロールを「参照者」を設定して[続行]をクリックし、その後[完了]をクリックします。

サービスアカウント一覧で作成したサービスアカウントをクリックします。

[キー]タブをクリックし、[鍵を追加]->[新しい鍵を作成]をクリックします。

キーのタイプを「JSON」にして、[作成]をクリックします。
ローカルにjson形式のキーが保存されます。(このファイル内のキーは後々利用します)

操作したいスプレッドシートに権限を付与する

APIアクセスを有効にしたので、次に操作したいスプレッドシートにAPI経由でアクセスできる権限を設定します。
対象のスプレッドシートをWebブラウザで開き、[共有]をクリックします。

作成したサービスアカウントのメールアドレスを「ユーザーやグループを追加」テキストボックスに入力し、ロールを編集者にして「送信」をクリックします。

C#でプログラムを作成する

必要なNuGetパッケージのインストール

C#でスプレッドシートを操作するためにはNuGetパッケージ「Google.Apis.Sheets.v4」を利用します。
VisualStudioでプロジェクト作成後に作成したプロジェクトをソリューションエクスプローラー上で右クリックし、[NuGetパッケージの管理]メニューを実行します。
表示されたNuGetパッケージマネージャーウィンドウから「Google.Apis.Sheets.v4」をインストールします。

プログラム作成

ようやく準備ができたので、以下のようなウェブページの閲覧ログデータにおいて、ページごとのユニークユーザー数を算出するためのプログラムを作成していきます。

以下がコードです。

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

// [Googleスプレッドシート用のAPIアクセスを有効にする]の章でエクスポートしたJSON形式のAPIキーを使って認証を実行する
// 上記のJSONファイルを開き、スプレッドシートにアクセスするための認証情報を作成
var fileStream = new FileStream("エクスポートしたJSON形式のAPIキーへのファイルパス", FileMode.Open, FileAccess.Read);
var credential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.SpreadsheetsReadonly);
// 上記の認証情報を使って、スプレッドシートにアクセスするためのサービスを作成
var service = new SheetsService(new BaseClientService.Initializer(){HttpClientInitializer = credential});

// 対象とするシートのID
var sheetId = "対象とするシートのID";

// ページ名列の範囲
var pageNameColumnRange = "対象のシート名!B2:B21";

// ユーザーID列の範囲
var userIdColumnRange = "対象のシート名!C2:C21";

// ページ名一覧を取得
var pageNameValues = service.Spreadsheets.Values.Get(sheetId, pageNameColumnRange).Execute().Values;
var pageNames = pageNameValues.Select(pageNameValue => pageNameValue.First().ToString());

// ユーザーID一覧を取得
var userIdValues = service.Spreadsheets.Values.Get(sheetId, userIdColumnRange).Execute().Values;
var userIds = userIdValues.Select(userIdValue => userIdValue.First().ToString());

// ページごとのユニークID一覧を記録するディクショナリ
var uniqueUserIdsPerPage = new Dictionary<string, HashSet<string>>();

// ページごとのユニークIDを取得するためにページ名一覧とユーザー名一覧を1つのEnumerableにまとめる
var pageNameAndUserIds = pageNames.Zip(userIds, (pageName, userId) => new { pageName, userId });

// ページ名とユーザーIDの組み合わせ一覧に対して反復処理をして、ページ名とユニークIDを集計する
foreach (var pageNameAndUserId in pageNameAndUserIds)
{
	if (!uniqueUserIdsPerPage.ContainsKey(pageNameAndUserId.pageName))
	{
		// 該当のページに対応するユーザーIDが一件も追加されていない場合はユーザーIDリストを初期化
		uniqueUserIdsPerPage.Add(pageNameAndUserId.pageName, new HashSet<string>() { pageNameAndUserId.userId });
	}
	else
	{
		// 該当のページに対応するユーザーIDを追加
		// HashSetへの追加なので重複追加はされない
		uniqueUserIdsPerPage[pageNameAndUserId.pageName].Add(pageNameAndUserId.userId);
	}
}

// ページごとのユニークユーザー数を出力
foreach (var (pageName, users) in uniqueUserIdsPerPage)
{
	Console.WriteLine($"{pageName}:{users.Count}");
}

コードの説明をしていきます。
[Googleスプレッドシート用のAPIアクセスを有効にする]の章でエクスポートしたJSON形式のAPIキー(JSONファイル)を開き、そのファイルを使ってスプレッドシートにアクセスするための認証情報を作成します。

// [Googleスプレッドシート用のAPIアクセスを有効にする]の章でエクスポートしたJSON形式のAPIキーを使って認証を実行する
// 上記のJSONファイルを開き、スプレッドシートにアクセスするための認証情報を作成
var fileStream = new FileStream("エクスポートしたJSON形式のAPIキーへのファイルパス", FileMode.Open, FileAccess.Read);
var credential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.SpreadsheetsReadonly);

上記の認証情報を利用してスプレッドシートにアクセスするためのサービスを作成します。

// 上記の認証情報を使って、スプレッドシートにアクセスするためのサービスを作成
var service = new SheetsService(new BaseClientService.Initializer(){HttpClientInitializer = credential});

以下のスプレッドシートのIDは、スプレッドシートをWebブラウザで表示した際のURLの以下の赤枠内の部分の値を記入します。

// 対象とするシートのID
var sheetId = "対象とするシートのID";

スプレッドシート内のシート名とセルの範囲を文字列で指定して、作成済みのサービスオブジェクト経由でページ名とユーザーID一覧を取得します。

// ページ名列の範囲
var pageNameColumnRange = "対象のシート名!B2:B21";

// ユーザーID列の範囲
var userIdColumnRange = "対象のシート名!C2:C21";

// ページ名一覧を取得
var pageNameValues = service.Spreadsheets.Values.Get(sheetId, pageNameColumnRange).Execute().Values;
var pageNames = pageNameValues.Select(pageNameValue => pageNameValue.First().ToString());

// ユーザーID一覧を取得
var userIdValues = service.Spreadsheets.Values.Get(sheetId, userIdColumnRange).Execute().Values;
var userIds = userIdValues.Select(userIdValue => userIdValue.First().ToString());

以降の部分では、上記までで取得したページ名一覧とユーザーID一覧を使ってページ名とユーザーIDを組み合わせて反復処理します。(スプレッドシートの各行ごとに処理をするイメージです。)
ページごとのユーザーIDの一覧をユーザーIDが重複しないように作成することで、最終的にページ名ごとのユニークなユーザー数(重複しないユーザーIDの数)を出力しています。

// ページごとのユニークID一覧を記録するディクショナリ
var uniqueUserIdsPerPage = new Dictionary<string, HashSet<string>>();

// ページごとのユニークIDを取得するためにページ名一覧とユーザー名一覧を1つのEnumerableにまとめる
var pageNameAndUserIds = pageNames.Zip(userIds, (pageName, userId) => new { pageName, userId });

// ページ名とユーザーIDの組み合わせ一覧に対して反復処理をして、ページ名とユニークIDを集計する
foreach (var pageNameAndUserId in pageNameAndUserIds)
{
	if (!uniqueUserIdsPerPage.ContainsKey(pageNameAndUserId.pageName))
	{
		// 該当のページに対応するユーザーIDが一件も追加されていない場合はユーザーIDリストを初期化
		uniqueUserIdsPerPage.Add(pageNameAndUserId.pageName, new HashSet<string>() { pageNameAndUserId.userId });
	}
	else
	{
		// 該当のページに対応するユーザーIDを追加
		// HashSetへの追加なので重複追加はされない
		uniqueUserIdsPerPage[pageNameAndUserId.pageName].Add(pageNameAndUserId.userId);
	}
}

// ページごとのユニークユーザー数を出力
foreach (var (pageName, users) in uniqueUserIdsPerPage)
{
	Console.WriteLine($"{pageName}:{users.Count}");
}

まとめ

本記事ではC#からGoogleスプレッドシートを操作するための手順をまとめました。
事前準備は必要ですが、それさえしてしまえば筆者が以前記事にしたCSVデータをプログラムで操作するときと大差ないプログラムで操作ができるため、ぜひ活用したいと思いました。(以前の記事はこちら。)