«Взаимодействуйте с 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. Получение объекта учетных данных

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

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

Затем мы создадим GoogleAuthorizationCodeFlow и отправим запрос на авторизацию. полученные полномочия. Другой вариант — использовать FileDataStoreFactory для хранения учетных данных в файле.

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;
    }
}

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

4. Создание экземпляра сервиса Sheets

Для взаимодействия с Google Sheets нам понадобится объект Sheets, который является клиентом для чтения и записи через API.

Давайте создадим класс SheetsServiceUtil, который использует указанный выше объект Credential для получения экземпляра Sheets:

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

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. Запись значений на лист

Взаимодействие с существующей электронной таблицей требует знания идентификатора этой электронной таблицы, который мы можем найти по ее 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():

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

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

Затем мы используем метод update() для создания запроса, который записывает значения в электронную таблицу с заданным идентификатором, начиная с ячейки «A1».

«Чтобы отправить запрос, мы используем метод execute().

Если мы хотим, чтобы наши наборы значений рассматривались как столбцы, а не строки, мы можем использовать метод setMajorDimension(“COLUMNS”).

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

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

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

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

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

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

5.2. Запись в несколько диапазонов

Если мы хотим обновить несколько диапазонов на листе, мы можем использовать BatchUpdateValuesRequest для повышения производительности:

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

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

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

Наконец, мы создаем и отправляем запрос batchUpdate. В результате диапазоны «D1:E1» и «D4:E4» будут обновлены.

5.3. Добавление данных после таблицы

Другой способ записи значений на листе — добавление их в конец таблицы.

Для этого мы можем использовать метод append():

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

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

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

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

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

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

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

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

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

Мы можем сделать это, используя метод spreadsheets().values().get() для чтения одного диапазона или метод batchUpdate() для чтения нескольких диапазонов:

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

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

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

Помимо чтения и обновления значений, мы также можем манипулировать листами или целыми электронными таблицами с помощью наборов электронных таблиц() и электронных таблиц().sheets().

Давайте рассмотрим пример создания новой электронной таблицы:

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

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

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

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.