使用Java与Google Sheets交互

1. 概述

Google Sheets 提供了一种便捷的方式来存储和操作电子表格，并与他人协作编辑文档。

有时，从应用程序访问这些文档会很有用，例如执行自动化操作。为此，Google 提供了 Google Sheets API 供开发者使用。

本文将介绍如何连接该 API 并对 Google Sheets 执行操作。

2. Maven 依赖

要连接 API 并操作文档，我们需要添加以下依赖：

<dependency>
    <groupId>com.google.api-client</groupId>
    <artifactId>google-api-client</artifactId>
    <version>1.23.0</version>
</dependency>
<dependency>
    <groupId>com.google.oauth-client</groupId>
    <artifactId>google-oauth-client-jetty</artifactId>
    <version>1.23.0</version>
</dependency>
<dependency>
    <groupId>com.google.apis</groupId>
    <artifactId>google-api-services-sheets</artifactId>
    <version>v4-rev493-1.23.0</version>
</dependency>

3. 授权

Google Sheets API 在通过应用程序访问前需要 OAuth 2.0 授权。

首先需要获取一组 OAuth 凭据，然后在应用程序中使用它提交授权请求。

3.1. 获取 OAuth 2.0 凭据

获取凭据需要在 Google Developers Console 创建项目并启用 Google Sheets API。Google Quickstart 指南包含详细步骤。

下载包含凭据信息的 JSON 文件后，将其内容复制到应用程序的 src/main/resources 目录下的 google-sheets-client-secret.json 文件中：

{
  "installed":
    {
      "client_id":"123456789.apps.googleusercontent.com",
      "project_id":"decisive-octane-187810",
      "auth_uri":"https://accounts.google.com/o/oauth2/auth",
      "token_uri":"https://accounts.google.com/o/oauth2/token",
      "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
      "client_secret":"GOCSPX-abc123def456",
      "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
    }
}

**3.2. 获取 Credential 对象**

成功授权后返回的 Credential 对象可用于与 Google Sheets API 交互。

创建 GoogleAuthorizeUtil 类，包含静态 authorize() 方法读取 JSON 文件并构建 GoogleClientSecrets 对象：

public class GoogleAuthorizeUtil {
    public static Credential authorize() throws IOException, GeneralSecurityException {
        
        // 从 JSON 文件构建 GoogleClientSecrets
        List<String> scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);

        // 构建 Credential 对象
        return credential;
    }
}

示例中设置 SPREADSHEETS 范围以访问 Google Sheets，并使用内存 DataStoreFactory 存储凭据。完整代码见 GitHub 项目。

**4. 构建 Sheets 服务实例**

与 Google Sheets 交互需要 Sheets 对象作为 API 读写客户端。

创建 SheetsServiceUtil 类，使用 Credential 对象获取 Sheets 实例：

public class SheetsServiceUtil {
    private static final String APPLICATION_NAME = "Google Sheets Example";

    public static Sheets getSheetsService() throws IOException, GeneralSecurityException {
        Credential credential = GoogleAuthorizeUtil.authorize();
        return new Sheets.Builder(
          GoogleNetHttpTransport.newTrustedTransport(), 
          JacksonFactory.getDefaultInstance(), credential)
          .setApplicationName(APPLICATION_NAME)
          .build();
    }
}

接下来介绍常见操作。

5. 向工作表写入数据

操作现有电子表格需要其 ID（从 URL 获取）。示例使用公开电子表格 "Expenses"：

https://docs.google.com/spreadsheets/d/1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI/edit#gid=0

ID 为 1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI。读写值使用 spreadsheets.values 集合，值表示为 ValueRange 对象（对应工作表的行列）。

创建测试类初始化服务：

public class GoogleSheetsLiveTest {
    private static Sheets sheetsService;
    private static String SPREADSHEET_ID = "1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI";

    @BeforeClass
    public static void setup() throws GeneralSecurityException, IOException {
        sheetsService = SheetsServiceUtil.getSheetsService();
    }
}

写入方式包括：

写入单个范围
写入多个范围
在表格后追加数据

5.1. 写入单个范围

使用 spreadsheets().values().update() 方法：

@Test
public void whenWriteSheet_thenReadSheetOk() throws IOException {
    ValueRange body = new ValueRange()
      .setValues(Arrays.asList(
        Arrays.asList("Expenses January"), 
        Arrays.asList("books", "30"), 
        Arrays.asList("pens", "10"),
        Arrays.asList("Expenses February"), 
        Arrays.asList("clothes", "20"),
        Arrays.asList("shoes", "5")));
    UpdateValuesResponse result = sheetsService.spreadsheets().values()
      .update(SPREADSHEET_ID, "A1", body)
      .setValueInputOption("RAW")
      .execute();
}

创建 ValueRange 对象后，通过 update() 方法写入指定电子表格的 "A1" 单元格起始位置。execute() 发送请求。若需按列写入，使用 setMajorDimension("COLUMNS")。

⚠️ 执行测试时会打开浏览器要求用户登录授权：

✅ 登录用户需有电子表格的查看/编辑权限，否则返回 403 错误。示例电子表格设为公开编辑。

