Взаимодействуйте с Google Sheets из Java

1. Обзор

Google Sheets предоставляет удобный способ хранения электронных таблиц и управления ими, а также совместной работы над документом с другими пользователями.

Иногда бывает полезно получить доступ к этим документам из приложения, например, для выполнения автоматизированной операции. Для этой цели Google предоставляет API Google Sheets, с которым разработчики могут взаимодействовать.

В этой статье мы рассмотрим, как мы можем подключиться к API и выполнять операции в Google Таблицах.

2. Зависимости Maven

Чтобы подключиться к 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. Авторизация

API Google Sheets требует авторизации OAuth 2.0 , прежде чем мы сможем получить к нему доступ через приложение.

Во-первых, нам нужно получить набор учетных данных OAuth, а затем использовать их в нашем приложении для отправки запроса на авторизацию.

3.1. Получение учетных данных OAuth 2.0

Чтобы получить учетные данные, нам нужно создать проект в Google Developers Console , а затем включить API Google Таблиц для проекта. Первый шаг в руководстве Google Quickstart содержит подробную информацию о том, как это сделать.

После того, как мы загрузили файл JSON с учетными данными, давайте скопируем содержимое файла google-sheets-client-secret.json в каталог src/main/resources нашего приложения.

Содержимое файла должно быть примерно таким:

{
  "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. Получение объекта учетных данных

Успешная авторизация возвращает объект Credential , который мы можем использовать для взаимодействия с API Google Sheets.

Давайте создадим класс GoogleAuthorizeUtil со статическим методом authorize() , который считывает содержимое файла JSON выше и создает объект GoogleClientSecrets .

Затем мы создадим 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;
    }
}

В нашем примере мы устанавливаем область SPREADSHEETS , так как мы хотим получить доступ к Google Sheets и использовать DataStoreFactory в памяти для хранения полученных учетных данных. Другой вариант — использовать FileDataStoreFactory для хранения учетных данных в файле.

Полный исходный код класса GoogleAuthorizeUtil см . в проекте 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();
    }
}

Далее мы рассмотрим некоторые из наиболее распространенных операций, которые мы можем выполнять с помощью API.

5. Запись значений на листе

Для взаимодействия с существующей электронной таблицей необходимо знать идентификатор этой электронной таблицы, который мы можем найти по ее URL-адресу.

Для наших примеров мы будем использовать общедоступную электронную таблицу под названием «Расходы», расположенную по адресу:

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

На основе этого URL-адреса мы можем определить идентификатор этой электронной таблицы как «1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI».

Кроме того, для чтения и записи значений мы будем использовать коллекции электронных таблиц .

Значения представлены в виде объектов ValueRange , которые представляют собой списки списков объектов Java , соответствующих строкам или столбцам на листе.

Давайте создадим тестовый класс, в котором мы инициализируем наш сервисный объект 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();
    }
}

Затем мы можем записать значения:

• запись в один диапазон
• запись в несколько диапазонов
• добавление данных после таблицы

5.1. Запись в один диапазон

Чтобы записать значения в один диапазон на листе, мы будем использовать метод электронных таблиц().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") .

Параметр ввода «RAW» означает, что значения записываются точно так, как они есть, а не вычисляются.

При выполнении этого теста JUnit приложение откроет окно браузера, используя системный браузер по умолчанию, который попросит пользователя войти в систему и предоставить нашему приложению разрешение на взаимодействие с Google Sheets от имени пользователя:

Обратите внимание, что этот ручной шаг можно пропустить, если у вас есть сервисный аккаунт OAuth .

Требование для того, чтобы приложение могло просматривать или редактировать электронную таблицу, заключается в том, что вошедший в систему пользователь имеет доступ к просмотру или редактированию. В противном случае запрос приведет к ошибке 403. Электронная таблица, которую мы используем для нашего примера, настроена на общедоступный доступ для редактирования.

Теперь, если мы проверим электронную таблицу, мы увидим, что диапазон « A1:B6 » обновлен нашими наборами значений.

Давайте перейдем к записи в несколько разрозненных диапазонов в одном запросе.

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

В этом примере мы сначала создаем список ValueRanges, каждый из которых состоит из двух ячеек, представляющих название месяца и общие расходы.

Затем мы создаем BatchUpdateValuesRequest с опцией ввода «USER_ENTERED», а не «RAW», что означает, что значения ячеек будут вычисляться на основе формулы добавления двух других ячеек.

Наконец, мы создаем и отправляем запрос batchUpdate . В результате диапазоны « 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");

Во-первых, мы создаем объект ValueRange , содержащий значения ячеек, которые мы хотим добавить.

В нашем случае это содержит ячейку с общими расходами за оба месяца, которые мы находим, складывая значения ячеек «E1» и «E2» .

Затем мы создаем запрос, который добавит данные после таблицы, содержащей ячейку « A1 ».

Параметр INSERT_ROWS означает, что мы хотим, чтобы данные добавлялись в новую строку, а не заменяли какие-либо существующие данные после таблицы. Это означает, что пример запишет диапазон « A7:B7 » при первом запуске.

При последующих запусках таблица, которая начинается с ячейки «A1» , теперь будет растягиваться, чтобы включить строку «A7: B7» , поэтому новая строка переходит к строке «A8: B8» и так далее.

Нам также необходимо установить для свойства includeValuesInResponse значение true, если мы хотим проверить ответ на запрос . В результате объект ответа будет содержать обновленные данные.

6. Чтение значений с листа

Давайте проверим правильность написания наших значений, прочитав их с листа.

Мы можем сделать это, используя метод 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");

Здесь мы читаем диапазоны «E1» и «E4» и проверяем, содержат ли они итоговую сумму за каждый месяц, которую мы записали ранее.

7. Создание новых электронных таблиц

Помимо чтения и обновления значений, мы также можем манипулировать листами или целыми электронными таблицами, используя коллекции электронных таблиц() и электронных таблиц().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();   
}

Здесь мы сначала создаем объект электронной таблицы с заголовком « Моя таблица» , затем строим и отправляем запрос с использованием методов create() и execute() .

Новая таблица будет закрыта и размещена на Диске пользователя, вошедшего в систему.

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 , который содержит источник и место назначения операции, а затем добавляем эти объекты в список запросов .

Затем мы выполняем оба запроса как пакетное обновление.

Многие другие типы запросов доступны для использования аналогичным образом. Например, мы можем создать новый лист в электронной таблице с помощью AddSheetRequest или изменить значения с помощью FindReplaceRequest.

Мы можем выполнять другие операции, такие как изменение границ, добавление фильтров или объединение ячеек. Полный список типов запросов доступен здесь .

9. Заключение

В этой статье мы увидели, как мы можем подключиться к API Google Sheets из приложения Java, и несколько примеров манипулирования документами, хранящимися в Google Sheets.

Полный исходный код примеров можно найти на GitHub .