streamalism.org

1. Overzicht

Google Spreadsheets biedt een handige manier om spreadsheets op te slaan en te bewerken en met anderen samen aan een document te werken.

Soms kan het handig zijn om vanuit een applicatie toegang te krijgen tot deze documenten, bijvoorbeeld om een ​​geautomatiseerde bewerking uit te voeren. Voor dit doel biedt Google de Google Spreadsheets-API waarmee ontwikkelaars kunnen communiceren.

In dit artikel, we gaan kijken hoe we verbinding kunnen maken met de API en bewerkingen kunnen uitvoeren op Google Spreadsheets.

2. Maven afhankelijkheden

Om verbinding te maken met de API en documenten te manipuleren, moeten we de afhankelijkheden google-api-client, google-oauth-client-jetty en google-api-services-sheets toevoegen:

 com.google.api-client google-api-client 1.23.0 com.google.oauth-client google-oauth-client-steiger 1.23.0 com.google.apis google-api-services-sheets v4-rev493-1.23. 0 

3. Autorisatie

De Google Spreadsheets-API vereist OAuth 2.0-autorisatie voordat we er via een applicatie toegang toe hebben.

Eerst moeten we een set OAuth-inloggegevens verkrijgen en deze vervolgens in onze applicatie gebruiken om een ​​autorisatieverzoek in te dienen.

3.1. OAuth 2.0-referenties verkrijgen

Om de inloggegevens te verkrijgen, moeten we een project maken in de Google Developers Console en vervolgens de Google Spreadsheets-API voor het project inschakelen. De eerste stap in de Google Quickstart-handleiding bevat gedetailleerde informatie over hoe u dit kunt doen.

Nadat we het JSON-bestand met de inloggegevens hebben gedownload, gaan we de inhoud kopiëren in een google-sheets-client-secret.json bestand in het src / main / resources directory van onze applicatie.

De inhoud van het bestand moet er ongeveer zo uitzien:

