Interact with Google Sheets from Java – 从Java中与谷歌表互动

1. Overview

1.概述

Google Sheets provides a convenient way to store and manipulate spreadsheets and collaborate with others on a document.

谷歌表格提供了一种方便的方式来存储和处理电子表格，并与他人协作处理文件。

Sometimes, it can be useful to access these documents from an application, say to perform an automated operation. For this purpose, Google provides the Google Sheets API that developers can interact with.

有时，从一个应用程序中访问这些文件会很有用，比如说执行一个自动化操作。为此，谷歌提供了谷歌表格API，开发者可以与之互动。

In this article, we’re going to take a look at how we can connect to the API and perform operations on Google Sheets.

在这篇文章中，我们将看看我们如何连接到API并在Google表单上执行操作。

2. Maven Dependencies

2.Maven的依赖性

To connect to the API and manipulate documents, we’ll need to add the google-api-client, google-oauth-client-jetty and google-api-services-sheets dependencies:

为了连接到API并操作文档，我们需要添加google-api-client、google-oauth-client-jetty和google-api-services-sheets依赖项。

<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. Authorization

3.授权

The Google Sheets API requires OAuth 2.0 authorization before we can access it through an application.

Google Sheets API需要OAuth 2.0授权，然后我们才能通过应用程序访问它。

First, we need to obtain a set of OAuth credentials, then use this in our application to submit a request for authorization.

首先，我们需要获得一组OAuth凭证，然后在我们的应用程序中使用它来提交授权请求。

3.1. Obtaining OAuth 2.0 Credentials

3.1.获取OAuth 2.0凭证

To obtain the credentials, we’ll need to create a project in the Google Developers Console and then enable the Google Sheets API for the project. The first step in the Google Quickstart guide contains detailed information on how to do this.

要获得证书，我们需要在Google 开发人员控制台中创建一个项目，然后为该项目启用 Google Sheets API。Google 快速入门指南中的第一个步骤包含了有关如何进行此操作的详细信息。

Once we’ve downloaded the JSON file with the credential information, let’s copy the contents in a google-sheets-client-secret.json file in the src/main/resources directory of our application.

一旦我们下载了包含证书信息的JSON文件，让我们将其内容复制到我们应用程序的src/main/resources目录下的google-sheets-client-secret.json文件中。

The contents of the file should be similar to this:

该文件的内容应该与此类似。

{
  "installed":
    {
      "client_id":"<your_client_id>",
      "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":"<your_client_secret>",
      "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
    }
}

3.2. Obtaining a Credential Object

3.2.获得一个Credential对象

A successful authorization returns a Credential object we can use to interact with the Google Sheets API.

成功的授权会返回一个Credential对象，我们可以用它来与Google Sheets API互动。

Let’s create a GoogleAuthorizeUtil class with a static authorize() method which reads the content of the JSON file above and builds a GoogleClientSecrets object.

让我们创建一个GoogleAuthorizeUtil类，它有一个静态的authorize()方法，可以读取上述JSON文件的内容并建立一个GoogleClientSecrets对象。

Then, we’ll create a GoogleAuthorizationCodeFlow and send the authorization request:

然后，我们将创建一个GoogleAuthorizationCodeFlow并发送授权请求。

public class GoogleAuthorizeUtil {
    public static Credential authorize() throws IOException, GeneralSecurityException {
        
        // build GoogleClientSecrets from JSON file

        List<String> scopes = Arrays.asList(SheetsScopes.SPREADSHEETS);

        // build Credential object

        return credential;
    }
}

In our example, we’re setting the SPREADSHEETS scope since we want to access Google Sheets and using an in-memory DataStoreFactory to store the credentials received. Another option is using a FileDataStoreFactory to store the credentials in a file.

在我们的例子中，我们设置了SPREADSHEETS范围，因为我们想访问Google Sheets，并使用内存中的DataStoreFactory来存储收到的凭证。另一个选择是使用FileDataStoreFactory将凭证存储在一个文件中。

For the full source code of the GoogleAuthorizeUtil class, check out the GitHub project.

关于GoogleAuthorizeUtil class的完整源代码，请查看GitHub项目。

4. Constructing the Sheets Service Instance

4.构建Sheets服务实例

For interacting with Google Sheets, we’ll need a Sheets object which is the client for reading and writing through the API.

为了与Google Sheets进行交互，我们需要一个Sheets对象，它是通过API进行读写的客户端。

Let’s create a SheetsServiceUtil class that uses the Credential object above to obtain an instance of Sheets:

让我们创建一个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();
    }
}

Next, we’ll take a look at some of the most common operations we can perform using the API.

接下来，我们将看一下我们可以使用API进行的一些最常见的操作。

5. Writing Values on a Sheet

5.在工作表上书写数值

Interacting with an existing spreadsheet requires knowing that spreadsheet’s id, which we can find from its URL.

与现有电子表格互动需要知道该电子表格的ID，我们可以从其URL中找到。

