5 Fungsi Skrip Google Sheets yang Perlu Anda Ketahui

Google Spreadsheet adalah alat spreadsheet berbasis cloud yang kuat yang memungkinkan Anda melakukan hampir semua hal yang dapat Anda lakukan di Microsoft Excel. Tetapi kekuatan sebenarnya dari Google Sheets adalah fitur Google Scripting yang menyertainya.

Skrip Google Apps adalah alat skrip latar belakang yang berfungsi tidak hanya di Google Spreadsheet tetapi juga Google Documents, Gmail, Google Analytics, dan hampir semua layanan cloud Google lainnya. Ini memungkinkan Anda mengotomatiskan aplikasi individual tersebut, dan mengintegrasikan masing-masing aplikasi tersebut satu sama lain.

Dalam artikel ini Anda akan mempelajari cara memulai skrip Google Apps, membuat skrip dasar di Google Spreadsheet untuk membaca dan menulis data sel, dan fungsi skrip Google Spreadsheet lanjutan yang paling efektif.

Cara Membuat Skrip Google Apps

Anda dapat mulai sekarang membuat skrip Google Apps pertama Anda dari dalam Google Spreadsheet.

Untuk melakukannya, pilih Alat dari menu, lalu Editor Skrip.

Ini membuka jendela editor skrip dan default ke fungsi yang disebut myfunction(). Di sinilah Anda dapat membuat dan menguji Google Script Anda.

Untuk mencobanya, coba buat fungsi skrip Google Sheets yang akan membaca data dari satu sel, melakukan perhitungan padanya, dan menampilkan jumlah data ke sel lain.

Fungsi untuk mendapatkan data dari sel adalah fungsi getRange() dan getValue(). Anda dapat mengidentifikasi sel dengan baris dan kolom. Jadi jika Anda memiliki nilai di baris 2 dan kolom 1 (kolom A), bagian pertama skrip Anda akan terlihat seperti ini:

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); var baris = 2; var col = 1; var data = sheet.getRange(baris, kolom).getValue(); }

Ini menyimpan nilai dari sel itu dalam variabel data. Anda dapat melakukan penghitungan pada data, lalu menulis data tersebut ke sel lain. Jadi bagian terakhir dari fungsi ini adalah:

var hasil = data * 100; sheet.getRange(baris, col+1).setValue(hasil); }

Setelah selesai menulis fungsi Anda, pilih ikon disk untuk disimpan.

Saat pertama kali menjalankan fungsi skrip Google Spreadsheet baru seperti ini (dengan memilih ikon jalankan), Anda harus memberikan Otorisasi agar skrip dapat dijalankan di Akun Google Anda.

Izinkan izin untuk melanjutkan. Setelah skrip Anda berjalan, Anda akan melihat bahwa skrip tersebut menulis hasil perhitungan ke sel target.

Sekarang setelah Anda mengetahui cara menulis fungsi skrip Google Apps dasar, mari kita lihat beberapa fungsi lanjutan.

Gunakan getValues ​​Untuk Memuat Array

Anda dapat mengambil konsep melakukan perhitungan pada data di spreadsheet Anda dengan skrip ke tingkat yang baru dengan menggunakan array. Jika Anda memuat variabel dalam skrip Google Apps menggunakan getValues, variabel tersebut akan berupa larik yang dapat memuat beberapa nilai dari sheet.

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange().getValues();

Variabel data adalah array multi-dimensi yang menampung semua data dari sheet. Untuk melakukan perhitungan pada data, Anda menggunakan for loop. Penghitung loop for akan bekerja melalui setiap baris, dan kolom tetap konstan, berdasarkan kolom tempat Anda ingin menarik data.

Dalam contoh spreadsheet kita, Anda dapat melakukan perhitungan pada tiga baris data sebagai berikut.

for (var i = 1; i < data.length; i++) { var result = data[i][0] * 100; sheet.getRange(i+1, 2).setValue(hasil); } }

Simpan dan jalankan skrip ini seperti yang Anda lakukan di atas. Anda akan melihat bahwa semua hasil diisi ke dalam kolom 2 di spreadsheet Anda.

Anda akan melihat bahwa mereferensikan sel dan baris dalam variabel array berbeda dengan fungsi getRange.

data[i][0] mengacu pada dimensi array di mana dimensi pertama adalah baris dan yang kedua adalah kolom. Keduanya dimulai dari nol.

getRange(i+1, 2) mengacu pada baris kedua ketika i=1 (karena baris 1 adalah header), dan 2 adalah kolom kedua tempat hasil disimpan.

Gunakan appendRow Untuk Menulis Hasil

Bagaimana jika Anda memiliki spreadsheet tempat Anda ingin menulis data ke baris baru, bukan kolom baru?

Ini mudah dilakukan dengan fungsi appendRow. Fungsi ini tidak akan mengganggu data yang ada di sheet. Itu hanya akan menambahkan baris baru ke lembar yang ada.

Sebagai contoh, buatlah sebuah fungsi yang akan menghitung dari 1 hingga 10 dan menampilkan penghitung dengan kelipatan 2 pada kolom Penghitung.