5.2. 写入多个范围

使用 BatchUpdateValuesRequest 批量更新多个范围：

List<ValueRange> data = new ArrayList<>();
data.add(new ValueRange()
  .setRange("D1")
  .setValues(Arrays.asList(
    Arrays.asList("January Total", "=B2+B3"))));
data.add(new ValueRange()
  .setRange("D4")
  .setValues(Arrays.asList(
    Arrays.asList("February Total", "=B5+B6"))));

BatchUpdateValuesRequest batchBody = new BatchUpdateValuesRequest()
  .setValueInputOption("USER_ENTERED")
  .setData(data);

BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets().values()
  .batchUpdate(SPREADSHEET_ID, batchBody)
  .execute();

USER_ENTERED 选项表示值将根据公式计算（如 =B2+B3）。执行后更新 "D1:E1" 和 "D4:E4" 范围。

5.3. 追加数据到表格末尾

使用 append() 方法在表格后追加数据：

ValueRange appendBody = new ValueRange()
  .setValues(Arrays.asList(
    Arrays.asList("Total", "=E1+E4")));
AppendValuesResponse appendResult = sheetsService.spreadsheets().values()
  .append(SPREADSHEET_ID, "A1", appendBody)
  .setValueInputOption("USER_ENTERED")
  .setInsertDataOption("INSERT_ROWS")
  .setIncludeValuesInResponse(true)
  .execute();
        
ValueRange total = appendResult.getUpdates().getUpdatedData();
assertThat(total.getValues().get(0).get(1)).isEqualTo("65");

INSERT_ROWS 表示插入新行而非替换数据。首次运行写入 "A7:B7"，后续运行追加到新行。设置 includeValuesInResponse(true) 可在响应中获取更新数据。

6. 从工作表读取数据

使用 spreadsheets().values().get() 读取单个范围，或 batchGet() 读取多个范围：

List<String> ranges = Arrays.asList("E1","E4");
BatchGetValuesResponse readResult = sheetsService.spreadsheets().values()
  .batchGet(SPREADSHEET_ID)
  .setRanges(ranges)
  .execute();
        
ValueRange januaryTotal = readResult.getValueRanges().get(0);
assertThat(januaryTotal.getValues().get(0).get(0))
  .isEqualTo("40");

ValueRange febTotal = readResult.getValueRanges().get(1);
assertThat(febTotal.getValues().get(0).get(0))
  .isEqualTo("25");

读取 "E1" 和 "E4" 范围并验证月度总计值。

7. 创建新电子表格

使用 spreadsheets() 和 spreadsheets().sheets() 集合操作工作表或整个电子表格。创建新电子表示例：

@Test
public void test() throws IOException {
    Spreadsheet spreadSheet = new Spreadsheet().setProperties(
      new SpreadsheetProperties().setTitle("My Spreadsheet"));
    Spreadsheet result = sheetsService
      .spreadsheets()
      .create(spreadSheet).execute();
        
    assertThat(result.getSpreadsheetId()).isNotNull();   
}

创建标题为 "My Spreadsheet" 的电子表格，新表格为私有并存储在登录用户的 Drive 中。

8. 其他更新操作

大多数操作采用 Request 对象形式，添加到列表后构建 BatchUpdateSpreadsheetRequest。示例：修改电子表格标题并复制单元格：

@Test
public void whenUpdateSpreadSheetTitle_thenOk() throws IOException {
    UpdateSpreadsheetPropertiesRequest updateSpreadSheetRequest 
      = new UpdateSpreadsheetPropertiesRequest().setFields("*")
      .setProperties(new SpreadsheetProperties().setTitle("Expenses"));
                
    CopyPasteRequest copyRequest = new CopyPasteRequest()
      .setSource(new GridRange().setSheetId(0)
        .setStartColumnIndex(0).setEndColumnIndex(2)
        .setStartRowIndex(0).setEndRowIndex(1))
      .setDestination(new GridRange().setSheetId(1)
        .setStartColumnIndex(0).setEndColumnIndex(2)
        .setStartRowIndex(0).setEndRowIndex(1))
      .setPasteType("PASTE_VALUES");
        
    List<Request> requests = new ArrayList<>();
    requests.add(new Request()
      .setCopyPaste(copyRequest));
    requests.add(new Request()
      .setUpdateSpreadsheetProperties(updateSpreadSheetRequest));
        
    BatchUpdateSpreadsheetRequest body 
      = new BatchUpdateSpreadsheetRequest().setRequests(requests);
        
    sheetsService.spreadsheets().batchUpdate(SPREADSHEET_ID, body).execute();
}

创建 UpdateSpreadSheetPropertiesRequest 修改标题，CopyPasteRequest 指定源/目标范围，添加到 Request 列表后批量执行。其他可用请求包括：

AddSheetRequest 创建新工作表
FindReplaceRequest 替换值
修改边框、添加筛选器、合并单元格等

完整请求类型列表见官方文档。

9. 总结

本文介绍了如何从 Java 应用连接 Google Sheets API，并演示了操作 Google Sheets 文档的示例。完整代码见 GitHub 项目。

Persistence

REST

Security