Panduan Pemula Script Google Sheets

Bagaimana-Untuk

OlehRyan Dube

Terakhir diperbarui saat6 Agustus 2020

Jika Anda menggunakan Google Sheets, Anda mungkin tertarik untuk mengetahui bahwa Google Script dapat sangat meningkatkan spreadsheet Anda.

Pikirkan seperti Google Script VBA di Microsoft Excel. Ini memungkinkan Anda menambahkan kode ke spreadsheet Anda untuk mengotomatiskan entri data. Anda juga dapat menggunakannya untuk membawa data eksternal ke lembar Anda. Yang terbaik dari semuanya, Anda dapat menjalankan perhitungan pada jadwal berkala, bahkan ketika Anda tidak memiliki spreadsheet yang terbuka.

Di artikel ini, Anda akan belajar cara mengaktifkan Google Script dan cara menulis dan menjalankan skrip sederhana.

Cara Menggunakan Google Script

Ada beberapa hal di Google Sheets yang perlu Anda aktifkan sebelum Anda dapat mulai menulis Google Script di sana.

Pilih menu Tools dan pilih Editor skrip dari menu.

Ini akan membuka editor Google Scripts di jendela proyek Untitled.

Skrip ini sebenarnya terdapat di dalam akun Google Anda di dalam alat Google Scripts. Proyek khusus ini terhubung ke spreadsheet yang sedang Anda kerjakan.

Anda dapat menulis skrip, tetapi tidak dapat menjalankannya sampai skrip benar-benar diaktifkan di sheet. Anda harus melakukan ini saat pertama kali menjalankannya (seperti yang akan Anda lihat di bawah).

Untuk melihat ini dalam tindakan, mari buat skrip sederhana.

Buat Google Script Pertama Anda

Dalam contoh ini, Anda akan menulis skrip yang menambahkan semua kuis dan nilai ujian akhir untuk kolom B (skor Chris).

Ini adalah contoh spreadsheet.

Kembali ke jendela Google Scripts, rekatkan kode berikut di dalam fungsi kurung kurawal myfunction (), seperti:


function myFunction () {
var ss = SpreadsheetApp.getActiveSpreadsheet ();
var sheet = ss.getSheets () [0] var range = sheet.getRange (2,2,5);
nilai var = range.getValues ​​();
jumlah var = 0
untuk (var row dalam nilai) {
untuk (var col dalam nilai [baris]) {
jumlah = nilai [baris] [kolom] + jumlah;
}
}
sheet.getRange (7,2) .setValue (jumlah);
}


Kode ini mungkin terlihat rumit, tetapi mari kita uraikan persis apa yang dilakukannya. Ini lebih sederhana daripada yang terlihat.

Mari pecahkan kode ini satu demi satu.

Mendefinisikan Variabel

1. var ss = SpreadsheetApp.getActiveSpreadsheet ();

Baris ini mengambil spreadsheet saat ini yang aktif. Karena Anda hanya menjalankan skrip ini ketika spreadsheet yang ingin Anda jalankan penghitungannya aktif, itu akan selalu mendapatkan spreadsheet yang benar. Lembar akan disimpan sebagai variabel "objek" yang disebut "ss".

2. var sheet = ss.getSheets () [0]

Ini mendapatkan lembar pertama dan menyimpannya sebagai objek variabel bernama lembar. Ada fungsi alternatif yang bisa Anda gunakan untuk mendapatkan sheet dengan nama sheet.

Itu akan menjadi var sheet = ss.getSheetByName ("Sheet1");

Anda dapat menggunakan salah satu fungsi yang Anda suka.

3. var range = sheet.getRange (2,2,5);

Fungsi ini mendapatkan rentang nilai berdasarkan baris, kolom, dan jumlah baris. Dalam hal ini kami menarik semua nilai mulai dari baris kedua, kolom kedua, dan mendapatkan nilai lima baris ke bawah.

"Rentang" variabel menjadi apa yang dikenal sebagai "array". Array adalah variabel yang berisi banyak nilai. Di Google Script, Anda merujuk ke nomor mana saja menggunakan skema pengalamatan elemen khusus dengan tanda kurung, seperti yang akan Anda lihat di bawah.

4. nilai var = range.getValues ​​();

Fungsi getValues ​​() sebenarnya menarik nilai dari objek rentang yang Anda tetapkan di atas.

5. jumlah var = 0

Ini hanya variabel angka tunggal yang kami inisialisasi dengan angka 0. Ini menjadi awal bagaimana kami akan merangkum semua angka di kolom.

Menciptakan For Loops