For our examples, we’re going to use a public spreadsheet called “Expenses”, located at:

对于我们的例子，我们将使用一个名为 “支出 “的公共电子表格，位于。

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

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

Based on this URL, we can identify this spreadsheet’s id as “1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI”.

根据这个URL，我们可以确定这个电子表格的ID是 “1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PLfqYe4DhI”。

Also, to read and write values, we’re going to use spreadsheets.values collections.

另外，为了读写数值，我们将使用spreadsheets.values集合。

The values are represented as ValueRange objects, which are lists of lists of Java Objects, corresponding to rows or columns in a sheet.

这些值被表示为ValueRange对象，它是Java Objects的列表，对应于工作表的行或列。

Let’s create a test class where we initialize our Sheets service object and a SPREADSHEET_ID constant:

让我们创建一个测试类，在那里我们初始化我们的Sheets服务对象和一个SPREADSHEET_ID常量。

public class GoogleSheetsLiveTest {
    private static Sheets sheetsService;
    private static String SPREADSHEET_ID = // ...

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

Then, we can write values by:

然后，我们可以通过以下方式写出数值。

• writing to a single range
• writing to multiple ranges
• appending data after a table

5.1. Writing to a Single Range

5.1.写入单个范围

To write values to a single range on a sheet, we’ll use the spreadsheets().values().update() method:

为了将数值写入工作表的单一范围，我们将使用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();
}

Here, we’re first creating a ValueRange object with multiple rows containing a list of expenses for two months.

在这里，我们首先创建一个ValueRange对象，该对象有多行，包含两个月的支出列表。

Then, we’re using the update() method to build a request that writes the values to the spreadsheet with the given id, starting at the “A1” cell.

然后，我们使用update()方法建立一个请求，将数值写入给定id的电子表格，从 “A1 “单元开始。

To send the request, we’re using the execute() method.

为了发送请求，我们正在使用execute()方法。

If we want our value sets to be considered as columns instead of rows, we can use the setMajorDimension(“COLUMNS”) method.

如果我们希望我们的值集被认为是列而不是行，我们可以使用setMajorDimension(“COLUMNS”)方法。

The “RAW” input option means the values are written exactly as they are, and not computed.

“RAW “输入选项意味着数值是按原样写的，而不是经过计算。

When executing this JUnit test, the application will open a browser window using the system’s default browser that asks the user to log in and give our application permission to interact with Google Sheets on the user’s behalf:

当执行这个JUnit测试时，应用程序将使用系统的默认浏览器打开一个浏览器窗口，要求用户登录并给予我们的应用程序权限，代表用户与Google表单进行交互：。

Note that this manual step can be bypassed if you have an OAuth Service Account.

请注意，如果您有一个OAuth服务账户，就可以绕过这个手动步骤。

A requirement for the application to be able to view or edit the spreadsheet is that the signed-in user has a view or edit access to it. Otherwise, the request will result in a 403 error. The spreadsheet we use for our example is set to public edit access.

应用程序能够查看或编辑电子表格的一个要求是，登录的用户拥有查看或编辑权限。否则，该请求将导致403错误。我们的例子中使用的电子表格被设置为公共编辑权限。

Now, if we check the spreadsheet, we’ll see the range “A1:B6” is updated with our value sets.

现在，如果我们检查电子表格，我们会看到范围”A1:B6“被我们的值集所更新。

Let’s move on to writing to multiple disparate ranges in a single request.

让我们继续在一个请求中写到多个不同的范围。

5.2. Writing to Multiple Ranges

5.2.向多个区间写入数据

If we want to update multiple ranges on a sheet, we can use a BatchUpdateValuesRequest for better performance:

如果我们想更新一个工作表上的多个范围，我们可以使用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();

In this example, we’re first building a list of ValueRanges, each made up of two cells that represent the name of the month and the total expenses.

在这个例子中，我们首先建立一个ValueRanges的列表，每个列表由两个单元格组成，分别代表月份的名称和总支出。

Then, we’re creating a BatchUpdateValuesRequest with the input option “USER_ENTERED”, as opposed to “RAW”, meaning the cell values will be computed based on the formula of adding two other cells.

然后，我们要创建一个BatchUpdateValuesRequest，输入选项“USER_ENTERED”，而不是“RAW”，意味着单元格值将根据添加另外两个单元格的公式来计算。

Finally, we’re creating and sending the batchUpdate request. As a result, the ranges “D1:E1” and “D4:E4” will be updated.

最后，我们要创建并发送batchUpdate请求。结果，范围”D1:E1“和”D4:E4“将被更新。

5.3. Appending Data After a Table

5.3.在表后添加数据

Another way of writing values in a sheet is by appending them at the end of a table.

另一种在工作表中写入数值的方法是将其附加在表的末尾。

For this, we can use the append() method:

为此，我们可以使用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");

First, we’re building the ValueRange object containing the cell values we want to add.