{"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. Verkrijgen van een Referentie Voorwerp

Een succesvolle autorisatie retourneert een Referentie object dat we kunnen gebruiken om te communiceren met de Google Spreadsheets-API.

Laten we een GoogleAuthorizeUtil klasse met een static toestemming geven() methode die de inhoud van het JSON-bestand hierboven leest en een GoogleClientSecrets voorwerp.

Vervolgens maken we een GoogleAuthorizationCodeFlow en stuur het autorisatieverzoek:

openbare klasse GoogleAuthorizeUtil {openbare statische referentie authorize () gooit IOException, GeneralSecurityException {// bouw GoogleClientSecrets uit JSON-bestand List scopes = Arrays.asList (SheetsScopes.SPREADSHEETS); // build referentie object retourneer referentie; }}

In ons voorbeeld stellen we de SPREADSHEETS bereik omdat we toegang willen hebben tot Google Spreadsheets en een in-memory willen gebruiken DataStoreFactory om de ontvangen inloggegevens op te slaan. Een andere optie is het gebruik van een FileDataStoreFactory om de legitimatiegegevens in een bestand op te slaan.

Voor de volledige broncode van het GoogleAuthorizeUtil class, bekijk het GitHub-project.

4. Het construeren van het Lakens Service-instantie

Voor interactie met Google Spreadsheets hebben we een Lakens object dat de client is voor het lezen en schrijven via de API.

Laten we een SheetsServiceUtil klasse die de Referentie object hierboven om een ​​exemplaar van Vellen:

openbare klasse SheetsServiceUtil {private static final String APPLICATION_NAME = "Google Spreadsheets-voorbeeld"; openbare statische bladen getSheetsService () gooit IOException, GeneralSecurityException {Credential credential = GoogleAuthorizeUtil.authorize (); retourneer nieuwe Sheets.Builder (GoogleNetHttpTransport.newTrustedTransport (), JacksonFactory.getDefaultInstance (), referentie) .setApplicationName (APPLICATION_NAME) .build (); }}

Vervolgens zullen we enkele van de meest voorkomende bewerkingen bekijken die we met de API kunnen uitvoeren.

5. Waarden op een blad schrijven

Om met een bestaande spreadsheet te kunnen werken, moet u de id van die spreadsheet kennen, die we kunnen vinden op de URL.

Voor onze voorbeelden gebruiken we een openbare spreadsheet met de naam 'Uitgaven', te vinden op:

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

Op basis van deze URL kunnen we de id van deze spreadsheet identificeren als "1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI".

Ook, om waarden te lezen en te schrijven, gaan we gebruiken spreadsheets.values collecties.

De waarden worden weergegeven als Waardebereik objecten, dit zijn lijsten met lijsten van Java Voorwerpen, overeenkomend met rijen of kolommen in een blad.

Laten we een testklasse maken waarin we onze Lakens service-object en een constante SPREADSHEET_ID:

openbare klasse GoogleSheetsLiveTest {privé statische Sheets sheetsService; private statische String SPREADSHEET_ID = // ... @BeforeClass public static void setup () gooit GeneralSecurityException, IOException {sheetsService = SheetsServiceUtil.getSheetsService (); }}

Vervolgens kunnen we waarden schrijven door:

• schrijven naar een enkel bereik
• schrijven naar meerdere bereiken
• gegevens aan een tabel toevoegen

5.1. Schrijven naar één bereik

Om waarden naar een enkel bereik op een blad te schrijven, gebruiken we de spreadsheets (). waarden (). update () methode:

@Test public void whenWriteSheet_thenReadSheetOk () gooit IOException {ValueRange body = new ValueRange () .setValues ​​(Arrays.asList (Arrays.asList ("Expenses January"), Arrays.asList ("books", "30"), Arrays.asList ("pennen", "10"), Arrays.asList ("Uitgaven februari"), Arrays.asList ("kleding", "20"), Arrays.asList ("schoenen", "5"))); UpdateValuesResponse resultaat = sheetsService.spreadsheets (). Values ​​() .update (SPREADSHEET_ID, "A1", body) .setValueInputOption ("RAW") .execute (); }

Hier maken we eerst een Waardebereik object met meerdere rijen met daarin een lijst met uitgaven voor twee maanden.

Vervolgens gebruiken we de bijwerken() methode om een ​​verzoek te bouwen dat de waarden naar de spreadsheet schrijft met de opgegeven id, beginnend bij de "A1" -cel.

Om het verzoek te verzenden, gebruiken we de uitvoeren () methode.

Als we willen dat onze waardensets worden beschouwd als kolommen in plaats van rijen, kunnen we de setMajorDimension ("COLUMNS") methode.

De invoeroptie "RAW" betekent dat de waarden precies zo worden geschreven als ze zijn, en niet worden berekend.

Bij het uitvoeren van deze JUnit-test, de applicatie opent een browservenster met de standaardbrowser van het systeem waarin de gebruiker wordt gevraagd in te loggen en onze applicatie toestemming geeft om namens de gebruiker met Google Spreadsheets te communiceren:

Merk op dat deze handmatige stap kan worden overgeslagen als u een OAuth-serviceaccount heeft.

Een vereiste voor de toepassing om de spreadsheet te kunnen bekijken of bewerken, is dat de aangemelde gebruiker de spreadsheet kan bekijken of bewerken. Anders resulteert het verzoek in een 403-fout. De spreadsheet die we voor ons voorbeeld gebruiken, is ingesteld op openbare bewerkingstoegang.

Als we de spreadsheet controleren, zien we het bereik "A1: B6”Is bijgewerkt met onze waardensets.

Laten we verder gaan met het schrijven naar meerdere ongelijksoortige bereiken in één verzoek.

5.2. Schrijven naar meerdere bereiken

Als we meerdere bereiken op een blad willen bijwerken, kunnen we een BatchUpdateValuesRequest voor betere prestaties:

Lijstgegevens = nieuwe ArrayList (); data.add (nieuwe ValueRange () .setRange ("D1") .setValues ​​(Arrays.asList (Arrays.asList ("Januari Totaal", "= B2 + B3")))); data.add (new ValueRange () .setRange ("D4") .setValues ​​(Arrays.asList (Arrays.asList ("February Total", "= B5 + B6")))); BatchUpdateValuesRequest batchBody = nieuwe BatchUpdateValuesRequest () .setValueInputOption ("USER_ENTERED") .setData (gegevens); BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets (). Waarden () .batchUpdate (SPREADSHEET_ID, batchBody) .execute ();

In dit voorbeeld maken we eerst een lijst met Waardebereik, elk bestaande uit twee cellen die de naam van de maand en de totale uitgaven vertegenwoordigen.

Vervolgens maken we een BatchUpdateValuesRequest met de invoeroptie "USER_ENTERED", in tegenstelling tot "RAW", wat betekent dat de celwaarden worden berekend op basis van de formule van het toevoegen van twee andere cellen.

Ten slotte maken en verzenden we het batchUpdate verzoek. Het resultaat is dat de bereiken "D1: E1"En"D4: E4" zal geüpdatet worden.

5.3. Gegevens toevoegen na een tabel

Een andere manier om waarden in een blad te schrijven, is door ze aan het einde van een tabel toe te voegen.

Hiervoor kunnen we de toevoegen () methode:

ValueRange appendBody = nieuwe ValueRange () .setValues ​​(Arrays.asList (Arrays.asList ("Total", "= E1 + E4"))); AppendValuesResponse appendResult = sheetsService.spreadsheets (). Waarden () .append (SPREADSHEET_ID, "A1", appendBody) .setValueInputOption ("USER_ENTERED") .setInsertDataOption ("INSERT_ROWS") .setIncludeValuesIn. ValueRange totaal = appendResult.getUpdates (). GetUpdatedData (); assertThat (total.getValues ​​(). get (0) .get (1)). isEqualTo ("65");

Ten eerste bouwen we het Waardebereik object met de celwaarden die we willen toevoegen.

In ons geval bevat dit een cel met de totale uitgaven voor beide maanden die we vinden door de "E1" en "E2" celwaarden.

Vervolgens maken we een verzoek dat de gegevens toevoegt na de tabel met de "A1" cel.

De INSERT_ROWS optie betekent dat we willen dat de gegevens worden toegevoegd aan een nieuwe rij en geen bestaande gegevens na de tabel vervangen. Dit betekent dat het voorbeeld het bereik "A7: B7”In zijn eerste run.

Bij volgende runs wordt de tafel die begint bij de "A1" cel wordt nu uitgerekt om de "A7: B7" rij, dus een nieuwe rij gaat naar de "A8: B8" rij, enzovoort.

We moeten ook de includeValuesInResponse property op true als we het antwoord op een verzoek willen verifiëren. Als resultaat zal het responsobject de bijgewerkte gegevens bevatten.

6. Waarden van een blad lezen

Laten we controleren of onze waarden correct zijn geschreven door ze vanaf het blad te lezen.

We kunnen dit doen door de spreadsheets (). waarden (). get () methode om een ​​enkel bereik of de batchUpdate () methode om meerdere bereiken te lezen:

Lijstbereiken = Arrays.asList ("E1", "E4"); BatchGetValuesResponse readResult = sheetsService.spreadsheets (). Waarden () .batchGet (SPREADSHEET_ID) .setRanges (bereiken) .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");

Hier lezen we de bereiken "E1" en "E 4" en controleren of ze het totaal bevatten voor elke maand die we eerder schreven.

7. Nieuwe spreadsheets maken

Naast het lezen en bijwerken van waarden, kunnen we ook bladen of hele spreadsheets manipuleren door gebruik te maken van spreadsheets () en spreadsheets (). sheets () collecties.

Laten we een voorbeeld bekijken van het maken van een nieuwe spreadsheet:

@Test public void test () gooit IOException {Spreadsheet spreadsheet = new Spreadsheet (). SetProperties (nieuwe SpreadsheetProperties (). SetTitle ("Mijn spreadsheet")); Werkbladresultaat = sheetsService .spreadsheets () .create (spreadsheet) .execute (); assertThat (result.getSpreadsheetId ()). isNotNull (); }

Hier maken we eerst een Werkblad object met de titel 'MijnSpreadsheet ' vervolgens bouwen en een verzoek verzenden met behulp van de maken () en uitvoeren () methoden.

De nieuwe spreadsheet is privé en wordt in de Drive van de aangemelde gebruiker geplaatst.

8. Andere bijwerkhandelingen

De meeste andere bewerkingen hebben de vorm van een Verzoek object, dat we vervolgens aan een lijst toevoegen en gebruiken om een BatchUpdateSpreadsheetRequest.

Laten we eens kijken hoe we twee verzoeken kunnen verzenden om de titel van een spreadsheet te wijzigen en een reeks cellen van het ene blad naar het andere te kopiëren en plakken:

@Test openbare leegte whenUpdateSpreadSheetTitle_thenOk () gooit IOException {UpdateSpreadsheetPropertiesRequest updateSpreadSheetRequest = nieuwe UpdateSpreadsheetPropertiesRequest (). SetFields ("*") .setProperties (nieuwe SpreadsheetProperties) (nieuwe SpreadsheetProperties); CopyPasteRequest copyRequest = nieuwe CopyPasteRequest () .setSource (nieuwe GridRange (). SetSheetId (0) .setStartColumnIndex (0) .setEndColumnIndex (2) .setStartRowIndex (0) .setEndRowIndex (1)) .setEndRowIndex (1)). 1) .setStartColumnIndex (0) .setEndColumnIndex (2) .setStartRowIndex (0) .setEndRowIndex (1)) .setPasteType ("PASTE_VALUES"); Lijstverzoeken = nieuwe ArrayList (); requests.add (nieuw Request () .setCopyPaste (copyRequest)); requests.add (nieuw Request () .setUpdateSpreadsheetProperties (updateSpreadSheetRequest)); BatchUpdateSpreadsheetRequest body = nieuwe BatchUpdateSpreadsheetRequest (). SetRequests (verzoeken); sheetsService.spreadsheets (). batchUpdate (SPREADSHEET_ID, body) .execute (); }

Hier maken we een UpdateSpreadSheetPropertiesRequest object dat de nieuwe titel specificeert, een CopyPasteRequest object dat de bron en bestemming van de bewerking bevat en deze objecten vervolgens aan een Lijst van Verzoeken.

Vervolgens voeren we beide verzoeken uit als een batchupdate.

Er zijn veel andere soorten verzoeken beschikbaar om op een vergelijkbare manier te gebruiken. We kunnen bijvoorbeeld een nieuw blad in een spreadsheet maken met een AddSheetRequest of verander waarden met een FindReplaceRequest.

We kunnen andere bewerkingen uitvoeren, zoals het wijzigen van randen, het toevoegen van filters of het samenvoegen van cellen. De volledige lijst met Verzoek types is hier beschikbaar.

9. Conclusie

In dit artikel hebben we gezien hoe we verbinding kunnen maken met de Google Spreadsheets-API vanuit een Java-applicatie en een paar voorbeelden van het manipuleren van documenten die zijn opgeslagen in Google Spreadsheets.

De volledige broncode van de voorbeelden is te vinden op GitHub.