オウルです。
皆さんは、Gmail、Googleドライブ使ってますか？
僕は便利なのでガンガン使ってます（所属会社ではGoogle Workspace）。今回は、そんなGoogleサービスの１つ、Googleフォームとスプレッドシートについてです。まぁタイトル通りです。
.NET Core3.1 コンソールアプリからGoogleフォームの回答の保存先スプレッドシートの値を取得、削除します。
| language | .NET Core3.1 | C# |
| editor | VSCode |
Googleフォームとスプレッドシート
Googleフォームやスプレッドシートの概要については、ここでは省略します。
Googleフォームの回答の保存先をスプレッドシートに設定した場合、まるでGoogleフォームの回答がスプレッドシートで管理されているように見えがちですが、実はGoogleフォームが保持している回答をスプレッドシートにコピー表示しているだけなのです。その辺は、最後に少し補足しています。
また、自前でNoSQLなどを用意してGoogleフォームの内容を永続化するのも１つの方法ですが、一般的にはGoogle Apps Script APIのFormResponseあたりを使用して他のサービスと連携するケースが多いと思われます。
GCPプロジェクトを作成してAPIを有効化
では、GCPプロジェクトを作成してAPIを有効化することから始めます。.NET quickstartにあるように、Google Cloud ConsoleからGCPにプロジェクトを作成しましょう。Create a project and enable the APIを参考に進めてください。
プロジェクト作成後は、下記の画面（クラウドのWebコンソールのUIは、よく更新されるので変わっている可能性があります）からOAuth ウェブ クライアント ID を作成してJSONファイルをダウンロードします。
Sheets API
.NET版のGoogle Sheets v4クライアントライブラリを使用してGoogle Sheets APIリクエストします。ここからはコードベースです。
行取得
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
//scope: https://googleapis.dev/dotnet/Google.Apis.Sheets.v4/latest/api/Google.Apis.Sheets.v4.SheetsService.Scope.html private readonly string[] _scopes = { SheetsService.Scope.Spreadsheets }; private readonly string _appName = "Google Sheets API .NET Console App"; private SheetsService _sheetsService; /// <summary> /// 指定範囲のスプレッドシートの値を取得 /// <see href="https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get" /> /// </summary> /// <param name="credentialsJsonFile">ダウンロードしたOAuthクライアントIDのJSON</param> /// <param name="spreadsheetId">スプレッドシートID</param> /// <param name="range">範囲 ex)sheet_name!A2:AF6</param> /// <param name="cancellationToken">キャンセルトークン</param> /// <returns></returns> public IList<IList<object>> Get(FileInfo credentialsJsonFile, string spreadsheetId, string range, CancellationToken cancellationToken) { if (_sheetsService == null) { CreateSheetsService(credentialsJsonFile, cancellationToken); } // FORMATTED_VALUE(default), UNFORMATTED_VALUE, FORMULA // https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum valueRenderOption = SpreadsheetsResource.ValuesResource.GetRequest.ValueRenderOptionEnum.FORMATTEDVALUE; // SERIAL_NUMBER(default), FORMATTED_VALUE // https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum dateTimeRenderOption = SpreadsheetsResource.ValuesResource.GetRequest.DateTimeRenderOptionEnum.FORMATTEDSTRING; SpreadsheetsResource.ValuesResource.GetRequest request = _sheetsService.Spreadsheets.Values.Get(spreadsheetId, range); request.ValueRenderOption = valueRenderOption; request.DateTimeRenderOption = dateTimeRenderOption; // To execute asynchronously in an async method, replace `request.Execute()` as shown: Google.Apis.Sheets.v4.Data.ValueRange response = request.Execute(); return response.Values; } public void CreateSheetsService(FileInfo credentialsJsonFile, CancellationToken cancellationToken) { UserCredential credential; // The file token.json stores the user's access and refresh tokens, and is created // automatically when the authorization flow completes for the first time. // ↑にあるように1回は認証する必要があります。但し作成したトークンを削除した場合は再度認証が必要。 using (var stream = new FileStream(credentialsJsonFile.FullName, FileMode.Open, FileAccess.Read)) { var current = Path.GetDirectoryName(Assembly.GetEntryAssembly().Location); string credPath = Path.Combine(current, "token.json"); credential = GoogleWebAuthorizationBroker.AuthorizeAsync( GoogleClientSecrets.Load(stream).Secrets, _scopes, "user", cancellationToken, new FileDataStore(credPath, true)).Result; } _sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = credential, ApplicationName = _appName }); } |
行削除
行削除は、Delete a rowにあるように、 spreadsheet.batchUpdateで行います。ただ、僕が調べる限り残念ながらサンプルコードはなかったので、spreadsheets.batchUpdateのサンプルとGoogle.Apis.Sheets.v4を参考に実装していきます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
/// <summary> /// 指定範囲のスプレッドシートの行を削除 /// </summary> /// <param name="credentialsJsonFile">ダウンロードしたOAuthクライアントIDのJSON</param> /// <param name="spreadsheetId">スプレッドシートID</param> /// <param name="sheetId">シートID</param> /// <param name="startIndex">範囲: 開始行</param> /// <param name="outIndex">範囲: 終了行</param> /// <param name="cancellationToken">キャンセルトークン</param> public void Delete(FileInfo credentialsJsonFile, string spreadsheetId, int sheetId, int startIndex, int outIndex, CancellationToken cancellationToken) { if (_sheetsService == null) { CreateSheetsService(credentialsJsonFile, cancellationToken); } Request requestBody = new Request() { DeleteDimension = new DeleteDimensionRequest() { // DimensionRangeの説明にあるが、例えば2行目を削除する場合は、 // StartIndex: 2 // EndIndex: 3 // となる Range = new DimensionRange() { SheetId = sheetId, Dimension = "ROWS", StartIndex = startIndex, EndIndex = outIndex } } }; var batchRequest = new BatchUpdateSpreadsheetRequest() { Requests = new List<Request>(1) { requestBody } }; SpreadsheetsResource.BatchUpdateRequest request = _sheetsService.Spreadsheets.BatchUpdate(batchRequest, spreadsheetId); Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetResponse response = request.Execute(); // Data.BatchUpdateValuesResponse response = await request.ExecuteAsync(); } |
Googleフォーム回答の考察
行削除について少し補足しておきます。フォームの回答の保存先スプレッドシートの行を削除してもフォームの回答自体を削除したわけではありません。試しに、行を削除した後にフォームの回答の保存先を選択するにある、「フォームからスプレッドシートのリンクを解除する」を操作後、再度フォームの回答の保存先を選択してみましょう。フォームの回答済みの内容が新たにスプレッドシートにコピーされるはずです。
Googleフォームの回答を削除するには、Google Apps ScriptのdeleteResponseになります。
フォームの回答のスプレッドシートへのコピー行の規則
Googleフォームの回答の保存先スプレッドシートを利用したアプリを考えるときに、とても気になる内容です。ですが、この規則についてのドキュメントは残念ながら見つけることができませんでした。なので、これは自分で確かめる他ないように思われます。僕が試した限りだと、1, 2, 3, …, n – 1, n と行がある場合、次は、n + 1 です。但し、手動で行を追加した場合は含まれません。つまり n = 5 の場合、手動で追加して今 n = 6 とすると、次に自動でコピーされるのは、n = 7 ではなく、n = 6 です。手動で追加した行が、n = 7 となります。
では、行クリアのときは？、行削除のときは？気になるところです。繰り返しになりますが、ドキュメントで見つけることができなかったので、このあたりは、せっかくですし、遊びながらご自分で確認してみるのもおもしろいかと思います。
