Interagieren Sie mit Google Sheets aus Java

1. Übersicht

Google Sheets bietet eine bequeme Möglichkeit, Tabellenkalkulationen zu speichern, zu bearbeiten und mit anderen an einem Dokument zusammenzuarbeiten.

Manchmal kann es nützlich sein, von einer Anwendung aus auf diese Dokumente zuzugreifen, um beispielsweise einen automatisierten Vorgang auszuführen. Zu diesem Zweck stellt Google die Google Sheets-API zur Verfügung, mit der Entwickler interagieren können.

In diesem Artikel werden wir uns ansehen, wie wir eine Verbindung zur API herstellen und Vorgänge in Google Sheets ausführen können.

2. Maven-Abhängigkeiten

Um eine Verbindung zur API herzustellen und Dokumente zu bearbeiten, müssen Sie die Abhängigkeiten google-api-client, google-oauth-client-jetty und google-api-services-sheet hinzufügen:

 com.google.api-client google-api-client 1.23.0   com.google.oauth-client google-oauth-client-jetty 1.23.0   com.google.apis google-api-services-sheets v4-rev493-1.23.0 

3. Autorisierung

Die Google Sheets-API erfordert eine OAuth 2.0-Autorisierung, bevor wir über eine Anwendung darauf zugreifen können.

Zuerst müssen wir eine Reihe von OAuth-Anmeldeinformationen erhalten und diese dann in unserer Anwendung verwenden, um einen Antrag auf Autorisierung zu stellen.

3.1. Abrufen von OAuth 2.0-Anmeldeinformationen

Um die Anmeldeinformationen zu erhalten, müssen Sie ein Projekt in der Google Developers Console erstellen und anschließend die Google Sheets-API für das Projekt aktivieren. Der erste Schritt in der Google-Kurzanleitung enthält detaillierte Informationen dazu.

Nachdem wir die JSON-Datei mit den Anmeldeinformationen heruntergeladen haben, kopieren wir den Inhalt in eine Datei google-sheet-client-secret.json im Verzeichnis src / main / resources unserer Anwendung.

Der Inhalt der Datei sollte ungefähr so ​​aussehen:

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

3.2. Beziehen eines Credential - Objekt

Eine erfolgreiche Autorisierung gibt ein Anmeldeinformationsobjekt zurück , mit dem wir mit der Google Sheets-API interagieren können.

Erstellen wir eine GoogleAuthorizeUtil- Klasse mit einer statischen authorize () -Methode, die den Inhalt der obigen JSON-Datei liest und ein GoogleClientSecrets- Objekt erstellt.

Anschließend erstellen wir einen GoogleAuthorizationCodeFlow und senden die Autorisierungsanforderung:

public class GoogleAuthorizeUtil { public static Credential authorize() throws IOException, GeneralSecurityException { // build GoogleClientSecrets from JSON file List scopes = Arrays.asList(SheetsScopes.SPREADSHEETS); // build Credential object return credential; } }

In unserem Beispiel legen wir den Bereich SPREADSHEETS fest, da wir auf Google Sheets zugreifen und eine empfangene DataStoreFactory im Speicher zum Speichern der empfangenen Anmeldeinformationen verwenden möchten . Eine andere Option ist die Verwendung einer FileDataStoreFactory , um die Anmeldeinformationen in einer Datei zu speichern.

Den vollständigen Quellcode der GoogleAuthorizeUtil- Klauseln finden Sie im GitHub-Projekt.

4. Erstellen der Sheets Service Instance

Für die Interaktion mit Google Sheets benötigen wir ein Sheets- Objekt, das der Client zum Lesen und Schreiben über die API ist .

Erstellen wir eine SheetsServiceUtil- Klasse, die das obige Credential- Objekt verwendet, um eine Instanz von Sheets abzurufen :

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

Als nächstes werfen wir einen Blick auf einige der häufigsten Vorgänge, die wir mit der API ausführen können.

5. Werte auf ein Blatt schreiben

Für die Interaktion mit einer vorhandenen Tabelle muss die ID der Tabelle bekannt sein, die wir anhand ihrer URL finden.

Für unsere Beispiele verwenden wir eine öffentliche Tabelle mit dem Namen "Ausgaben" unter:

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

Basierend auf dieser URL können wir die ID dieser Tabelle als "1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI" identifizieren.

Auch zu lesen und Schreibwerte, wir gehen verwenden spreadsheets.values Sammlungen.

Die Werte werden als ValueRange- Objekte dargestellt. Hierbei handelt es sich um Listen von Listen von Java- Objekten, die Zeilen oder Spalten in einem Blatt entsprechen.

Erstellen wir eine Testklasse, in der wir unser Sheets- Serviceobjekt und eine SPREADSHEET_ID-Konstante initialisieren :

public class GoogleSheetsLiveTest { private static Sheets sheetsService; private static String SPREADSHEET_ID = // ... @BeforeClass public static void setup() throws GeneralSecurityException, IOException { sheetsService = SheetsServiceUtil.getSheetsService(); } }

Dann können wir Werte schreiben durch:

