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

1. Pengantar

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

Yang akan Anda pelajari

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

Sebelum memulai

Ini adalah codelab kedua dalam playlist Dasar-Dasar Apps Script dengan Google Spreadsheet. Sebelum memulai, pastikan Anda telah menyelesaikan codelab pertama: Makro dan Fungsi Kustom.

Yang Anda butuhkan

• Pemahaman tentang topik dasar Apps Script yang dibahas dalam codelab sebelumnya di playlist ini.
• Pemahaman dasar tentang editor Apps Script
• Pemahaman dasar tentang Google Spreadsheet
• Kemampuan untuk membaca Spreadsheet dengan Notasi A1
• Pemahaman dasar tentang JavaScript dan class String-nya

Bagian berikutnya memperkenalkan class inti 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, sheet, dan rentang, Anda harus meninjau class induknya: SpreadsheetApp. Banyak skrip dimulai dengan memanggil metode SpreadsheetApp, karena metode ini dapat memberikan titik akses awal ke file Google Spreadsheet Anda. Anda dapat menganggap SpreadsheetApp sebagai class utama Spreadsheet service. Class SpreadsheetApp tidak dibahas secara mendalam di sini. Namun, nanti dalam codelab ini, Anda dapat menemukan contoh dan latihan untuk membantu Anda memahami class ini.

Spreadsheet, sheet, dan kelasnya

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

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

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

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

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

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

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

Anda dapat melihat kedua ID dalam contoh URL ini:

Karena ID untuk spreadsheet dan sheet 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 Anda. 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 pada kumpulan sheet dan menentukan file Google Spreadsheet di Google Drive. Class Sheet beroperasi pada setiap 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. Anda dapat menggunakan class Range untuk memilih kumpulan sel tertentu dalam sheet. Instance class ini merepresentasikan rentang—grup yang terdiri dari satu atau beberapa sel yang berdekatan dalam sheet. Anda dapat menentukan rentang berdasarkan nomor baris dan kolomnya, atau menggunakan notasi A1.

Istilah utama: Di Apps Script, sheet aktif adalah sheet yang saat ini terbuka dan terlihat di antarmuka Google Spreadsheet. Demikian pula, rentang aktif adalah grup satu atau beberapa sel yang saat ini disorot dalam sheet aktif. Sel aktif adalah satu sel yang disorot 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 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 tersebut di antarmuka Google Spreadsheet, sehingga memindahkan fokus kursor ke lokasi tersebut.

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

3. Siapkan

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

Salinan spreadsheet contoh untuk Anda gunakan ditempatkan di folder Google Drive Anda dan diberi nama "Salinan 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 kualitas 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 ini mengajarkan cara mengganti nama spreadsheet dan menduplikasi sheet dalam spreadsheet.

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

Mengganti nama spreadsheet aktif

Misalnya, Anda ingin mengubah nama default, "Salinan 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 alpukat" sebagai nama project baru, lalu klik Ganti nama.
4. Untuk menjalankan skrip, pilih renameSpreadsheet dari daftar fungsi, lalu klik Run.
5. Beri 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 Anda menggunakan akun gmail.com, Anda mungkin mendapatkan dialog aplikasi yang belum diverifikasi saat pertama kali menggunakan skrip. Google menggunakan informasi ini untuk memperingatkan pengguna yang mungkin menggunakan kode dari penulis yang tidak dikenal atau tidak tepercaya. Jika Anda melihat dialog ini, Anda dapat melanjutkan karena Anda adalah penulis skrip. Ikuti perintah di layar untuk melanjutkan pemberian otorisasi skrip.

Setelah fungsi dijalankan, 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) di mySS akan mengubah nama file spreadsheet di Google Drive menjadi "2017 Avocado Prices in Portland, Seattle".

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

Menduplikasi 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 di 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 Run.

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

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

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

5. Memformat sheet dengan class Sheet

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

Catatan**: Class Spreadsheet** dan Sheet memiliki beberapa metode khusus sheet yang sama, 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 ini akan menghemat waktu Anda.

