Dasar-Dasar Apps Script dengan Google Spreadsheet #2: Spreadsheet, Spreadsheet, dan Rentang

1. Pengantar

Selamat datang di bagian kedua playlist codelab Dasar Aplikasi Apps dengan Google Spreadsheet. Codelab sebelumnya berfokus pada konsep editor skrip, makro, dan fungsi kustom. Codelab ini mempelajari layanan Spreadsheet yang dapat Anda gunakan untuk membaca, menulis, dan memanipulasi data di Google Spreadsheet.

Yang akan Anda pelajari

• Bagaimana spreadsheet, sheet, dan rentang diwakili dalam Apps Script.
• Cara mengakses, membuat, dan mengganti nama spreadsheet (terbuka) aktif dengan class SpreadsheetApp dan Spreadsheet.
• Cara mengubah nama sheet dan orientasi kolom/baris rentang, menggunakan class Sheet.
• Cara menentukan, mengaktifkan, memindahkan, dan mengurutkan grup sel atau rentang data menggunakan class Range.

Sebelum memulai

Ini adalah codelab kedua dalam playlist Fundamentals of Apps Script dengan Google Spreadsheet. Sebelum memulai, pastikan untuk menyelesaikan codelab pertama: Makro dan Fungsi Kustom.

Yang Anda butuhkan

• Pemahaman tentang topik Apps Script dasar yang dijelaskan dalam codelab sebelumnya dari playlist ini.
• Pemahaman dasar tentang editor Apps Script
• Pemahaman dasar tentang Google Spreadsheet
• Kemampuan untuk membaca Notasi A1 Spreadsheet
• Pemahaman dasar tentang JavaScript dan class String

Bagian berikutnya memperkenalkan class inti dari layanan Spreadsheet.

2. Pengantar layanan Spreadsheet

Empat class mencakup dasar layanan Spreadsheet: SpreadsheetApp, Spreadsheet, Sheet, dan Range. Bagian ini menjelaskan class tersebut dan kegunaannya.

Class SpreadsheetApp

Sebelum mempelajari spreadsheet, spreadsheet, dan rentang, Anda harus meninjau kelas induknya: SpreadsheetApp. Banyak skrip dimulai dengan memanggil metode SpreadsheetApp, karena dapat memberikan titik akses awal ke file Google Spreadsheet Anda. Anda dapat menganggap SpreadsheetApp sebagai class utama Layanan spreadsheet. Class SpreadsheetApp tidak dipelajari secara mendalam di sini. Namun, dalam codelab ini, Anda dapat menemukan contoh dan latihan untuk membantu Anda memahami class ini.

Spreadsheet, spreadsheet, dan kelasnya

Sebagai istilah Spreadsheet, spreadsheet adalah file Google Spreadsheet (disimpan di Google Drive) yang berisi data yang diatur menurut baris dan kolom. Spreadsheet terkadang disebut sebagai ‘Google Spreadsheet' dengan cara yang sama seperti dokumen disebut sebagai ‘Dokumen Google'.

Anda dapat menggunakan class Spreadsheet untuk mengakses dan mengubah data file Google Spreadsheet. Anda juga dapat menggunakan class ini untuk operasi level file lainnya, seperti menambahkan kolaborator.

Sheet** mewakili halaman individual spreadsheet, terkadang disebut sebagai "tab." Setiap spreadsheet dapat berisi satu atau beberapa sheet. Anda dapat menggunakan class Sheet** untuk mengakses serta mengubah data dan setelan tingkat sheet, seperti memindahkan baris atau kolom data.

Poin Penting: Selain nama, spreadsheet juga memiliki string ID unik untuk sheet tersebut. ID ada di URL spreadsheet dalam format:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=0

Setiap sheet di spreadsheet juga memiliki nomor ID unik untuk spreadsheet tersebut. Nomor ID ini diacak, tapi Anda juga dapat melihatnya di URL spreadsheet dalam format:

https://docs.google.com/spreadsheets/d/spreadsheetID/edit#gid=sheetID

Anda dapat melihat kedua ID di URL contoh ini:

Karena ID spreadsheet dan spreadsheet bersifat unik (dan nama spreadsheet tidak), sebaiknya gunakan ID untuk menentukan spreadsheet dan sheet jika memungkinkan. Misalnya, Anda dapat memiliki beberapa spreadsheet dengan nama yang sama di Google Drive. Dengan menggunakan metode skrip seperti SpreadsheetApp.openById(id) untuk membuka spreadsheet, Anda dapat menghindari pengeditan file yang salah secara tidak sengaja.

Pelajari lebih lanjut ID di Google Spreadsheet dengan melihat Pengantar Google Spreadsheet.

Singkatnya, class Spreadsheet beroperasi di koleksi sheet dan menentukan file Google Spreadsheet di Google Drive. Class Sheet beroperasi di masing-masing sheet dalam spreadsheet.

Class Range

Sebagian besar operasi manipulasi data (misalnya, membaca, menulis, atau memformat data sel) mengharuskan Anda menentukan sel yang akan diterapkan operasi tersebut. Anda dapat menggunakan class Range untuk memilih kumpulan sel tertentu dalam sheet. Instance class ini merepresentasikan rentang—grup dari satu atau beberapa sel yang berdekatan di sheet. Anda dapat menentukan rentang berdasarkan nomor baris dan kolomnya, atau menggunakan notasi A1.

Istilah kunci: Di Apps Script, sheet aktif adalah sheet yang saat ini terbuka dan terlihat di antarmuka Google Spreadsheet. Demikian pula, rentang aktif adalah grup dari satu atau beberapa sel yang saat ini disorot dalam sheet aktif. Sel aktif adalah satu sel yang ditandai dalam sheet aktif dengan fokus kursor.

Banyak metode class menampilkan objek aktif dari class yang diminta. Misalnya, menjalankan SpreadsheetApp.getActiveSpreadsheet() akan menampilkan spreadsheet aktif atau saat ini milik pengguna. Dengan mendapatkan spreadsheet yang aktif, Anda dapat menerapkan operasi ke spreadsheet tersebut. Demikian pula, metode seperti metode Sheet.activate() dapat mengubah objek aktif.

Mengaktifkan sheet, rentang, atau sel sama dengan mengklik sheet, rentang, atau sel di antarmuka Google Spreadsheet, sehingga memindahkan fokus fokus ke lokasi tersebut.

Bagian codelab lainnya menunjukkan contoh skrip yang berfungsi dengan class ini dan metodenya.

3. Siapkan

Sebelum melanjutkan, Anda memerlukan spreadsheet yang berisi beberapa data. Kami telah menyediakannya untuk Anda: klik link ini untuk menyalin sheet data, lalu klik Buat salinan.

Salinan contoh spreadsheet yang dapat Anda gunakan ditempatkan dalam folder Google Drive Anda dan diberi nama "Salin spreadsheet Tanpa Judul." Gunakan spreadsheet ini untuk menyelesaikan latihan codelab ini.

Sebagai pengingat, Anda dapat membuka editor skrip dari Google Spreadsheet dengan mengklik Ekstensi > Apps Script.

Saat membuka project Apps Script di editor skrip untuk pertama kalinya, editor skrip akan membuat project skrip dan file skrip untuk Anda.

Bagian berikutnya menunjukkan cara menggunakan class Spreadsheet untuk meningkatkan spreadsheet ini.

4. Mengakses dan mengubah spreadsheet

Di bagian ini, Anda dapat mempelajari cara menggunakan class SpreadsheetApp dan Spreadsheet untuk mengakses dan mengubah spreadsheet. Secara khusus, latihan mengajarkan cara mengganti nama spreadsheet dan menduplikasi sheet dalam spreadsheet.

