Berinteraksi dengan Helaian Google dari Java

1. Gambaran keseluruhan

Helaian Google menyediakan cara mudah untuk menyimpan dan memanipulasi hamparan dan berkolaborasi dengan orang lain pada dokumen.

Kadang-kadang, berguna untuk mengakses dokumen ini dari aplikasi, katakanlah untuk melakukan operasi automatik. Untuk tujuan ini, Google menyediakan API Helaian Google yang dapat berinteraksi dengan pemaju.

Dalam artikel ini, kita akan melihat bagaimana kita dapat menyambung ke API dan melakukan operasi di Helaian Google.

2. Pergantungan Maven

Untuk menyambung ke API dan memanipulasi dokumen, kami perlu menambahkan pergantungan google-api-client, google-oauth-client-jetty dan 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. Kebenaran

API Helaian Google memerlukan kebenaran OAuth 2.0 sebelum kami dapat mengaksesnya melalui aplikasi.

Pertama, kita perlu mendapatkan satu set kelayakan OAuth, kemudian gunakan ini dalam aplikasi kita untuk mengemukakan permintaan untuk mendapatkan kebenaran.

3.1. Memperolehi kelayakan OAuth 2.0

Untuk mendapatkan kelayakan, kita perlu membuat projek di Google Developers Console dan kemudian mengaktifkan Google Sheets API untuk projek tersebut. Langkah pertama dalam panduan Google Quickstart mengandungi maklumat terperinci mengenai cara melakukannya.

Sebaik sahaja kami memuat turun fail JSON dengan maklumat kelayakan, mari salin kandungannya dalam fail google-sheets-client-secret.json di direktori src / main / resource aplikasi kami.

Kandungan fail harus serupa dengan ini:

{ "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. Mendapatkan Kelayakan Objek

Kebenaran yang berjaya mengembalikan objek Kredensial yang dapat kita gunakan untuk berinteraksi dengan API Helaian Google.

Mari buat kelas GoogleAuthorizeUtil dengan kaedah static authorize () yang membaca kandungan fail JSON di atas dan membina objek GoogleClientSecrets .

Kemudian, kami akan membuat GoogleAuthorizationCodeFlow dan menghantar permintaan kebenaran:

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

Dalam contoh kami, kami menetapkan skop SPREADSHEETS kerana kami ingin mengakses Helaian Google dan menggunakan DataStoreFactory dalam memori untuk menyimpan kelayakan yang diterima. Pilihan lain adalah menggunakan FileDataStoreFactory untuk menyimpan kelayakan dalam fail.

Untuk kod sumber penuh GoogleAuthorizeUtil kera ss, daftar projek GitHub itu.

4. Membina Contoh Perkhidmatan Helaian

Untuk berinteraksi dengan Helaian Google, kita perlu satu Helaian objek yang merupakan pelanggan untuk membaca dan menulis melalui API .

Mari buat kelas SheetsServiceUtil yang menggunakan objek Kredensial di atas untuk mendapatkan contoh Helaian:

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

Seterusnya, kita akan melihat beberapa operasi yang paling biasa yang dapat kita lakukan menggunakan API.

5. Menulis Nilai pada Lembaran

Berinteraksi dengan spreadsheet yang ada memerlukan mengetahui id spreadsheet itu, yang dapat kita temukan dari URL-nya.

Sebagai contoh, kami akan menggunakan hamparan umum yang disebut "Perbelanjaan", yang terletak di:

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

Berdasarkan URL ini, kami dapat mengenal pasti id spreadsheet ini sebagai "1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI".

Juga, untuk membaca dan menulis nilai, kita akan menggunakan koleksi spreadsheets.values .

Nilai ditunjukkan sebagai objek ValueRange , yang merupakan daftar daftar Objek Java , sesuai dengan baris atau lajur dalam lembaran.

Mari buat kelas ujian di mana kami memulakan objek perkhidmatan Helaian kami dan pemalar 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(); } }

Kemudian, kita dapat menulis nilai dengan:

  • menulis kepada julat tunggal
  • menulis kepada pelbagai julat
  • melampirkan data selepas jadual

5.1. Menulis untuk Julat Tunggal

Untuk menulis nilai ke julat tunggal pada helaian, kami akan menggunakan kaedah spreadsheet (). 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(); }

Di sini, pertama-tama kami membuat objek ValueRange dengan beberapa baris yang mengandungi senarai perbelanjaan selama dua bulan.

Kemudian, kami menggunakan kaedah kemas kini () untuk membangun permintaan yang menulis nilai ke spreadsheet dengan id yang diberikan, bermula dari sel "A1".

Untuk menghantar permintaan, kami menggunakan kaedah execute () .

Sekiranya kita mahu set nilai kita dianggap sebagai lajur dan bukannya baris, kita dapat menggunakan kaedah setMajorDimension ("COLUMNS") .

Pilihan input "RAW" bermaksud nilainya ditulis dengan tepat sebagaimana mestinya, dan tidak dihitung.

Semasa menjalankan ujian JUnit ini, aplikasi akan membuka tetingkap penyemak imbas menggunakan penyemak imbas lalai sistem yang meminta pengguna untuk masuk dan memberi izin aplikasi kami untuk berinteraksi dengan Helaian Google bagi pihak pengguna:

Perhatikan bahawa langkah manual ini dapat dilewati jika anda mempunyai Akaun Perkhidmatan 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.

Kita boleh melakukan operasi lain seperti menukar sempadan, menambah penapis atau menggabungkan sel. Senarai lengkap jenis Permintaan boleh didapati di sini.

9. Kesimpulannya

Dalam artikel ini, kami telah melihat bagaimana kami dapat menyambung ke Google Spreadsheet dari aplikasi Java dan beberapa contoh memanipulasi dokumen yang disimpan di Google Spreadsheet.

Kod sumber lengkap contoh boleh didapati di GitHub.