【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 スプレッドシート にアクセス権限が設定されていないためでした。

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

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