C#.NETでGoogle Sheets API 4を使ってSpreadSheetを読み書きしてみる

Google Sheets API 4をC#.NETで使う機会があったのでそっち関係のまとめ。

お題 : Google Sheets API 4/C#.NETでGoogle Drive上に作成されたSpreadsheetにデータを追記する

最初にハマったところを書く。

Sheets APIにはAppendがあって、これを使えば一発だろう、Google.Apis.Sheets.v4パッケージの資料にもAppend用のRequestがあるし。

と、思ってやってみたら実際のライブラリにはどうもこのリクエストが見つからない…。Appendしようと四苦八苦したあげく挫折。

なので方針転換、書き込む前にすでに書かれているデータを読み出して調べてその次の行を指定して書き込むようにした。

まず、コードから。

Program.cs

using Google.Apis.Auth.OAuth2;
using Google.Apis.Sheets.v4;
using Google.Apis.Sheets.v4.Data;
using Google.Apis.Services;
using Google.Apis.Util.Store;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace SheetsQuickstart
{
	class Program
	{
		//****************************************************************
		//Scopeで読み取りのみ、読み書きなど指定する。
		//scopeを変更したら、credentialsしなおすため、
		// ~/.credentials/sheets.googleapis.com-dotnet-quickstart.json
		//をいったん削除して再度認証プロセスを通すこと
		//****************************************************************
		static string[] Scopes = { SheetsService.Scope.Spreadsheets };
		static string ApplicationName = "Google Sheets API .NET Quickstart";
		static string spreadsheetId = "(spreadsheetIdをいれる)";
		static string sheetName = "(Worksheet名をいれる)";

		//****************************************************************
		//Main - 10秒毎に追加する。終了するときはCtl-Cで。
		//****************************************************************
		static void Main(string[] args){
			var service = OpenSheet();
			while (true) {
				ReadWrite(service);
				System.Threading.Thread.Sleep(10000);
			}
		}

		//****************************************************************
		//OpenSheet() - 認証プロセスとAPI serviceの作成
		//****************************************************************
		static SheetsService OpenSheet(){
			UserCredential credential;
			//認証プロセス。credPathが作成されていないとBrowserが起動して認証ページが開くので認証を行って先に進む
			using(var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read)){
				string credPath	= Path.Combine
					(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal),
					 ".credentials/sheets.googleapis.com-dotnet-quickstart.json");
				//CredentialファイルがcredPathに保存される
				credential = GoogleWebAuthorizationBroker.AuthorizeAsync
					(GoogleClientSecrets.Load(stream).Secrets, Scopes, "user", CancellationToken.None,
					 new FileDataStore(credPath, true)).Result;
			}
			//API serviceを作成、Requestパラメータを設定
			var service = new SheetsService(new BaseClientService.Initializer(){
					HttpClientInitializer = credential,
					ApplicationName = ApplicationName,
				});
			return service;
		}

		//****************************************************************
		//OpenSheet() - 本当はAppendしたいんだが動かない(というかエントリがない)ので、
		// 1. 今あるデータを全部読み出す
		// 2. 行数を調べてその次の行にデータを書く
		//****************************************************************
		static void ReadWrite(SheetsService service){
			ValueRange rVR;
			String wRange;
			//データを読み出す
			int rowNumber = 1;
			wRange = String.Format("{0}!A{1}:B", sheetName, rowNumber);	//行を全部読む
			SpreadsheetsResource.ValuesResource.GetRequest getRequest
				=	service.Spreadsheets.Values.Get(spreadsheetId, wRange);
			rVR = getRequest.Execute();
			IList<IList<Object>> values = rVR.Values;
			if(values != null && values.Count > 0) rowNumber = values.Count + 1;
			//空行に新たにデータを書き込む
			wRange = String.Format("{0}!A{1}:B{1}", sheetName, rowNumber);	//行を追加
			ValueRange valueRange = new ValueRange();
			valueRange.Range = wRange;
			valueRange.MajorDimension = "ROWS";
			DateTime dt = new DateTime();
			dt = DateTime.Now;
			string dts = dt.ToString("HH:mm:ss");
			var oblist = new List<object>(){ String.Format("{0}", rowNumber), dts };
			valueRange.Values = new List<IList<object>>{ oblist };
			SpreadsheetsResource.ValuesResource.UpdateRequest updateRequest
				= service.Spreadsheets.Values.Update(valueRange, spreadsheetId, wRange);
			updateRequest.ValueInputOption
				= SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
			UpdateValuesResponse uUVR = updateRequest.Execute();
		}
		
	}
}

