streamalism.org

1. Oversikt

Google Sheets gir en praktisk måte å lagre og manipulere regneark og samarbeide med andre om et dokument.

Noen ganger kan det være nyttig å få tilgang til disse dokumentene fra et program, for eksempel å utføre en automatisert operasjon. For dette formålet tilbyr Google Google Sheets API som utviklere kan samhandle med.

I denne artikkelen, vi skal se på hvordan vi kan koble oss til API og utføre operasjoner på Google Sheets.

2. Maven-avhengigheter

For å koble til API og manipulere dokumenter, må vi legge til avhengighetene google-api-client, google-oauth-client-jetty og google-api-services-sheets:

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

Google Sheets API krever OAuth 2.0-autorisasjon før vi får tilgang til det gjennom en applikasjon.

Først må vi skaffe oss et sett med OAuth-legitimasjon, og deretter bruke dette i søknaden vår for å sende inn en forespørsel om autorisasjon.

3.1. Innhenting av OAuth 2.0-legitimasjon

For å få legitimasjonen må vi opprette et prosjekt i Google Developers Console og deretter aktivere Google Sheets API for prosjektet. Det første trinnet i Googles hurtigstartveiledning inneholder detaljert informasjon om hvordan du gjør dette.

Når vi har lastet ned JSON-filen med påloggingsinformasjonen, la oss kopiere innholdet i en google-sheets-client-secret.json filen i src / main / resources katalog over applikasjonen vår.

Innholdet i filen skal være lik dette:

{"installert": {"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. Få en Legitimasjon Gjenstand

En vellykket autorisasjon returnerer a Legitimasjon objekt vi kan bruke til å samhandle med Google Sheets API.

La oss lage en GoogleAuthorizeUtil klasse med statisk autorisere() metode som leser innholdet i JSON-filen ovenfor og bygger en GoogleClientSecrets gjenstand.

Deretter lager vi en GoogleAuthorizationCodeFlow og send autorisasjonsforespørselen:

offentlig klasse GoogleAuthorizeUtil {public static Credential authorize () kaster IOException, GeneralSecurityException {// bygg GoogleClientSecrets fra JSON-filliste omfang = Arrays.asList (SheetsScopes.SPREADSHEETS); // bygge legitimasjon for legitimasjonsobjekt; }}

I vårt eksempel setter vi SPREIDBORD omfang siden vi ønsker å få tilgang til Google Sheets og bruke et minne DataStoreFactory for å lagre den mottatte legitimasjonen. Et annet alternativ er å bruke en FileDataStoreFactory for å lagre legitimasjonen i en fil.

For den fulle kildekoden til GoogleAuthorizeUtil class, sjekk ut GitHub-prosjektet.

4. Konstruksjon av Ark Serviceinstans

For å kommunisere med Google Sheets trenger vi en Ark objekt som er klienten for å lese og skrive gjennom API.

La oss lage en SheetsServiceUtil klasse som bruker Legitimasjon objektet ovenfor for å få en forekomst av Ark:

offentlig klasse SheetsServiceUtil {private static final String APPLICATION_NAME = "Eksempel på Google Sheets"; offentlige statiske ark getSheetsService () kaster IOException, GeneralSecurityException {Credential credential = GoogleAuthorizeUtil.authorize (); returner nye Sheets.Builder (GoogleNetHttpTransport.newTrustedTransport (), JacksonFactory.getDefaultInstance (), legitimasjon) .setApplicationName (APPLICATION_NAME) .build (); }}

Deretter tar vi en titt på noen av de vanligste operasjonene vi kan utføre ved hjelp av API.

5. Skrive verdier på et ark

Samhandling med et eksisterende regneark krever at du vet at regnearkets ID, som vi kan finne fra nettadressen.

For eksemplene våre skal vi bruke et offentlig regneark som heter "Utgifter", som ligger på:

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

Basert på denne URL-en kan vi identifisere dette regnearkets id som “1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI”.

Også, for å lese og skrive verdier, skal vi bruke regneark. verdier samlinger.

Verdiene er representert som ValueRange objekter, som er lister over lister over Java Gjenstander, tilsvarer rader eller kolonner i et ark.

La oss lage en testklasse der vi initialiserer Ark tjenesteobjekt og en SPREADSHEET_ID-konstant:

offentlig klasse GoogleSheetsLiveTest {private statiske ark arkService; privat statisk streng SPREADSHEET_ID = // ... @BeforeClass offentlig statisk ugyldig oppsett () kaster GeneralSecurityException, IOException {sheetsService = SheetsServiceUtil.getSheetsService (); }}

Deretter kan vi skrive verdier ved å:

• skrive til et enkelt område
• skrive til flere områder
• legge til data etter en tabell

5.1. Skrive til et enkelt utvalg

For å skrive verdier til et enkelt område på et ark, bruker vi regneark (). verdier (). oppdatering () metode:

@Test offentlig ugyldig nårWriteSheet_thenReadSheetOk () kaster IOException {ValueRange body = new ValueRange () .setValues ​​(Arrays.asList (Arrays.asList ("Expenses January"), Arrays.asList ("books", "30"), Arrays.asList) ("penner", "10"), Arrays.asList ("Utgifter februar"), Arrays.asList ("klær", "20"), Arrays.asList ("sko", "5"))); UpdateValuesResponse result = sheetsService.spreadsheets (). Values ​​() .update (SPREADSHEET_ID, "A1", body) .setValueInputOption ("RAW") .execute (); }

Her oppretter vi først en ValueRange objekt med flere rader som inneholder en liste over utgifter i to måneder.

Så bruker vi Oppdater() metode for å lage en forespørsel som skriver verdiene til regnearket med den angitte id-en, og starter ved "A1" -cellen.

For å sende forespørselen bruker vi henrette() metode.

Hvis vi vil at verdisettene våre skal betraktes som kolonner i stedet for rader, kan vi bruke setMajorDimension (“COLUMNS”) metode.

Inngangsalternativet “RAW” betyr at verdiene skrives nøyaktig slik de er, og ikke beregnes.

Når du utfører denne JUnit-testen, applikasjonen åpner et nettleservindu ved å bruke systemets standard nettleser som ber brukeren logge på og gi applikasjonen vår tillatelse til å samhandle med Google Sheets på brukerens vegne:

Merk at dette manuelle trinnet kan omgåes hvis du har en OAuth-servicekonto.

Et krav for at applikasjonen skal kunne se eller redigere regnearket er at den påloggede brukeren har en visning eller redigeringstilgang til den. Ellers vil forespørselen resultere i en 403-feil. Regnearket vi bruker for vårt eksempel er satt til offentlig redigeringstilgang.

Nå, hvis vi sjekker regnearket, ser vi området “A1: B6”Er oppdatert med våre verdisett.

La oss gå videre til å skrive til flere forskjellige områder i en enkelt forespørsel.

5.2. Skrive til flere områder

Hvis vi vil oppdatere flere områder på et ark, kan vi bruke en BatchUpdateValuesRequest for bedre ytelse:

Listedata = ny ArrayList (); data.add (ny ValueRange () .setRange ("D1") .setValues ​​(Arrays.asList (Arrays.asList ("Januar totalt", "= B2 + B3")))); data.add (ny ValueRange () .setRange ("D4") .setValues ​​(Arrays.asList (Arrays.asList ("Februar Total", "= B5 + B6")))); BatchUpdateValuesRequest batchBody = ny BatchUpdateValuesRequest () .setValueInputOption ("USER_ENTERED") .setData (data); BatchUpdateValuesResponse batchResult = sheetsService.spreadsheets (). Values ​​() .batchUpdate (SPREADSHEET_ID, batchBody) .execute ();

I dette eksemplet bygger vi først en liste over ValueRanges, hver består av to celler som representerer navnet på måneden og de totale utgiftene.

Så lager vi en BatchUpdateValuesRequest med inngangsmuligheten “USER_ENTERED”, i motsetning til "RÅ", som betyr at celleverdiene blir beregnet basert på formelen å legge til to andre celler.

Til slutt lager og sender vi batchUpdate be om. Som et resultat blir områdene “D1: E1”Og”D4: E4" vil bli oppdatert.

5.3. Legge til data etter en tabell

En annen måte å skrive verdier på et ark er ved å legge dem til på slutten av en tabell.

For dette kan vi bruke legg til () metode:

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") .setIncludeValues ValueRange total = appendResult.getUpdates (). GetUpdatedData (); assertThat (total.getValues ​​(). get (0) .get (1)). isEqualTo ("65");

Først bygger vi ValueRange objekt som inneholder celleverdiene vi vil legge til.

I vårt tilfelle inneholder dette en celle med de totale utgiftene for begge månedene som vi finner ved å legge til “E1” og “E2” celleverdier.

Deretter oppretter vi en forespørsel som vil legge til dataene etter tabellen som inneholder “A1”Celle.

De INSERT_ROWS alternativet betyr at vi vil at dataene skal legges til en ny rad, og ikke erstatte eksisterende data etter tabellen. Dette betyr at eksemplet vil skrive området “A7: B7”I sitt første løp.

Ved påfølgende løp, bordet som starter ved “A1” cellen vil nå strekke seg for å inkludere “A7: B7” rad, så en ny rad går til “A8: B8” rad, og så videre.

Vi må også stille inn includeValuesInResponse eiendom til sant hvis vi ønsker å bekrefte svaret på en forespørsel. Som et resultat vil responsobjektet inneholde oppdaterte data.

6. Leseverdier fra et ark

La oss verifisere at verdiene våre ble skrevet riktig ved å lese dem fra arket.

Vi kan gjøre dette ved å bruke regneark (). verdier (). get () metode for å lese et enkelt område eller batchUpdate () metode for å lese flere områder:

Listeområder = 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");

Her leser vi områdene “E1” og “E4” og verifisere at de inneholder totalsummen for hver måned vi skrev før.

7. Opprette nye regneark

Foruten å lese og oppdatere verdier, kan vi også manipulere ark eller hele regneark ved hjelp av regneark () og regneark (). ark () samlinger.

La oss se et eksempel på å lage et nytt regneark:

@Test offentlig ugyldig test () kaster IOException {Regneark spreadSheet = nytt regneark (). SetProperties (nytt regnearkProperties (). SetTitle ("Mitt regneark")); Regnearkresultat = sheetsService .spreadsheets () .create (spreadsheet) .utfør (); assertThat (result.getSpreadsheetId ()). erNotNull (); }

Her oppretter vi først en Regneark objekt med tittelen “MinRegneark ” deretter bygge og sende en forespørsel ved hjelp av skape() og henrette() metoder.

Det nye regnearket vil være privat og plasseres i den påloggede brukerens Drive.

8. Andre oppdateringsoperasjoner

De fleste andre operasjoner har form av a Be om objekt, som vi deretter legger til i en liste og bruker til å bygge et BatchUpdateSpreadsheetRequest.

La oss se hvordan vi kan sende to forespørsler om å endre tittelen på et regneark og kopiere og lime inn et sett med celler fra ett ark til et annet:

@Test offentlig ugyldig nårUpdateSpreadSheetTitle_thenOk () kaster IOException {UpdateSpreadsheetPropertiesRequest updateSpreadSheetRequest = new UpdateSpreadsheetPropertiesRequest (). SetFields ("*") .setProperties (new SpreadsheetProperties.) (). " CopyPasteRequest copyRequest = ny CopyPasteRequest () .setSource (ny GridRange (). SetSheetId (0) .setStartColumnIndex (0) .setEndColumnIndex (2) .setStartRowIndex (0) .setEndRowIndex (1)). SetDestination (ny) 1) .setStartColumnIndex (0) .setEndColumnIndex (2) .setStartRowIndex (0) .setEndRowIndex (1)) .setPasteType ("PASTE_VALUES"); Listeforespørsler = ny ArrayList (); requests.add (ny forespørsel () .setCopyPaste (copyRequest)); requests.add (ny forespørsel () .setUpdateSpreadsheetProperties (updateSpreadSheetRequest)); BatchUpdateSpreadsheetRequest body = ny BatchUpdateSpreadsheetRequest (). SetRequests (forespørsler); sheetsService.spreadsheets (). batchUpdate (SPREADSHEET_ID, body) .execute (); }

Her lager vi en UpdateSpreadSheetPropertiesRequest objekt som spesifiserer den nye tittelen, a CopyPasteRequest objekt som inneholder kilden og målet for operasjonen, og deretter legger disse objektene til et Liste av Forespørsler.

Deretter utfører vi begge forespørslene som en batchoppdatering.

Mange andre typer forespørsler er tilgjengelige for bruk på lignende måte. For eksempel kan vi lage et nytt ark i et regneark med et AddSheetRequest eller endre verdier med en FindReplaceRequest.

Vi kan utføre andre operasjoner som å endre grenser, legge til filtre eller slå sammen celler. Hele listen over Be om typer er tilgjengelig her.

9. Konklusjon

I denne artikkelen har vi sett hvordan vi kan koble til Google Sheets API fra et Java-program og noen få eksempler på manipulering av dokumenter som er lagret i Google Sheets.

Den fulle kildekoden til eksemplene finner du på GitHub.