Mengubah 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 sesuai 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 dibuat dan diganti namanya saat Anda menjalankan fungsi:

Dalam 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, kita dapat memperbarui fungsi duplicateAndOrganizeActiveSheet() Anda untuk mengubah ukuran kolom pada sheet duplikat, dan menambahkan baris yang dibekukan:

1. Di Google Spreadsheet, klik sheet Sheet_Original untuk mengaktifkannya.
2. Di Apps Script, ubah fungsi duplicateAndOrganizeActiveSheet() agar sesuai 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), yang membuat baris header tetap terlihat saat pembaca men-scroll ke bawah spreadsheet.

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

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

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

6. Menyusun 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 menggunakan rentang di Apps Script, sementara codelab lain dalam playlist ini membahas lebih mendalam manipulasi dan pemformatan data.

Memindahkan rentang

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

Mari kita perbarui metode duplicateAndOrganizeActiveSheet() Anda untuk memindahkan beberapa data:

1. Di Google Spreadsheet, klik sheet Sheet_Original untuk mengaktifkannya.
2. Di Apps Script, ubah fungsi duplicateAndOrganizeActiveSheet() agar sesuai 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, lembar 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 rentang yang ditentukan ada, metode getRange(a1Notation) akan menampilkan instance Range-nya. Kode menyimpan instance dalam variabel myRange agar mudah digunakan.

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

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

Mengurutkan 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 sesuai 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, selain format sebelumnya, fungsi ini 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 akan diurutkan dari terendah hingga tertinggi (nilai menaik). Metode sort(sortSpecObj) dapat melakukan persyaratan pengurutan yang lebih kompleks, tetapi Anda tidak memerlukannya di sini. Anda dapat melihat semua cara berbeda untuk memanggil rentang pengurutan dalam dokumentasi referensi metode.

Selamat, Anda telah berhasil menyelesaikan semua latihan dalam codelab ini. Bagian berikutnya meninjau poin-poin penting dari 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 serta istilah penting layanan Spreadsheet di Apps Script.

Anda siap melanjutkan ke codelab berikutnya.

Apakah codelab ini bermanfaat bagi Anda?

Ya Tidak

Yang telah kita bahas

• Cara spreadsheet, sheet, dan rentang direpresentasikan di Apps Script.
• Beberapa penggunaan dasar class SpreadsheetApp, Spreadsheet, Sheet, dan Range.

Konsep Utama:

1. Di 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, rentang, sel yang sedang terbuka dianggap aktif. Banyak metode Apps Script yang mengakses atau mengubah elemen aktif.
3. Class Spreadsheet dan Sheet memiliki beberapa metode khusus sheet. Metode duplikat di class Spreadsheet hanya untuk kemudahan 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 sheet menggunakan ID. Anda dapat mempelajari ID dan URL lebih lanjut di Pengantar Google Spreadsheet.

Istilah Utama:

• Aktif (status): Menunjukkan bahwa spreadsheet, sheet, rentang, atau sel yang ditentukan adalah yang sedang dilihat atau ditandai oleh pengguna spreadsheet.
• Sel aktif: Sel tunggal yang disorot dalam sheet aktif dengan fokus kursor.
• Rentang aktif: Sekelompok satu atau beberapa sel yang saat ini ditandai dalam sheet aktif.
• Otorisasi: Proses pemberian izin oleh pengguna untuk mengizinkan Apps Script mengakses data pengguna di layanan Google yang relevan atau mengambil tindakan atas nama pengguna.
• Skrip terikat penampung: Skrip apa pun yang terikat ke dan dibuat dari dokumen Google Workspace, seperti Spreadsheet Google atau Dokumen Google.
• Rentang:** Sekelompok 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 pada 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 memberikan titik awal untuk kode yang membaca atau memanipulasi data Google Spreadsheet.
• Spreadsheet**: File Google Spreadsheet yang ada 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.

Langkah berikutnya

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

Temukan codelab berikutnya di Bekerja dengan data.