首先，我们要建立ValueRange对象，包含我们要添加的单元格值。

In our case, this contains a cell with the total expenses for both months that we find by adding the “E1” and “E2” cell values.

在我们的案例中，这包含了一个包含两个月的总支出的单元格，我们通过添加“E1”和“E2”单元格的值来找到。

Then, we’re creating a request that will append the data after the table containing the “A1” cell.

然后，我们要创建一个请求，将数据追加到包含”A1“单元格的表格之后。

The INSERT_ROWS option means that we want the data to be added to a new row, and not replace any existing data after the table. This means the example will write the range “A7:B7” in its first run.

INSERT_ROWS选项意味着我们希望数据被添加到一个新的行中，而不是替换表后的任何现有数据。这意味着这个例子在第一次运行时将写入范围”A7:B7“。

On subsequent runs, the table that starts at the “A1” cell will now stretch to include the “A7:B7” row, so a new row goes to the “A8:B8” row, and so on.

在随后的运行中，从“A1”单元格开始的表格现在将扩展到包括“A7:B7”行，所以一个新行到“A8:B8”行，以此类推。

We also need to set the includeValuesInResponse property to true if we want to verify the response to a request. As a result, the response object will contain the updated data.

如果我们想验证对请求的响应，我们还需要将includeValuesInResponse属性设置为true。结果是，响应对象将包含更新的数据。

6. Reading Values from a Sheet

6.从工作表中读取数值

Let’s verify that our values were written correctly by reading them from the sheet.

让我们通过从工作表上读取数值来验证我们的数值是否写得正确。

We can do this by using the spreadsheets().values().get() method to read a single range or the batchUpdate() method to read multiple ranges:

我们可以通过使用spreadsheets().values().get()方法来读取单个范围，或者使用batchUpdate()方法来读取多个范围：。

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");

Here, we’re reading the ranges “E1” and “E4” and verifying that they contain the total for each month that we wrote before.

这里，我们正在读取“E1”和“E4”范围，并验证它们是否包含我们之前写的每个月的总数。

7. Creating New Spreadsheets

7.创建新的电子表格

Besides reading and updating values, we can also manipulate sheets or entire spreadsheets by using spreadsheets() and spreadsheets().sheets() collections.

除了读取和更新数值，我们还可以通过使用spreadsheets()和spreadsheets().sheet()集合来操作表单或整个电子表格。

Let’s see an example of creating a new spreadsheet:

让我们看一个创建新电子表格的例子。

@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();   
}

Here, we’re first creating a Spreadsheet object with the title “My Spreadsheet” then building and sending a request using the create() and execute() methods.

在这里，我们首先创建一个标题为”我的Spreadsheet”的Spreadsheet>对象，然后使用create()和execute()方法构建并发送一个请求。

The new spreadsheet will be private and placed in the signed-in user’s Drive.

新的电子表格将是私有的，并放置在已登录用户的驱动器中。

8. Other Updating Operations

8.其他更新操作

Most other operations take the form of a Request object, which we then add to a list and use to build a BatchUpdateSpreadsheetRequest.

大多数其他操作采用Request对象的形式，然后我们将其添加到一个列表中，并用于构建BatchUpdateSpreadsheetRequest。。

Let’s see how we can send two requests to change the title of a spreadsheet and copy-paste a set of cells from one sheet to another:

让我们看看我们如何发送两个请求，以改变电子表格的标题，并将一组单元格从一个表复制粘贴到另一个表。

@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();
}

Here, we’re creating an UpdateSpreadSheetPropertiesRequest object which specifies the new title, a CopyPasteRequest object which contains the source and destination of the operation and then adding these objects to a List of Requests.

在这里，我们要创建一个UpdateSpreadSheetPropertiesRequest对象，它指定了新的标题，一个CopyPasteRequest对象，它包含操作的来源和目的地，然后将这些对象添加到List的Requests.。

Then, we’re executing both requests as a batch update.

然后，我们将这两个请求作为一个批次更新来执行。

Many other types of requests are available to use in a similar manner. For example, we can create a new sheet in a spreadsheet with an AddSheetRequest or alter values with a FindReplaceRequest.

许多其他类型的请求也可以用类似的方式来使用。例如，我们可以用AddSheetRequest在电子表格中创建一个新的工作表，或者用FindReplaceRequest改变数值。

We can perform other operations such as changing borders, adding filters or merging cells. The full list of Request types is available here.

我们可以执行其他操作，如改变边框、添加过滤器或合并单元格。Request类型的完整列表可在此处获得。

9. Conclusion

9.结论

In this article, we’ve seen how we can connect to the Google Sheets API from a Java application and a few examples of manipulating documents stored in Google Sheets.

在这篇文章中，我们已经看到了如何从Java应用程序连接到Google Sheets API，以及一些操作存储在Google Sheets中的文档的例子。

The full source code of the examples can be found over on GitHub.

示例的完整源代码可以在GitHub上找到over。