  • Schreiben in einen einzelnen Bereich
  • Schreiben in mehrere Bereiche
  • Anhängen von Daten nach einer Tabelle

5.1. Schreiben in einen einzelnen Bereich

Um Werte in einen einzelnen Bereich auf einem Blatt zu schreiben, verwenden wir die Methode tablesheets (). 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(); }

Hier erstellen wir zunächst ein ValueRange- Objekt mit mehreren Zeilen, die eine Liste der Ausgaben für zwei Monate enthalten.

Anschließend verwenden wir die update () -Methode, um eine Anforderung zu erstellen, die die Werte mit der angegebenen ID in die Tabelle schreibt, beginnend mit der Zelle "A1".

Zum Senden der Anfrage verwenden wir die Methode execute () .

Wenn unsere Wertesätze als Spalten anstelle von Zeilen betrachtet werden sollen, können wir die Methode setMajorDimension („COLUMNS“) verwenden.

Die Eingabeoption „RAW“ bedeutet, dass die Werte genau so geschrieben werden, wie sie sind, und nicht berechnet werden.

Bei der Ausführung dieses JUnit-Tests öffnet die Anwendung ein Browserfenster mit dem Standardbrowser des Systems, in dem der Benutzer aufgefordert wird, sich anzumelden und unserer Anwendung die Berechtigung zu erteilen, im Namen des Benutzers mit Google Sheets zu interagieren:

Beachten Sie, dass dieser manuelle Schritt umgangen werden kann, wenn Sie über ein OAuth-Dienstkonto verfügen.

Voraussetzung für die Anzeige oder Bearbeitung der Tabelle durch die Anwendung ist, dass der angemeldete Benutzer über eine Ansicht verfügt oder Zugriff darauf hat. Andernfalls führt die Anforderung zu einem 403-Fehler. Die Tabelle, die wir für unser Beispiel verwenden, ist auf öffentlichen Bearbeitungszugriff eingestellt.

Wenn wir nun die Tabelle überprüfen, sehen wir, dass der Bereich „ A1: B6 “ mit unseren Wertesätzen aktualisiert wird.

Fahren wir mit dem Schreiben in mehrere unterschiedliche Bereiche in einer einzigen Anforderung fort.

5.2. Schreiben in mehrere Bereiche

Wenn wir mehrere Bereiche auf einem Blatt aktualisieren möchten, können wir eine BatchUpdateValuesRequest verwenden, um eine bessere Leistung zu erzielen:

List 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 diesem Beispiel erstellen wir zunächst eine Liste von ValueRanges, die jeweils aus zwei Zellen bestehen, die den Namen des Monats und die Gesamtkosten darstellen.

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.

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

5.3. Appending Data After a Table

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:

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.

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.

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

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.

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.

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.

6. Reading Values from a Sheet

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:

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

7. Creating New Spreadsheets

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

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 “MySpreadsheet” then building and sending a request using the create() and execute() methods.

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

8. Other Updating Operations

Most other operations take the form of a Request object, which we then add to a list and use to build a 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 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.

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.

Wir können andere Vorgänge ausführen, z. B. das Ändern von Rahmen, das Hinzufügen von Filtern oder das Zusammenführen von Zellen. Die vollständige Liste der Anforderungstypen finden Sie hier.

9. Fazit

In diesem Artikel haben wir gesehen, wie wir von einer Java-Anwendung aus eine Verbindung zur Google Sheets-API herstellen können, und einige Beispiele für die Bearbeitung von in Google Sheets gespeicherten Dokumenten.

Den vollständigen Quellcode der Beispiele finden Sie auf GitHub.