Fungsi ini akan terlihat seperti ini:

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); for (var i = 1; i<11; i++) { var hasil = i * 2; sheet.appendRow([i,hasil]); } }

Berikut adalah hasil ketika Anda menjalankan fungsi ini.

Proses Umpan RSS Dengan URLFetchApp

Anda dapat menggabungkan fungsi skrip Google Sheets sebelumnya dan URLFetchApp untuk menarik umpan RSS dari situs web mana pun, dan menulis baris ke spreadsheet untuk setiap artikel yang baru-baru ini diterbitkan ke situs web itu.

Ini pada dasarnya adalah metode DIY untuk membuat spreadsheet pembaca umpan RSS Anda sendiri!

Script untuk melakukan ini juga tidak terlalu rumit.

function myFunction() { var sheet = SpreadsheetApp.getActiveSheet(); var item, tanggal, judul, tautan, desc; var txt = UrlFetchApp.fetch(“https://www.topsecretwriters.com/rss”).getContentText(); var doc = Xml.parse(txt, salah); judul = doc.getUnsurt().getUnsurt(“channel”).getUnsurt(“title”).getText(); var item = doc.getUnsurt().getUnsurt(“channel”).getUnsurts(“item”); // Mengurai satu item dalam Umpan RSS untuk (var i dalam item) { item = item[i]; judul = item.getUnsurt(“judul”).getText(); tautan = item.getUnsurt(“tautan”).getText(); tanggal = item.getUnsurt(“pubDate”).getText(); desc = item.getUnsurt(“deskripsi”).getText(); sheet.appendRow([judul,tautan,tanggal,desc]); } }

Seperti yang Anda lihat, Xml.parse menarik setiap item dari RSS feed dan memisahkan setiap baris ke dalam judul, link, tanggal dan deskripsi.

Menggunakan fungsi appendRow, Anda dapat menempatkan item ini ke dalam kolom yang sesuai untuk setiap item dalam umpan RSS.

Output di lembar Anda akan terlihat seperti ini:

Alih-alih menyematkan URL umpan RSS ke dalam skrip, Anda dapat memiliki bidang di lembar Anda dengan URL, dan kemudian memiliki beberapa lembar – satu untuk setiap situs web yang ingin Anda pantau.

Menggabungkan String dan Menambahkan Pengembalian Kereta

Anda dapat mengambil spreadsheet RSS selangkah lebih maju dengan menambahkan beberapa fungsi manipulasi teks, dan kemudian menggunakan fungsi email untuk mengirim email kepada diri Anda sendiri dengan ringkasan semua posting baru di umpan RSS situs.

Untuk melakukan ini, di bawah skrip yang Anda buat di bagian sebelumnya, Anda ingin menambahkan beberapa skrip yang akan mengekstrak semua informasi dalam spreadsheet.

Anda akan ingin membuat baris subjek dan
isi teks email dengan mem-parsing bersama semua informasi dari larik “item” yang sama yang Anda gunakan untuk menulis data RSS ke spreadsheet.

Untuk melakukan ini, inisialisasi subjek dan pesan dengan menempatkan baris berikut sebelum “item” untuk loop.

var subject = ’10 artikel terbaru dipublikasikan di mysite.com’ var message = ”

Kemudian, di akhir “item” untuk loop (tepat setelah fungsi appendRow), tambahkan baris berikut.

pesan = pesan + judul + ‘n’ + tautan + ‘n’ + tanggal + ‘n’ + desc + ‘n’ + ‘n n’;

Simbol “+” akan menggabungkan keempat item bersama-sama diikuti oleh “n” untuk carriage return setelah setiap baris. Di akhir setiap blok data judul, Anda akan menginginkan dua carriage return untuk badan email yang diformat dengan baik.

Setelah semua baris diproses, variabel “body” menyimpan seluruh string pesan email. Sekarang Anda siap mengirim email!

Cara Mengirim Email Di Google Apps Script

Bagian selanjutnya dari Google Script Anda adalah mengirim “subjek” dan “tubuh” melalui email. Melakukan ini di Google Script sangat mudah.

var emailAddress = [email protected]; MailApp.sendEmail(alamat email, subjek, pesan);

MailApp adalah kelas yang sangat nyaman di dalam skrip Google Apps yang memberi Anda akses ke layanan email Akun Google Anda untuk mengirim atau menerima email. Berkat ini, satu baris dengan fungsi sendEmail memungkinkan Anda mengirim email apa pun hanya dengan alamat email, baris subjek, dan teks isi.

Seperti inilah tampilan email yang dihasilkan.

Menggabungkan kemampuan untuk mengekstrak umpan RSS situs web, menyimpannya di Google Sheet, dan mengirimkannya ke diri Anda sendiri dengan tautan URL yang disertakan, membuatnya sangat nyaman untuk mengikuti konten terbaru untuk situs web apa pun.

Ini hanyalah salah satu contoh kekuatan yang tersedia di skrip Google Apps untuk mengotomatiskan tindakan dan mengintegrasikan beberapa layanan cloud.

Related Posts