【C#】Google スプレッドシートのセル操作

忘れないよう備忘録です。

環境

Windows 11 Home 21H2
Visual Studio Community 2022
.NET 6.0
Google.Apis.Sheets.v4 1.57.0.2727

今回はトップレベルステートメントを使わないでやってみます。6.0 からの新機能！

チェックを外すとMainメソッドがなくなります。

NuGet

Google.Apis.Sheets.v4 1.57.0.2727

Google スプレッドシート

分かりやすいように、つぎのようなデータを準備しました。

後に出てくる「spreadsheetId」はURL部にあるのをコピペします。

Google Cloud Platform

Google スプレッドシートを操作するためには認証情報が必要です。今回はJSONファイルを .NETプロジェクトに読み込ませます。

プロジェクト作成

GCPコンソールを開いて新しいプロジェクトを作成します。

ライブラリ追加

「APIとサービス」>「ライブラリ」を選択し、次の2つのライブラリを有効化します。

Google Drive API

Google Sheets API

サービスアカウント作成

「APIとサービス」>「認証情報」を選択し、サービスアカウントを作成します。

「CREATE CREDENTIALS」>「サービスアカウント」を選択し、

適当なサービスアカウントIDを入力して、サービスアカウントを作成します。

認証情報作成

「認証情報」画面に戻ったら、今作ったサービスアカウントを選択し、

キータブの「新しい鍵を作成」で認証情報を作成します。

JSON型式で作成します。

.NETプロジェクトフォルダー内に、JSON型式の認証ファイルを保存します。

.NETプロジェクトの設定

認証ファイルを.NETプロジェクトで読み込むように設定します。

ソリューションエクスプローラーから、認証ファイルのプロパティを選択し、

プロパティの「出力ディレクトリにコピー」を「新しい場合はコピーする」に変更。

取得

単一セルの取得

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";
var range = "シート1!B2";

var request = sheetsService.Spreadsheets.Values.Get(spreadsheetId, range);
var response = request.Execute();
var values = response.Values;

foreach (var item in values)
{
    Console.WriteLine(string.Join(", ", item));
}

// 取得結果
// b2

範囲セルの取得

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";
var range = "シート1!B2:E4";

var request = sheetsService.Spreadsheets.Values.Get(spreadsheetId, range);
var response = request.Execute();
var values = response.Values;

foreach (var item in values)
{
    Console.WriteLine(string.Join(", ", item));
}

// 取得結果
// b2, c2, d2, e2
// b3, c3, d3, e3
// b4, c4, d4, e4

複数セルの同時取得 (ROWS)

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";

var request = sheetsService.Spreadsheets.Values.BatchGet(spreadsheetId);
request.Ranges = new Repeatable<string>(new[] { "シート1!B2", "シート1!B4:C4", "シート1!B6:D6" });
request.MajorDimension = SpreadsheetsResource.ValuesResource.BatchGetRequest.MajorDimensionEnum.ROWS;

var response = request.Execute();
var valueRanges = response.ValueRanges;

foreach (var valueRange in valueRanges)
{
    foreach (var item in valueRange.Values)
    {
        Console.WriteLine(string.Join(", ", item));
    }
}

// 取得結果
// b2
// b4, c4
// b6, c6, d6

複数セルの同時取得 (COLUMNS)

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";

var request = sheetsService.Spreadsheets.Values.BatchGet(spreadsheetId);
request.Ranges = new Repeatable<string>(new[] { "シート1!B2", "シート1!D2:D3", "シート1!F2:F4" });
request.MajorDimension = SpreadsheetsResource.ValuesResource.BatchGetRequest.MajorDimensionEnum.COLUMNS;

var response = request.Execute();
var valueRanges = response.ValueRanges;

foreach (var valueRange in valueRanges)
{
    foreach (var item in valueRange.Values)
    {
        Console.WriteLine(string.Join(", ", item));
    }
}

// 取得結果
// b2
// d2, d3
// f2, f3, f4

更新

単一セルの更新

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";

var body = new ValueRange() { Values = new List<IList<object>>() { new List<object>() { "new_b2" } } };
var request = sheetsService.Spreadsheets.Values.Update(body, spreadsheetId, "シート1!B2");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
request.Execute();

範囲セルの更新

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";

var body = new ValueRange();
body.Values = new List<IList<object>>()
{
    new List<object>() { "new_b2", "new_c2", "new_d2", "new_e2" },
    new List<object>() { "new_b3", "new_c3", "new_d3", "new_e3" },
    new List<object>() { "new_b4", "new_c4", "new_d4", "new_e4" },
};

var request = sheetsService.Spreadsheets.Values.Update(body, spreadsheetId, "シート1!B2:E4");
request.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
request.Execute();

複数セルの同時更新 (ROWS)

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";

var valueRanges = new List<ValueRange>();
valueRanges.Add(new ValueRange() { MajorDimension = "ROWS", Range = "シート1!B2", Values = new List<IList<object>>() { new List<object>() { "new_b2" } }, });
valueRanges.Add(new ValueRange() { MajorDimension = "ROWS", Range = "シート1!B4:C4", Values = new List<IList<object>>() { new List<object>() { "new_b4", "new_c4" } }, });
valueRanges.Add(new ValueRange() { MajorDimension = "ROWS", Range = "シート1!B6:D6", Values = new List<IList<object>>() { new List<object>() { "new_b6", "new_c6", "new_d6" } }, });

var body = new BatchUpdateValuesRequest() { Data = valueRanges, ValueInputOption = "USER_ENTERED" };
var request = sheetsService.Spreadsheets.Values.BatchUpdate(body, spreadsheetId);
request.Execute();

複数セルの同時更新 (COLUMNS)

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

using var fileStream = new FileStream("./disco-technique-358701-49da36e0a747.json", FileMode.Open, FileAccess.Read);
var googleCredential = GoogleCredential.FromStream(fileStream).CreateScoped(SheetsService.Scope.Spreadsheets);
var sheetsService = new SheetsService(new BaseClientService.Initializer() { HttpClientInitializer = googleCredential });

var spreadsheetId = "1xYoHhSbH9IO9fwmMZykAaez0h4l5zPaommA7mOYLtj4";

var valueRanges = new List<ValueRange>();
valueRanges.Add(new ValueRange() { MajorDimension = "COLUMNS", Range = "シート1!B2", Values = new List<IList<object>>() { new List<object>() { "new_b2" } }, });
valueRanges.Add(new ValueRange() { MajorDimension = "COLUMNS", Range = "シート1!D2:D3", Values = new List<IList<object>>() { new List<object>() { "new_d2", "new_d3" } }, });
valueRanges.Add(new ValueRange() { MajorDimension = "COLUMNS", Range = "シート1!F2:F4", Values = new List<IList<object>>() { new List<object>() { "new_f2", "new_f3", "new_f4" } }, });

var body = new BatchUpdateValuesRequest() { Data = valueRanges, ValueInputOption = "USER_ENTERED" };
var request = sheetsService.Spreadsheets.Values.BatchUpdate(body, spreadsheetId);
request.Execute();

おわりに

複数セルの同時取得や複数セルの同時更新で処理を作っておけば、単一セルでも範囲セルでも使えます。上位互換！

困った点

Google.GoogleApiException: 'The service sheets has thrown an exception. HttpStatusCode is Forbidden. The caller does not have permission'

Google スプレッドシートにアクセス権限が設定されていないためでした。

スプレッドシートのファイル > 共有 > 他のユーザーと共有

ユーザー追加でサービスアカウントのアドレスをコピペして、アクセス権限を編集者にして共有。