Interagere med Google Sheets fra Java
1. Oversigt
Google Sheets giver en praktisk måde at gemme og manipulere regneark på og samarbejde med andre om et dokument.
Nogle gange kan det være nyttigt at få adgang til disse dokumenter fra et program, f.eks. At udføre en automatisk handling. Til dette formål leverer Google Google Sheets API, som udviklere kan interagere med.
I denne artikel vi skal se på, hvordan vi kan oprette forbindelse til API'en og udføre operationer på Google Sheets.
2. Maven-afhængigheder
For at oprette forbindelse til API'en og manipulere dokumenter skal vi tilføje afhængighederne google-api-client, google-oauth-client-jetty og google-api-services-sheets:
com.google.api-klient google-api-klient 1.23.0 com.google.oauth-klient google-oauth-klient-anløbsbro 1.23.0 com.google.apis google-api-services-ark v4-rev493-1.23. 0 3. Autorisation
Google Sheets API kræver OAuth 2.0-godkendelse inden vi kan få adgang til det via en applikation.
Først skal vi få et sæt OAuth-legitimationsoplysninger og derefter bruge dette i vores ansøgning til at indsende en anmodning om godkendelse.
3.1. Opnåelse af OAuth 2.0-legitimationsoplysninger
For at opnå legitimationsoplysninger skal vi oprette et projekt i Google Developers Console og derefter aktivere Google Sheets API til projektet. Det første trin i Google Quickstart-guiden indeholder detaljerede oplysninger om, hvordan du gør dette.
Når vi har downloadet JSON-filen med legitimationsoplysninger, lad os kopiere indholdet i en google-sheets-client-secret.json fil i src / main / ressourcer katalog over vores applikation.
Indholdet af filen skal ligne dette:
{"installeret": {"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. Opnåelse af en Legitimationsoplysninger Objekt
En vellykket godkendelse returnerer a Legitimationsoplysninger objekt, som vi kan bruge til at interagere med Google Sheets API.
Lad os oprette en GoogleAuthorizeUtil klasse med en statisk bemyndige() metode, der læser indholdet af JSON-filen ovenfor og bygger en GoogleClientSecrets objekt.
Derefter opretter vi en GoogleAuthorizationCodeFlow og send godkendelsesanmodningen:
offentlig klasse GoogleAuthorizeUtil {public static Credential authorize () kaster IOException, GeneralSecurityException {// build GoogleClientSecrets from JSON file List scopes = Arrays.asList (SheetsScopes.SPREADSHEETS); // opbyg legitimationsoplysning om legitimationsobjekt; }}I vores eksempel indstiller vi SPREDEBLAD rækkevidde, da vi ønsker at få adgang til Google Sheets og bruge en in-memory DataStoreFactory for at gemme de modtagne legitimationsoplysninger. En anden mulighed er at bruge en FileDataStoreFactory for at gemme legitimationsoplysningerne i en fil.
For den fulde kildekode for GoogleAuthorizeUtil class, tjek GitHub-projektet.
4. Konstruktion af Ark Serviceinstans
For at interagere med Google Sheets har vi brug for -en Ark objekt, som er klienten til læsning og skrivning via API'en.
Lad os oprette en SheetsServiceUtil klasse, der bruger Legitimationsoplysninger objekt ovenfor for at få en forekomst af Ark:
public class 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 (), legitimationsoplysninger) .setApplicationName (APPLICATION_NAME) .build (); }}Dernæst ser vi på nogle af de mest almindelige operationer, vi kan udføre ved hjælp af API'en.
5. Skrivning af værdier på et ark
Interaktion med et eksisterende regneark kræver kendskab til det regnearks id, som vi kan finde fra dets URL.
For vores eksempler skal vi bruge et offentligt regneark kaldet "Udgifter", der ligger på:
//docs.google.com/spreadsheets/d/1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI/edit#gid=0
Baseret på denne URL kan vi identificere dette regnearks id som “1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI”.
Også, for at læse og skrive værdier, skal vi bruge regneark. værdier samlinger.
Værdierne er repræsenteret som ValueRange objekter, som er lister over lister over Java Objekter, svarende til rækker eller kolonner i et ark.
Lad os oprette en testklasse, hvor vi initialiserer vores Ark tjeneste objekt og en SPREADSHEET_ID konstant:
offentlig klasse GoogleSheetsLiveTest {private statiske ark sheetsService; privat statisk streng SPREADSHEET_ID = // ... @BeforeClass offentlig statisk ugyldig opsætning () kaster GeneralSecurityException, IOException {sheetsService = SheetsServiceUtil.getSheetsService (); }}Derefter kan vi skrive værdier ved at:
- skriver til et enkelt interval
- skriver til flere intervaller
- tilføje data efter en tabel
5.1. Skrivning til et enkelt interval
For at skrive værdier til et enkelt område på et ark bruger vi regneark (). værdier (). opdatering () 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) ("penne", "10"), Arrays.asList ("Udgifter februar"), Arrays.asList ("tøj", "20"), Arrays.asList ("sko", "5"))); UpdateValuesResponse result = sheetsService.spreadsheets (). Values () .update (SPREADSHEET_ID, "A1", body) .setValueInputOption ("RAW") .execute (); }Her opretter vi først en ValueRange objekt med flere rækker, der indeholder en liste over udgifter i to måneder.
Derefter bruger vi opdater () metode til at oprette en anmodning, der skriver værdierne til regnearket med det givne id, startende ved "A1" cellen.
For at sende anmodningen bruger vi udføre () metode.
Hvis vi ønsker, at vores værdisæt skal betragtes som kolonner i stedet for rækker, kan vi bruge setMajorDimension (“COLUMNS”) metode.
Indtastningsindstillingen “RAW” betyder, at værdierne skrives nøjagtigt, som de er, og ikke beregnes.
Når du udfører denne JUnit-test, applikationen åbner et browservindue ved hjælp af systemets standardbrowser, der beder brugeren om at logge ind og give vores applikation tilladelse til at interagere med Google Sheets på brugerens vegne:
Bemærk, at dette manuelle trin kan omgåes, hvis du har en OAuth-servicekonto.
Et krav for, at applikationen kan se eller redigere regnearket er, at den bruger, der er logget ind, har en visning eller redigeringsadgang til det. Ellers vil anmodningen resultere i en 403-fejl. Regnearket, vi bruger til vores eksempel, er indstillet til offentlig redigeringsadgang.
Hvis vi nu kontrollerer regnearket, ser vi området “A1: B6”Opdateres med vores værdisæt.
Lad os gå videre til at skrive til flere forskellige områder i en enkelt anmodning.
5.2. Skrivning til flere områder
Hvis vi vil opdatere flere intervaller på et ark, kan vi bruge en BatchUpdateValuesRequest for bedre ydeevne:
Listedata = ny ArrayList (); data.add (ny ValueRange () .setRange ("D1") .setValues (Arrays.asList (Arrays.asList ("Januar i alt", "= 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 (). Værdier () .batchUpdate (SPREADSHEET_ID, batchBody). Udfør ();I dette eksempel bygger vi først en liste over ValueRanges, hver består af to celler, der repræsenterer månedens navn og de samlede udgifter.
Derefter opretter vi en BatchUpdateValuesRequest med inputmuligheden “USER_ENTERED”, i modsætning til "RÅ", hvilket betyder, at celleværdierne beregnes ud fra formlen at tilføje to andre celler.
Endelig opretter og sender vi batchUpdate anmodning. Som et resultat blev intervallerne “D1: E1”Og“D4: E4”Opdateres.
5.3. Tilføjelse af data efter en tabel
En anden måde at skrive værdier på et ark er ved at tilføje dem i slutningen af en tabel.
Til dette kan vi bruge Tilføj() metode:
ValueRange appendBody = new ValueRange () .setValues (Arrays.asList (Arrays.asList ("Total", "= E1 + E4"))); AppendValuesResponse appendResult = sheetsService.spreadsheets (). Værdier () .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");For det første bygger vi ValueRange objekt, der indeholder de celleværdier, vi vil tilføje.
I vores tilfælde indeholder dette en celle med de samlede udgifter for begge måneder, som vi finder ved at tilføje “E1” og “E2” celleværdier.
Derefter opretter vi en anmodning, der tilføjer dataene efter tabellen, der indeholder "A1”Celle.
Det INSERT_ROWS mulighed betyder, at vi ønsker, at dataene skal føjes til en ny række og ikke erstatter eksisterende data efter tabellen. Dette betyder, at eksemplet vil skrive området “A7: B7”I sit første løb.
Ved efterfølgende kørsler starter tabellen, der starter ved “A1” cellen vil nu strække sig for at omfatte “A7: B7” række, så en ny række går til “A8: B8” række osv.
Vi er også nødt til at indstille includeValuesInResponse ejendom til sand, hvis vi vil bekræfte svaret på en anmodning. Som et resultat indeholder svarobjektet de opdaterede data.
6. Læsning af værdier fra et ark
Lad os kontrollere, at vores værdier blev skrevet korrekt ved at læse dem fra arket.
Vi kan gøre dette ved hjælp af regneark (). værdier (). get () metode til at læse et enkelt område eller batchUpdate () metode til at læse flere intervaller:
Listeområder = Arrays.asList ("E1", "E4"); BatchGetValuesResponse readResult = sheetsService.spreadsheets (). Værdier () .batchGet (SPREADSHEET_ID) .setRanges (intervaller). 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 læser vi intervallerne “E1” og “E4” og kontrollere, at de indeholder det samlede beløb for hver måned, som vi skrev før.
7. Oprettelse af nye regneark
Udover at læse og opdatere værdier kan vi også manipulere ark eller hele regneark ved hjælp af regneark () og regneark (). ark () samlinger.
Lad os se et eksempel på oprettelse af et nyt regneark:
@Test offentlig ugyldig test () kaster IOException {Regneark spreadSheet = nyt regneark (). SetProperties (nyt regnearkProperties (). SetTitle ("Mit regneark")); Regnearkresultat = sheetsService .spreadsheets () .create (spreadsheet). Udfør (); assertThat (result.getSpreadsheetId ()). erNotNull (); }Her opretter vi først en Regneark objekt med titlen “MinRegneark ” derefter bygge og sende en anmodning ved hjælp af skab() og udføre () metoder.
Det nye regneark vil være privat og placeres i den indloggede brugers drev.
8. Andre opdateringsoperationer
De fleste andre operationer har form af a Anmodning objekt, som vi derefter føjer til en liste og bruger til at bygge et BatchUpdateSpreadsheetRequest.
Lad os se, hvordan vi kan sende to anmodninger om at ændre titlen på et regneark og kopiere og indsætte et sæt celler fra et ark til et andet:
@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"); Listeanmodninger = ny ArrayList (); requests.add (ny anmodning () .setCopyPaste (copyRequest)); anmodninger.add (ny anmodning () .setUpdateSpreadsheetProperties (updateSpreadSheetRequest)); BatchUpdateSpreadsheetRequest body = ny BatchUpdateSpreadsheetRequest (). SetRequests (anmodninger); sheetsService.spreadsheets (). batchUpdate (SPREADSHEET_ID, body) .execute (); }Her opretter vi en UpdateSpreadSheetPropertiesRequest objekt, der angiver den nye titel, a CopyPasteRequest objekt, der indeholder kilden og destinationen for operationen og derefter føjer disse objekter til en Liste af Anmodninger.
Derefter udfører vi begge anmodninger som en batchopdatering.
Mange andre typer anmodninger er tilgængelige til brug på en lignende måde. For eksempel kan vi oprette et nyt ark i et regneark med et AddSheetRequest eller ændre værdier med en FindReplaceRequest.
Vi kan udføre andre operationer såsom at ændre kanter, tilføje filtre eller flette celler. Den fulde liste over Anmodning typer findes her.
9. Konklusion
I denne artikel har vi set, hvordan vi kan oprette forbindelse til Google Sheets API fra en Java-applikation og et par eksempler på manipulation af dokumenter, der er gemt i Google Sheets.
Den fulde kildekode for eksemplerne kan findes på GitHub.
