Interagisci con Fogli Google da Java

1. Panoramica

Fogli Google offre un modo conveniente per archiviare e manipolare fogli di lavoro e collaborare con altri su un documento.

A volte può essere utile accedere a questi documenti da un'applicazione, ad esempio per eseguire un'operazione automatizzata. A tal fine, Google fornisce l'API di Fogli Google con cui gli sviluppatori possono interagire.

In questo articolo, daremo un'occhiata a come possiamo connetterci all'API ed eseguire operazioni su Fogli Google.

2. Dipendenze di Maven

Per connettersi all'API e manipolare i documenti, dovremo aggiungere le dipendenze google-api-client, google-oauth-client-jetty e google-api-services-sheet:

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

L'API di Fogli Google richiede l'autorizzazione OAuth 2.0 prima che possiamo accedervi tramite un'applicazione.

Innanzitutto, dobbiamo ottenere una serie di credenziali OAuth, quindi utilizzarle nella nostra applicazione per inviare una richiesta di autorizzazione.

3.1. Ottenimento delle credenziali OAuth 2.0

Per ottenere le credenziali, dovremo creare un progetto nella Google Developers Console e quindi abilitare l'API di Fogli Google per il progetto. Il primo passaggio nella guida di avvio rapido di Google contiene informazioni dettagliate su come eseguire questa operazione.

Dopo aver scaricato il file JSON con le informazioni sulle credenziali, copiamo il contenuto in un file google-sheet-client-secret.json nella directory src / main / resources della nostra applicazione.

Il contenuto del file dovrebbe essere simile a questo:

{ "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. Ottenere un oggetto credenziale

Un'autorizzazione riuscita restituisce un oggetto Credential che possiamo utilizzare per interagire con l'API di Fogli Google.

Creiamo una classe GoogleAuthorizeUtil con un metodo authorize () statico che legge il contenuto del file JSON sopra e crea un oggetto GoogleClientSecrets .

Quindi, creeremo un GoogleAuthorizationCodeFlow e invieremo la richiesta di autorizzazione:

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

Nel nostro esempio, stiamo impostando l' ambito SPREADSHEETS poiché vogliamo accedere a Fogli Google e utilizzare un DataStoreFactory in memoria per archiviare le credenziali ricevute. Un'altra opzione è utilizzare un FileDataStoreFactory per memorizzare le credenziali in un file.

Per il codice sorgente completo del GoogleAuthorizeUtil cla ss, controllare il progetto GitHub.

4. Costruzione dell'istanza del servizio Fogli

Per interagire con Fogli Google, avremo bisogno di un oggetto Fogli che è il client per la lettura e la scrittura tramite l'API .

Creiamo una classe SheetsServiceUtil che utilizza l' oggetto Credential sopra per ottenere un'istanza di 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(); } }

Successivamente, daremo un'occhiata ad alcune delle operazioni più comuni che possiamo eseguire utilizzando l'API.

5. Scrivere valori su un foglio

L'interazione con un foglio di calcolo esistente richiede la conoscenza dell'ID del foglio di calcolo, che possiamo trovare dal suo URL.

Per i nostri esempi, utilizzeremo un foglio di lavoro pubblico chiamato "Spese", situato in:

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

In base a questo URL, possiamo identificare l'ID di questo foglio di calcolo come "1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI".

Inoltre, per leggere e scrivere valori, utilizzeremo le raccolte spreadsheets.values .

I valori sono rappresentati come oggetti ValueRange , che sono elenchi di elenchi di oggetti Java , corrispondenti a righe o colonne in un foglio.

Creiamo una classe di test in cui inizializziamo il nostro oggetto di servizio Fogli e una costante 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(); } }

Quindi, possiamo scrivere valori in base a:

  • scrivendo su un singolo intervallo
  • scrittura su più intervalli
  • aggiungendo dati dopo una tabella

5.1. Scrittura su un singolo intervallo

Per scrivere valori in un singolo intervallo su un foglio, useremo il metodo 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(); }

Qui, creiamo prima un oggetto ValueRange con più righe contenenti un elenco di spese per due mesi.

Quindi, stiamo utilizzando il metodo update () per creare una richiesta che scrive i valori sul foglio di calcolo con l'id dato, a partire dalla cella "A1".

Per inviare la richiesta, stiamo usando il metodo execute () .

Se vogliamo che i nostri insiemi di valori vengano considerati come colonne invece che come righe, possiamo usare il metodo setMajorDimension ("COLUMNS") .

L'opzione di input "RAW" significa che i valori vengono scritti esattamente come sono e non calcolati.

Quando si esegue questo test JUnit, l'applicazione aprirà una finestra del browser utilizzando il browser predefinito del sistema che chiede all'utente di accedere e autorizza la nostra applicazione a interagire con Fogli Google per conto dell'utente:

Tieni presente che questo passaggio manuale può essere ignorato se disponi di un account di servizio 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.

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

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

5.2. Writing to Multiple Ranges

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

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

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.

Possiamo eseguire altre operazioni come cambiare i bordi, aggiungere filtri o unire celle. L'elenco completo dei tipi di richiesta è disponibile qui.

9. Conclusione

In questo articolo, abbiamo visto come possiamo connetterci all'API di Fogli Google da un'applicazione Java e alcuni esempi di manipolazione dei documenti archiviati in Fogli Google.

Il codice sorgente completo degli esempi può essere trovato su GitHub.