Ini adalah operasi sederhana, tetapi sering kali menjadi bagian dari alur kerja yang lebih besar dan lebih kompleks. Setelah Anda memahami cara mengotomatiskan tugas-tugas tersebut dengan kode skrip, akan lebih mudah untuk mempelajari cara mengotomatiskan operasi yang lebih rumit.

Mengganti nama spreadsheet yang aktif

Misalnya Anda ingin mengubah nama default, "Salin spreadsheet Tanpa Judul" menjadi judul yang lebih mencerminkan tujuan spreadsheet Anda. Anda dapat melakukannya dengan class SpreadsheetApp dan Spreadsheet.

1. Di editor skrip, ganti blok kode myFunction() default dengan kode berikut:

function renameSpreadsheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  mySS.rename("2017 Avocado Prices in Portland, Seattle");
}

2. Untuk menyimpan skrip, klik Simpan .
3. Untuk mengganti nama project Apps Script, klik Project tanpa judul, masukkan "Harga Avocado" sebagai nama project baru, lalu klik Ganti nama.
4. Untuk menjalankan skrip, pilih renameSpreadsheet dari daftar fungsi lalu klik Jalankan.
5. Otorisasi makro dengan mengikuti petunjuk di layar. Jika Anda mendapatkan pesan "Aplikasi ini tidak diverifikasi" klik Lanjutan, lalu klik Buka harga Alpukat (tidak aman). Di layar berikutnya, klik Izinkan.

Catatan: Jika menggunakan akun gmail.com, Anda mungkin mendapatkan dialog aplikasi yang belum diverifikasi saat pertama kali menggunakan skrip. Google menggunakan ini untuk memperingatkan pengguna yang mungkin menggunakan kode dari penulis yang tidak dikenal atau tidak tepercaya. Jika Anda melihat dialog ini, tidak apa-apa untuk melanjutkan karena Anda adalah penulis skripnya. Ikuti petunjuk di layar untuk terus mengotorisasi skrip.

Setelah fungsi dieksekusi, nama file spreadsheet Anda akan berubah:

Mari kita lihat kode yang Anda masukkan. Metode getActiveSpreadsheet() menampilkan objek yang merepresentasikan spreadsheet aktif; yaitu, salinan spreadsheet latihan yang Anda buat. Objek spreadsheet ini disimpan dalam variabel mySS. Memanggil rename(newName) pada mySS akan mengubah nama file spreadsheet di Google Drive menjadi "Harga Alpukat 2017 di Portland, Seattle."

Karena variabel mySS adalah referensi ke spreadsheet, Anda dapat membuat kode lebih bersih dan lebih efisien dengan memanggil metode Spreadsheet di mySS, bukan memanggil getActiveSpreadsheet() berulang kali.

Menduplikasikan sheet aktif

Di spreadsheet saat ini, Anda hanya memiliki satu sheet. Anda dapat memanggil metode Spreadsheet.duplicateActiveSheet() untuk membuat salinan sheet:

1. Tambahkan fungsi baru berikut di bawah fungsi renameSpreadsheet() yang sudah ada dalam project skrip Anda:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();
}

2. Simpan project skrip Anda.
3. Untuk menjalankan skrip, pilih duplicateAndOrganizeActiveSheet dari daftar fungsi lalu klik Jalankan.

Kembali ke Spreadsheet untuk melihat tab "Salinan Sheet_Original" sheet baru yang ditambahkan ke spreadsheet Anda.

Dalam fungsi baru ini, metode duplicateActiveSheet() akan membuat, mengaktifkan, dan menampilkan sheet duplikat di spreadsheet Anda. Sheet yang dihasilkan ini disimpan dalam duplicateSheet, namun kodenya belum melakukan apa pun terhadap variabel tersebut.

Di bagian berikutnya, Anda akan menggunakan class Sheet untuk mengganti nama dan memformat sheet duplikat.

5. Memformat sheet dengan class Spreadsheet