6. untuk (var row dalam nilai) {

Ini adalah apa yang disebut lingkaran "Untuk". Jenis loop ini melangkah melalui sejumlah item tertentu dalam daftar. Dalam hal ini, itu akan melalui array "nilai" yang kita bahas di atas. Loop pertama ini akan mengulang semua baris dalam array. Dalam hal ini hanya akan ada satu.

Catatan: baris array tidak sama dengan baris spreadsheet. Ketika array memuat nilai-nilai dari kolom dalam spreadsheet, itu meletakkannya ke dalam satu baris (atau baris) data, seperti ini: "89, 92, 78, 98, 89". Jadi, dalam hal ini array memiliki satu baris (baris), dan lima angka melintang (kolom).

7. untuk (var col dalam nilai [baris]) {

Loop kedua ini akan melewati semua "baris" dalam array. Dalam hal ini, ia akan melewati semua 5 nilai.

8. jumlah = nilai [baris] [kolom] + jumlah;

Setiap kali loop berjalan, pernyataan ini akan terus menambahkan jumlah nilai baru itu sendiri. Pada saat semua 5 loop terjadi, semua 5 angka harus dijumlahkan ke dalam variabel "jumlah".

9 sheet.getRange (7,2) .setValue (jumlah);

Ini adalah pernyataan dua fungsi. Fungsi getRange menunjuk ke sel di baris ke-7 dan kolom ke-2. Fungsi setValue kemudian menempatkan nilai variabel "jumlah" ke dalam sel itu.

Sekarang setelah Anda memahami cara kerja setiap baris kode, sekarang saatnya untuk melihat semuanya beraksi.

Menyimpan Google Script Anda

Setelah selesai mengetik skrip Google Anda, saatnya untuk menyimpannya dan melangkah melalui kode. Pertama, ketik "proyek Tanpa Judul" untuk memberi nama proyek Anda. Kemudian, pilih ikon floppy disk di menu untuk menyimpan skrip.

Anda mungkin melihat kesalahan saat mencoba menyimpan. Jangan panik. Ini sebenarnya adalah bagian dari proses debugging. Perhatikan baik-baik nomor baris di mana dikatakan Anda memiliki kesalahan. Perhatikan baik-baik kode Anda dan selesaikan kesalahan ketik atau sintaks yang mungkin Anda miliki.

Dalam contoh ini, loop "untuk" saya pada baris 8 tidak ada simbol pembuka “(”. Setelah diperbaiki, coba simpan lagi. Setelah Anda dapat menyimpan kode Anda, Google menganggap sintaksis kode Anda oke.

Ini tidak berarti kode Anda akan berjalan seperti yang dimaksudkan, itu hanya berarti dapat "mengkompilasi" dengan baik, tanpa masalah sintaksis.

Menjalankan Google Script Anda

Sekarang pilih ikon putar di bagian atas untuk menelusuri kode Anda. Karena ini adalah pertama kalinya Anda menjalankan skrip baru Anda, Anda harus memberikan izin kepada aplikasi Google Script untuk mengakses spreadsheet (data) di Akun Google Anda. Pilih Tinjau Izin di jendela sembulan.

Anda harus masuk ke akun Google Anda. Dan karena ini adalah aplikasi khusus dan bukan aplikasi terdaftar di Google, Anda akan melihat peringatan bahwa aplikasi tersebut tidak diverifikasi. Pilih menu Maju tautan lalu pilih Buka Contoh Google Script (tidak aman) untuk melanjutkan.

Di layar berikutnya, Anda harus memilih Mengizinkan untuk memberikan akses Google Script ke akun Google Anda. Setelah Anda melakukannya, skrip akan berjalan.

Jika Anda tidak melihat kesalahan, kembali ke spreadsheet dan Anda akan melihat hasil dari skrip Anda telah muncul di bagian bawah kolom pertama.

Menulis Skrip Google Lanjut

Ini adalah skrip Google yang sangat mendasar yang hanya menambahkan sel dalam satu kolom. Jika Anda ingin menambahkan variabel ke dua Untuk loop dalam kode, Anda bisa mengulangi semua kolom juga dan memberikan ringkasan di baris 7 untuk semuanya.

Google Scripts menawarkan segala macam kemungkinan mengirim email dari spreadsheet. Anda bahkan dapat memanfaatkan Google API khusus yang memungkinkan Anda mengintegrasikan aplikasi Google lainnya seperti Gmail, Kalender, dan bahkan Google Analytics. Setelah Anda mulai belajar cara menulis skrip Google, kemungkinan tidak terbatas.