Berinteraksi dengan Google Sheets dari Java

1. Ikhtisar

Google Sheets memberikan cara yang nyaman untuk menyimpan dan memanipulasi spreadsheet dan berkolaborasi dengan orang lain pada sebuah dokumen.

Terkadang, mengakses dokumen ini dari aplikasi, misalnya, melakukan operasi otomatis dapat berguna. Untuk tujuan ini, Google menyediakan Google Sheets API yang dapat digunakan oleh pengembang.

Pada artikel ini, kita akan melihat bagaimana kita dapat terhubung ke API dan melakukan operasi di Google Sheets.

2. Ketergantungan Maven

Untuk terhubung ke API dan memanipulasi dokumen, kita perlu menambahkan dependensi google-api-client, google-oauth-client-jetty dan 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. Otorisasi

Google Sheets API memerlukan otorisasi OAuth 2.0 sebelum kami dapat mengaksesnya melalui aplikasi.

Pertama, kita perlu mendapatkan sekumpulan kredensial OAuth, lalu menggunakannya dalam aplikasi kita untuk mengirimkan permintaan otorisasi.

3.1. Mendapatkan Kredensial OAuth 2.0

Untuk mendapatkan kredensial, kita perlu membuat proyek di Google Developers Console dan kemudian mengaktifkan Google Sheets API untuk proyek tersebut. Langkah pertama dalam panduan Memulai Cepat Google berisi informasi mendetail tentang cara melakukannya.

Setelah kita mengunduh file JSON dengan informasi kredensial, mari salin konten dalam file google-sheets-client-secret.json di direktori src / main / resources aplikasi kita.

Isi file 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. Memperoleh Objek Kredensial

Otorisasi yang berhasil mengembalikan objek Kredensial yang dapat kita gunakan untuk berinteraksi dengan Google Sheets API.

Mari buat kelas GoogleAuthorizeUtil dengan metode static authorize () yang membaca konten file JSON di atas dan membuat objek GoogleClientSecrets .

Kemudian, kami akan membuat GoogleAuthorizationCodeFlow dan mengirimkan permintaan otorisasi:

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 menyetel cakupan SPREADSHEETS karena kami ingin mengakses Google Sheets dan menggunakan DataStoreFactory dalam memori untuk menyimpan kredensial yang diterima. Pilihan lainnya adalah menggunakan FileDataStoreFactory untuk menyimpan kredensial dalam sebuah file.

Untuk kode sumber penuh dari GoogleAuthorizeUtil cla ss, memeriksa proyek GitHub.

4. Membuat Mesin Virtual Layanan Spreadsheet

Untuk berinteraksi dengan Google Sheets, kita akan membutuhkan sebuah Lembar objek yang merupakan client untuk membaca dan menulis melalui API .

Mari buat kelas SheetsServiceUtil yang menggunakan objek Credential di atas untuk mendapatkan instance dari 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(); } }

Selanjutnya, kita akan melihat beberapa operasi paling umum yang dapat kita lakukan menggunakan API.

5. Menulis Nilai pada Lembaran

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

Untuk contoh kami, kami akan menggunakan spreadsheet publik yang disebut "Pengeluaran", yang terletak di:

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

Berdasarkan URL ini, kami dapat mengidentifikasi id spreadsheet ini sebagai “1sILuxZUnyl_7-MlNThjt765oWshN3Xs-PPLfqYe4DhI”.

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

Nilai direpresentasikan sebagai objek ValueRange , yang merupakan daftar dari objek Java , sesuai dengan baris atau kolom dalam lembar.

Mari buat kelas percobaan tempat kita menginisialisasi objek layanan Sheets dan konstanta 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 ke satu rentang
  • menulis ke berbagai rentang
  • menambahkan data setelah tabel

5.1. Menulis ke Single Range

Untuk menulis nilai ke satu rentang pada lembar, kita akan menggunakan metode 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(); }

Di sini, pertama-tama kita membuat objek ValueRange dengan beberapa baris yang berisi daftar pengeluaran selama dua bulan.

Kemudian, kami menggunakan metode update () untuk membuat permintaan yang menulis nilai ke spreadsheet dengan id yang diberikan, dimulai dari sel "A1".

Untuk mengirim permintaan, kami menggunakan metode execute () .

Jika kita ingin himpunan nilai kita dianggap sebagai kolom alih-alih baris, kita dapat menggunakan metode setMajorDimension ("COLUMNS") .

Opsi input “RAW” berarti nilai ditulis persis seperti aslinya, dan tidak dihitung.

Saat menjalankan pengujian JUnit ini, aplikasi akan membuka jendela browser menggunakan browser default sistem yang meminta pengguna untuk masuk dan memberikan izin kepada aplikasi kami untuk berinteraksi dengan Google Sheets atas nama pengguna:

Perhatikan bahwa langkah manual ini dapat dilewati jika Anda memiliki Akun Layanan OAuth.

Persyaratan agar aplikasi dapat melihat atau mengedit spreadsheet adalah pengguna yang masuk memiliki akses untuk melihat atau mengeditnya. Jika tidak, permintaan tersebut akan menghasilkan kesalahan 403. Spreadsheet yang kami gunakan untuk contoh kami disetel ke akses edit publik.

Sekarang, jika kita memeriksa spreadsheet, kita akan melihat kisaran " A1: B6 " diperbarui dengan kumpulan nilai kita.

Mari beralih ke menulis ke beberapa rentang yang berbeda dalam satu permintaan.

5.2. Menulis ke Beberapa Rentang

Jika kami ingin memperbarui beberapa rentang pada satu lembar, kami dapat menggunakan BatchUpdateValuesRequest untuk kinerja yang lebih baik:

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

Dalam contoh ini, pertama-tama kita membuat daftar ValueRanges, masing-masing terdiri dari dua sel yang mewakili nama bulan dan total biaya.

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.

Kami dapat melakukan operasi lain seperti mengubah batas, menambahkan filter atau menggabungkan sel. Daftar lengkap jenis Permintaan tersedia di sini.

9. Kesimpulan

Di artikel ini, kami telah melihat bagaimana kami dapat terhubung ke Google Sheets API dari aplikasi Java dan beberapa contoh memanipulasi dokumen yang disimpan di Google Sheets.

Kode sumber lengkap dari contoh-contoh tersebut dapat ditemukan di GitHub.