Class Sheet menyediakan metode yang memungkinkan skrip membaca dan memperbarui sheet. Di bagian ini, Anda dapat mempelajari cara mengubah nama dan lebar kolom sheet dengan metode dari class Sheet.

Catatan**: Class Spreadsheet** dan Sheet memiliki beberapa metode khusus sheet umum, seperti Spreadsheet.deleteColumn(columnPosition) dan Sheet.deleteColumn(columnPosition).

Saat Anda memanggil Spreadsheet.deleteColumn(columnPosition), metode ini akan menghapus kolom dari sheet yang saat ini aktif (terbuka) dalam spreadsheet. Anda bisa mendapatkan hasil yang sama dengan memanggil getActiveSheet(), lalu Sheet.deleteColumn(columnPosition). Versi Spreadsheet hanya menghemat waktu Anda.

Ubah nama sheet

Mengganti nama sheet semudah mengganti nama spreadsheet di renameSpreadsheet(). Hanya satu panggilan metode yang diperlukan.

1. Di Google Spreadsheet, klik sheet Sheet_Original untuk mengaktifkannya.
2. Di Apps Script, ubah fungsi duplicateAndOrganizeActiveSheet() agar cocok dengan yang berikut:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());
}

3. Simpan dan jalankan fungsi.

Di Google Spreadsheet, sheet duplikat akan dibuat dan diganti namanya saat Anda menjalankan fungsi:

Pada kode yang ditambahkan, metode setName(name) mengubah nama duplicateSheet, menggunakan getSheetID() untuk mendapatkan nomor ID unik sheet. Operator + menggabungkan ID sheet ke akhir string "Sheet_".

Mengubah kolom dan baris sheet

Anda juga dapat menggunakan class Sheet untuk memformat sheet. Misalnya, Anda dapat memperbarui fungsi duplicateAndOrganizeActiveSheet() untuk mengubah ukuran kolom duplikat, serta menambahkan baris beku:

1. Di Google Spreadsheet, klik sheet Sheet_Original untuk mengaktifkannya.
2. Di Apps Script, ubah fungsi duplicateAndOrganizeActiveSheet() agar cocok dengan yang berikut:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);
}

3. Simpan dan jalankan fungsi.

Di Google Spreadsheet, sheet duplikat dibuat, diganti namanya, diaktifkan, dan diformat:

Kode yang Anda tambahkan menggunakan autoResizeColumns(startColumn, numColumns) untuk mengubah ukuran kolom sheet agar mudah dibaca. Metode setFrozenRows(rows) membekukan jumlah baris yang ditentukan (dalam hal ini, dua baris) yang membuat baris header tetap terlihat saat pembaca men-scroll ke bawah spreadsheet.

Catatan: Saat menangani array di Apps Script, Anda mengikuti konvensi bahwa daftar dimulai pada indeks 0. Namun, saat menggunakan baris dan kolom, indeks dimulai dari 1. Logika ini selaras dengan format spreadsheet konvensional yang dimulai dari baris 1 dan kolom A di Spreadsheet.

Berhati-hatilah saat menggunakan metode yang berkaitan dengan kolom dan baris seperti autoResizeColumns(startColumn, numColumns). Dalam latihan kami saat ini, Anda memasukkan 1 (bukan 0) sebagai parameter untuk mereferensikan kolom A dari duplicateSheet.

Di bagian berikutnya, Anda akan mempelajari rentang dan manipulasi data dasar.

6. Mengatur ulang data dengan class Range

Class Range dan metodenya menyediakan sebagian besar opsi manipulasi dan pemformatan data di layanan Spreadsheet.

Bagian ini memperkenalkan manipulasi data dasar dengan rentang. Latihan ini berfokus pada cara memanfaatkan rentang di Apps Script sementara codelab lain dalam playlist ini memperdalam manipulasi data dan pemformatan data.

Pindahkan rentang

Anda dapat mengaktifkan dan memindahkan rentang data dengan metode class dan notasi A1, singkatan untuk mengidentifikasi kumpulan sel tertentu dalam spreadsheet. Jika ingin membiasakan diri kembali, Anda dapat melihat deskripsi notasi A1 ini.