Googleのページのサンプルを多少書き換えた。

やってることはなんてことなく、

1. あらかじめ作成しておいたOAuth2.0クライアントIDのJSONファイルで認証、Credentialファイルを得る(OpenSheet())
2. CredentialファイルとApplicationNameを指定してSheetsServiceを作成(OpenSheet())
3. 書き込むSpreadsheetのIDとWorksheet名を指定して行を全部読んで追加する行を得る(ReadWrite())
4. 追加行にデータを書き込む(ReadWrite())

というわけで、これを動かす前に必要なのは、

1. GoogleのデベロッパコンソールでOAuth2.0クライアントIDのJSONファイルを作成
2. VS2015でプロジェクトを作成してNuGetパッケージ、Google.Apis.Sheets.v4をインストール
3. Spreadsheetを作成して、SpreadsheetIDとWorksheet名をメモ

の三作業。

Google Sheets APIの".NET Quickstart"のページ通りでできる。

GoogleのデベロッパコンソールでOAuth2.0クライアントIDのJSONファイルを作成

デベロッパコンソールのウィザードにアクセスして以下の作業。

1. 「プロジェクトを作成」または既存のプロジェクトを選択して「続行」をクリック
2. 「APIが有効化されました」と表示されたら「認証情報に進む」をクリック
3. 「プロジェクトへの認証情報の追加」画面、「この手順をスキップし、API キー、クライアント ID、サービス アカウントを作成できます」とある行の「クライアントID」をクリック
   • 「OAuth クライアント ID を作成するには、まず同意画面でサービス名を設定する必要があります」と表示されたら「同意画面を設定」をクリックして作成保存すると、認証情報画面に戻る
4. アプリケーションの種類を選択して「作成」をクリック
5. 「OAuthクライアント」が表示されるので「OK」をクリック
6. 「認証情報」画面の「OAuth2.0クライアントID」にエントリができるので行右端のダウンロードボタンをクリックして端末にセーブ

プログラムをビルドしたらこのファイルを"client_secret.json"にリネームして実行ディレクトリに置く。

VS2015でプロジェクトを作成してNuGetパッケージ、Google.Apis.Sheets.v4をインストール

VS2015でVisual C#のコンソールアプリケーションのプロジェクトを作成、プロジェクト上で以下の作業。

1. メニューのツール→NuGetパッケージマネージャー→パッケージマネージャーコンソールで、パッケージマネージャーコンソールを開く
2. Install-Package Google.Apis.Sheets.v4を入力してパッケージを導入する
3. Program.csの必要なusing句を追加する

追加するのは、

using

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

Spreadsheetを作成して、SpreadsheetIDとWorksheet名をメモ

Google DriveにアクセスしてSpreadsheetを作成する。
SpreadsheetIDはURLのhttps://docs.google.com/spreadsheets/d/の次の文字列。
Worksheet名は適宜変更してプログラムに渡せるようにする。

サンプルでは、変数 spreadsheetIdとsheetNameに埋め込んでいる。

ビルドと実行

ビルドしたらプログラムの実行ディレクトリにclient_secret.jsonにリネームしたクライアントIDファイルを置く。
実行すると初回はWebブラウザが起動してアクセス許可を促すので許可する。

だいたいこんな感じ。

しかしAppendもできないんでは、次の機会があれば、違う処理系でやるだろうなあ。