使用Google Data API使用C#访问Google Spreadsheets

使用C#通过Google Data API访问Google Spreadsheets

基础概念

Google Data API（现为Google Sheets API v4）是Google提供的一套RESTful接口，允许开发者以编程方式访问和操作Google Sheets电子表格数据。通过API可以实现读取、写入、更新和删除电子表格内容等功能。

准备工作

1. 创建Google Cloud项目并启用API

访问Google Cloud Console
创建新项目或选择现有项目
启用"Google Sheets API"
创建服务账号并下载JSON凭证文件

2. 安装必要的NuGet包

Install-Package Google.Apis.Sheets.v4
Install-Package Google.Apis.Auth

基本使用示例

1. 初始化服务

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

// 从JSON文件加载凭证
var credential = GoogleCredential.FromFile("credentials.json")
    .CreateScoped(SheetsService.Scope.Spreadsheets);

// 创建Sheets服务
var service = new SheetsService(new BaseClientService.Initializer()
{
    HttpClientInitializer = credential,
    ApplicationName = "Google Sheets API Demo"
});

2. 读取数据

// 电子表格ID和范围
string spreadsheetId = "your-spreadsheet-id";
string range = "Sheet1!A1:D10";

// 获取数据
var request = service.Spreadsheets.Values.Get(spreadsheetId, range);
var response = request.Execute();

// 处理数据
IList<IList<object>> values = response.Values;
if (values != null && values.Count > 0)
{
    foreach (var row in values)
    {
        Console.WriteLine(string.Join(", ", row));
    }
}
else
    Console.WriteLine("No data found.");

3. 写入数据

// 准备要写入的数据
var valueRange = new ValueRange();
valueRange.Values = new List<IList<object>> { new List<object> { "Hello", "World" } };

// 指定写入范围
string writeRange = "Sheet1!A1";

// 执行写入
var writeRequest = service.Spreadsheets.Values.Update(valueRange, spreadsheetId, writeRange);
writeRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var writeResponse = writeRequest.Execute();

Console.WriteLine($"Updated {writeResponse.UpdatedCells} cells.");

4. 批量更新

// 创建批量请求
var batchRequest = new BatchUpdateSpreadsheetRequest
{
    Requests = new List<Request>
    {
        new Request
        {
            UpdateCells = new UpdateCellsRequest
            {
                Start = new GridCoordinate
                {
                    SheetId = 0,
                    RowIndex = 0,
                    ColumnIndex = 0
                },
                Rows = new List<RowData>
                {
                    new RowData
                    {
                        Values = new List<CellData>
                        {
                            new CellData { UserEnteredValue = new ExtendedValue { StringValue = "Name" } },
                            new CellData { UserEnteredValue = new ExtendedValue { StringValue = "Age" } }
                        }
                    }
                },
                Fields = "userEnteredValue"
            }
        }
    }
};

// 执行批量请求
var batchResponse = service.Spreadsheets.BatchUpdate(batchRequest, spreadsheetId).Execute();