Mari perbarui metode duplicateAndOrganizeActiveSheet() untuk memindahkan beberapa data:

1. Di Google Spreadsheet, klik sheet Sheet_Original untuk mengaktifkannya.
2. Di Apps Script, ubah fungsi duplicateAndOrganizeActiveSheet() agar cocok dengan yang berikut:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));
}

3. Simpan dan jalankan fungsi.

Saat Anda menjalankan fungsi ini, sheet duplikat akan dibuat, diaktifkan, dan diformat. Selain itu, konten kolom F dipindahkan ke kolom C:

Kode baru menggunakan metode getRange(a1Notation) untuk mengidentifikasi rentang data yang akan dipindahkan. Dengan memasukkan notasi A1 "F2:F" sebagai parameter metode, Anda menentukan kolom F (tidak termasuk F1). Jika ada rentang yang ditentukan, metode getRange(a1Notation) akan menampilkan instance Range. Kode menyimpan instance dalam variabel myRange untuk kemudahan penggunaan.

Setelah rentang diidentifikasi, metode moveTo(target) akan mengambil konten myRange (nilai dan format) lalu memindahkannya. Tujuan (kolom C) ditentukan menggunakan notasi A1 "C2". Ini adalah satu sel, bukan satu kolom. Saat memindahkan data, Anda tidak perlu mencocokkan ukuran dengan rentang target dan tujuan. Apps Script hanya menyejajarkan sel pertama masing-masing.

Catatan: Saat menulis skrip sendiri, pastikan untuk mengujinya secara menyeluruh untuk memastikan data Anda berakhir di tempat yang Anda inginkan. Apps Script akan menimpa nilai data yang ada saat mengubah nilai sel. Jika tidak berhati-hati, Anda dapat menimpa data yang ingin disimpan. Dalam contoh ini, kolom C kosong, tetapi jika ada data, metode moveTo(target) akan menghapusnya.

Urutkan rentang

Class Range memungkinkan Anda membaca, memperbarui, dan mengatur grup sel. Misalnya, Anda dapat mengurutkan rentang data menggunakan metode Range.sort(sortSpecObj):

1. Di Google Spreadsheet, klik sheet Sheet_Original untuk mengaktifkannya.
2. Di Apps Script, ubah fungsi duplicateAndOrganizeActiveSheet() agar cocok dengan yang berikut:

function duplicateAndOrganizeActiveSheet() {
  var mySS = SpreadsheetApp.getActiveSpreadsheet();
  var duplicateSheet = mySS.duplicateActiveSheet();

  // Change the name of the new sheet.
  duplicateSheet.setName("Sheet_" + duplicateSheet.getSheetId());

  // Format the new sheet.
  duplicateSheet.autoResizeColumns(1, 5);
  duplicateSheet.setFrozenRows(2);

  // Move column F to column C.
  var myRange = duplicateSheet.getRange("F2:F");
  myRange.moveTo(duplicateSheet.getRange("C2"));

  // Sort all the data using column C (Price information).
  myRange = duplicateSheet.getRange("A3:D55");
  myRange.sort(3);
}

3. Simpan dan jalankan fungsi.

Sekarang fungsi, selain format sebelumnya, mengurutkan semua data dalam tabel menggunakan informasi harga di kolom C:

Kode baru menggunakan getRange(a1Notation) untuk menentukan rentang baru yang mencakup A3:D55 (seluruh tabel tidak termasuk header kolom). Kode kemudian memanggil metode sort(sortSpecObj) untuk mengurutkan tabel. Di sini, parameter sortSpecObj adalah nomor kolom yang akan diurutkan. Metode ini mengurutkan rentang sehingga nilai kolom yang ditunjukkan pergi dari nilai terendah ke tertinggi (nilai naik). Metode sort(sortSpecObj) dapat menjalankan persyaratan pengurutan yang lebih kompleks, tetapi Anda tidak memerlukannya di sini. Anda dapat melihat semua cara berbeda untuk memanggil rentang pengurutan di dokumentasi referensi metode.

Selamat, Anda berhasil menyelesaikan semua latihan di codelab. Bagian berikutnya meninjau poin-poin utama codelab ini dan melihat pratinjau codelab berikutnya dalam playlist ini.

7. Kesimpulan

Anda telah mencapai akhir codelab ini. Anda kini dapat menggunakan dan menentukan class dan persyaratan penting dari layanan Spreadsheet di Apps Script.

Anda siap untuk beralih ke codelab berikutnya.

Apakah codelab ini bermanfaat bagi Anda?

Ya Tidak

Yang telah kita bahas

• Bagaimana spreadsheet, sheet, dan rentang diwakili dalam Apps Script.
• Beberapa penggunaan dasar dari class SpreadsheetApp, Spreadsheet, Sheet, dan Range.

Konsep Utama:

1. Dalam Apps Script, semua array mengikuti konvensi bahwa daftar dimulai pada indeks 0. Namun, baris dan kolom diindeks mulai dari 1.
2. Di Apps Script, sheet yang saat ini terbuka, rentang yang ditandai, sel dianggap aktif. Banyak metode Apps Script mengakses atau memodifikasi elemen aktif.
3. Class Spreadsheet dan Sheet berbagi beberapa metode khusus sheet. Metode duplikat di class Spreadsheet hanya ditujukan untuk memudahkan pengguna karena biasanya menargetkan sheet aktif saat dipanggil.
4. Setiap spreadsheet dan sheet memiliki ID unik, yang dapat Anda temukan di URL browser. Metode class spreadsheet dan spreadsheet menggunakan ID. Anda dapat mempelajari ID dan URL lebih lanjut di Pengantar Google Spreadsheet.

Istilah Utama:

• Aktif (status): Menunjukkan spreadsheet, sheet, rentang, atau sel yang ditentukan adalah spreadsheet yang sedang dilihat atau disoroti oleh pengguna spreadsheet.
• Sel aktif: Sel tunggal yang ditandai dalam sheet aktif dengan fokus kursor.
• Rentang aktif: Grup satu atau beberapa sel yang saat ini ditandai dalam sheet aktif.
• Otorisasi: Proses pengguna memberikan izin untuk mengizinkan Apps Script mengakses data pengguna di layanan Google yang relevan atau melakukan tindakan atas nama pengguna.
• Skrip yang terikat dengan penampung: Skrip apa pun yang terikat dan dibuat dari dokumen Google Workspace, seperti Google Spreadsheet atau Google Dokumen.
• Rentang:** Grup dari satu atau beberapa sel yang berdekatan dalam sheet. Class Range** memberi Anda kemampuan untuk membaca dan memperbarui rentang dalam sheet.
• Editor skrip: Editor kode dalam browser untuk Apps Script.
• Sheet**: Satu halaman spreadsheet (mirip dengan workbook Microsoft Excel). Terkadang digunakan untuk merujuk ke file Google Spreadsheet dalam Google Drive. Class Sheet** memungkinkan Anda mengakses dan mengubah sheet.
• SpreadsheetApp: Class ini berfungsi sebagai class induk untuk layanan Spreadsheet dan menyediakan titik awal untuk kode yang membaca atau memanipulasi data Google Spreadsheet.
• Spreadsheet**: File Google Spreadsheet yang berada di Google Drive. Class Spreadsheet** memungkinkan Anda mengakses dan mengubah spreadsheet.
• Layanan spreadsheet: Layanan Apps Script yang memungkinkan skrip membuat, mengakses, dan mengubah file Google Spreadsheet.

Apa selanjutnya

Codelab berikutnya dalam playlist ini membahas lebih dalam cara membaca, menulis, dan mengubah data dalam spreadsheet.

Temukan codelab berikutnya di Menangani data.